Using wildcards and Variables to Query mySQL

[trebgur]

Ive been making a custom search feature in wordpress where I query the DB to look for posts based on specific criteria and while I can get it to work, it will break and not return results if I add this:

          
AND post_id IN (SELECT post_id
                       FROM   wp_postmeta
                       WHERE  ( meta_key = 'Utilities'
                                AND meta_value LIKE '%Water%' ))

or this, where $searchAvail = '%-08-%'

       AND post_id IN (SELECT post_id
                       FROM   wp_postmeta
                       WHERE  ( meta_key = 'Availability'
                                AND meta_value LIKE '%-$searchAvail-%' ))

it has the to be wildcards... I know for a fact the query returns real results (I used that exact code in terminal) but it doesnt work from php. I use backslashes to escape characters right? That doesnt seem to work.

[trq]

If that is a php string we need to see the entire thing in context.

[trebgur]

Im real new to both PHP and SQL so sorry if this code is horrendous.

 

$postids=$wpdb->get_col($wpdb->prepare("
SELECT DISTINCT post_id
FROM   wp_postmeta
WHERE  post_id IN (SELECT post_id
                   FROM   wp_postmeta
                   WHERE  ( meta_key = 'Price'
						AND meta_value BETWEEN 1 AND $searchPrice ))
   AND post_id IN (SELECT post_id
			   FROM   wp_postmeta
			   WHERE  ( meta_key = 'Bedrooms'
						AND meta_value = $searchBeds ))
       AND post_id IN (SELECT post_id
                       FROM   wp_postmeta
                       WHERE  ( meta_key = 'Area'
                                AND meta_value = '$searchArea' ))

", $searchPrice, $searchBeds, $searchArea)); 

if (!empty($postids)) {
   foreach ($postids as $id) { 
    $post=get_post(intval($id));
    setup_postdata($post);?>
    <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
    <?php
  } 
} else{
echo('No Results Found!');

}

		}
?>

 

I know the query works because this code which is more or less the same works in terminal when Im messing around with SQL tables:

SELECT DISTINCT post_id
FROM   wp_postmeta
WHERE  post_id IN (SELECT post_id
                   FROM   wp_postmeta
                   WHERE  ( meta_key = 'Price'
                            AND meta_value BETWEEN 1 AND 1000 ))
       AND post_id IN (SELECT post_id
                       FROM   wp_postmeta
                       WHERE  ( meta_key = 'Utilities'
                                AND meta_value LIKE '%Water%' ))
       AND post_id IN (SELECT post_id
                       FROM   wp_postmeta
                       WHERE  ( meta_key = 'Bedrooms'
                                AND meta_value = 1 ))
       AND post_id IN (SELECT post_id
                       FROM   wp_postmeta
                       WHERE  ( meta_key = 'Availability'
                                AND meta_value LIKE '%-08-%' ))
       AND post_id IN (SELECT post_id
                       FROM   wp_postmeta
                       WHERE  ( meta_key = 'Area'
                                AND meta_value = 'Cary Town' ));  

 

soooooo it just doesnt work from PHP

[trebgur]

BUMP!

[gizmola]

You're using the wpdb->prepare() method, that expects sprintf style substitution strings.  Considering what you're trying to do, you might have better luck using $wpdb->query()