Advanced SELECT query with condtional statements

Question

I am having trouble understanding the proper way to write an advanced select query. I have the following the code here.

if (!empty($_GET['3321body_type'])) {
    $bt = $_GET['3321body_type'];
    $bt_sql = "AND m1.meta_key = 'body_type' AND m1.meta_value = '$bt'";
}
else $bt_sql = '';
if (!empty($_GET['3321Make'])) {
   $make = $_GET['3321Make'];
    $make_sql = "AND m3.meta_key = 'Make' AND m3.meta_value = '$make'";
}
 else $make_sql = '';   
    if (!empty($_GET['3321Model'])) {
        $model = $_GET['3321Model'];
    $model_sql = "AND m4.meta_key = 'Model' AND m4.meta_value = '$model'";
    }
   else $model_sql = '';
  if (!empty($_GET['3321Year'])) {
  $year = $_GET['3321Year'];
  $year_sql = " AND m2.meta_key = 'Year' AND m2.meta_key = 'Year' AND   
  m2.meta_value       BETWEEN '$year' AND '9999'";

  }
  else $year_sql = ''; 
   global $wpdb;
    $wpdb->show_errors = true;

$querystr = "
SELECT * FROM wp_posts
LEFT JOIN wp_postmeta m0 on (wp_posts.ID = m0.post_id) 
LEFT JOIN wp_postmeta m1 on (wp_posts.ID = m1.post_id) 
LEFT JOIN wp_postmeta m2 on (wp_posts.ID = m2.post_id) 
LEFT JOIN wp_postmeta m3 on (wp_posts.ID = m3.post_id) 
LEFT JOIN wp_postmeta m4 on (wp_posts.ID = m4.post_id) 
WHERE wp_posts.ID = m0.post_ID
" . $bt_sql . "
" . $make_sql . "
" . $model_sql . "
" . $year_sql . "
";

$search = $wpdb->get_results($querystr, OBJECT);
foreach($search as $post) {
setup_postdata($post);

If I write it out like this

SELECT * FROM wp_posts
LEFT JOIN wp_postmeta m0 on (wp_posts.ID = m0.post_id) 
LEFT JOIN wp_postmeta m1 on (wp_posts.ID = m1.post_id) 
LEFT JOIN wp_postmeta m2 on (wp_posts.ID = m2.post_id) 
LEFT JOIN wp_postmeta m3 on (wp_posts.ID = m3.post_id) 
LEFT JOIN wp_postmeta m4 on (wp_posts.ID = m4.post_id) 
WHERE wp_posts.ID = m0.post_ID
AND m1.meta_key = 'body_type' AND m1.meta_value = 'Sedan'
AND m3.meta_key = 'Make' AND m3.meta_value = 'Acura'
AND m4.meta_key = 'Model' AND m4.meta_value = 'CL'
AND m2.meta_key = 'Year' AND m2.meta_key = 'Year' AND m2.meta_value BETWEEN '$year' AND '9999'

It shows up just fine. But When I add in the conditional statements in my first block of code. it appears the JOIN is not working properly because it returns duplicate rows.

Can anyone show me the proper way to write this statement to make the search query dynamic? Also an example of how to work wpdb->prepare into the works.

Answer 1

I think you are jumping a gun with custom SQL, complexity of this seems still in realm of WP_Query. Even if not it's a good idea to start with it to have more solid base for custom SQL.

If I try something like this:

$meta_query = array(
    array( 'key' => 'body_type', 'value' => 'bt' ),
    array( 'key' => 'Make', 'value' => 'make' ),
    array( 'key' => 'Model', 'value' => 'model' ),
    array( 'key' => 'Year', 'value' => array( 2010, 9999 ), 'compare' => 'BETWEEN' ),
);

$query = new WP_Query( array( 'meta_query' => $meta_query ) );

$query->get_posts();
var_dump( $wpdb->queries );

This is the SQL generated:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
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)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
WHERE 1=1
  AND wp_posts.post_type = 'post'
  AND (wp_posts.post_status = 'publish'
       OR wp_posts.post_status = 'private')
  AND ((wp_postmeta.meta_key = 'body_type'
        AND CAST(wp_postmeta.meta_value AS CHAR) = 'bt')
       AND (mt1.meta_key = 'Make'
            AND CAST(mt1.meta_value AS CHAR) = 'make')
       AND (mt2.meta_key = 'Model'
            AND CAST(mt2.meta_value AS CHAR) = 'model')
       AND (mt3.meta_key = 'Year'
            AND CAST(mt3.meta_value AS CHAR) BETWEEN '2010' AND '9999'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Note that GROUP BY at the end, it's probably what takes care of duplicates you are getting.

See Custom Field Parameters documentation in Codex for more info on meta queries.