wpdb get_results is empty when keyword contains ampersand (while the very same request works fine in phpMyAdmin SQL)

3 Views

I have a very specific problem with wpdb->get_results
I have implemented custom search request which is able to search through all our custom types and custom-structured tables.
The query works fine (therefor no db connection problem), until the search keyword contains an ampersand (&).
The real mystery is that if I echo the request itself and then run it through phpMyAdmin SQL, the query returns expected (and desired) results.
I tried using mysqli but to na avail.

Here is the crucial part of code used in the search system:

Read More
$request = "SELECT DISTINCT
                    SQL_CALC_FOUND_ROWS
                    p.ID,
                    (SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'latitude') AS latitude,
                    (SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'longitude') AS longitude
                        FROM wp_posts p
                        WHERE p.post_type = 'studia'
                            AND (SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'latitude') > 0
                            AND (SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'longitude') > 0
                            AND ((SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'mesto' LIMIT 1) LIKE '%" . $keyword . "%'
                                 OR p.post_title LIKE '%" . $keyword . "%'
                                 OR (SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'ulice' LIMIT 1) LIKE '%" . $keyword . "%'
                                 OR (SELECT IF(pm.meta_value LIKE 'a:%',(SELECT post_title FROM wp_posts WHERE ID = SUBSTRING_INDEX(SUBSTRING_INDEX(pm.meta_value,'"',2),'"',-1)), pm.meta_value ) FROM wp_postmeta pm WHERE pm.post_id = p.ID AND meta_key = 'studio' ) LIKE '%" . $keyword . "%')";
        echo $request;
        $studios = $wpdb->get_results( $request);

and here is the echoed example (keyword: YOGA & ART) request which again returns desired results in phpMyAdmin, but not on the website 

SELECT DISTINCT SQL_CALC_FOUND_ROWS p.ID, (SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'latitude') AS latitude, (SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'longitude') AS longitude FROM wp_posts p WHERE p.post_type = 'studia' AND (SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'latitude') > 0 AND (SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'longitude') > 0 AND ((SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'mesto' LIMIT 1) LIKE '%YOGA & ART%' OR p.post_title LIKE '%YOGA & ART%' OR (SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = 'ulice' LIMIT 1) LIKE '%YOGA & ART%' OR (SELECT IF(pm.meta_value LIKE 'a:%',(SELECT post_title FROM wp_posts WHERE ID = SUBSTRING_INDEX(SUBSTRING_INDEX(pm.meta_value,'"',2),'"',-1)), pm.meta_value ) FROM wp_postmeta pm WHERE pm.post_id = p.ID AND meta_key = 'studio' ) LIKE '%YOGA & ART%')
Post Views: 3

Related posts

Leave a Reply

1 comment

  1. I have found the answer. To put it simply, since I echoed the request, the display format wasn’t identical to what was actually sent, turns out that HTML entity & was actually sent instead of the symbol &. So simple replace solved my issue.

    $keyword = str_replace("&", "&", $keyword);