WP_Query not handling 'LIKE' correctly.

[tberneman]

I am doing custom PHP to retrieve listings (as a CPT) from the database for a Realty Company website I'm working on and one of the criteria is searching on the city from a search field on a form. The problem is the generated SQL has some sort of guid in it instead of the expected percent character normally used with LIKE. Here is the generated SQL from WP_Query:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'city' AND wp_postmeta.meta_value LIKE '{f270e6c7d3b231242ceefa28fdd47243cac2100fe4fe8c9df633ec6f3cc2f583}fairfield{f270e6c7d3b231242ceefa28fdd47243cac2100fe4fe8c9df633ec6f3cc2f583}' ) AND ( mt1.meta_key = 'beds' AND mt1.meta_value >= '0' ) ) AND wp_posts.post_type = 'listings' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

As you can see the generated code for searching the "city" field is:

wp_postmeta.meta_value LIKE '{f270e6c7d3b231242ceefa28fdd47243cac2100fe4fe8c9df633ec6f3cc2f583}fairfield{f270e6c7d3b231242ceefa28fdd47243cac2100fe4fe8c9df633ec6f3cc2f583}'

The "expected" code should look like this:

wp_postmeta.meta_value LIKE '%fairfield%'

The form is found here: ​https://millersells.com/search-2/

After making selections and clicking the Search button it calls itself with url parameters and you get one of these errors:
"Fatal error: Maximum execution time of 60 seconds exceeded in /home/extremx1/public_html/millersells2017b/wp-includes/query.php on line 0"
OR
"Fatal error: Maximum execution time of 60 seconds exceeded in /home/extremx1/public_html/millersells2017b/wp-includes/class-wp-query.php on line 3090"

(Example URL with parameters: ​https://millersells.com/search-2/?cf_id=37&loc=fairfield&type=SingleFamilyHome&beds=3&baths=2&min_price=0&max_price=0)

I am using this PHP code to create the query (for now I'm just working with 2 of the search fields):

<?php

if (isset($_GET['loc'])) $location = trim($_GET['loc']); else $location = '';
if (isset($_GET['type'])) $type = trim($_GET['type']); else $type = '';
if (isset($_GET['beds'])) $bedrooms = trim($_GET['beds']); else $bedrooms = 0;
if (isset($_GET['baths'])) $bathrooms = trim($_GET['baths']); else $bathrooms = 0;
if (isset($_GET['min_price'])) $min_price = trim($_GET['min_price']); else $min_price = 0;
if (isset($_GET['max_price'])) $max_price = trim($_GET['max_price']); else $max_price = 999999999;
$location = 'fairfield';

$args = array(
	'post_type' => array( 'listings' ),
	'meta_query' => array(
		array(
			'key' => 'city',
			'value' => $location,
			'type' => 'CHAR',
			'compare' => 'LIKE',
		),
		array(
			'key' => 'beds',
			'value' => $bedrooms,
			'compare' => '>='
		)
	)
);

$the_query = new WP_Query( $args );

?>

To see the generated SQL I added this code at line 2752 in wp_includes/class-wp-query.php: echo $this->request;

Also to aid in debugging I would suggest you add a "generated_sql" parameter to the query object that we can echo to see what the actual generated SQL looks like. EX: echo $the_query->generated_sql;

Thanks!

Tim :o]

PS: Your welcome to contact me at tberneman@…

[ionutm]

the problem is the last line from function prepare from wp-db.php

return $this->add_placeholder_escape( $query );

with replaces the % characters with placeholders