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.