I have a Wordpress website that written by third party web developer for version 2.9 before custom taxonomies were fully implemented. We have since upgraded to latest wordpress version 3.5.x. There is a SQL Select query that is generating a bunch of errors in the Wordpress error.log file.
In this template, we have a few custom taxonomies in use. The one I am concerned about is listed below:
A key point here is that not all Wordpress posts are using the taxonomy field
jobs. Therefore some post will not have any 'jobs' terms associated with it.
Here is the error I see repeated most often
WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 9 for query SELECT DISTINCT p.* FROM wp_posts p, wp_terms t, wp_term_taxonomy tt, wp_term_relationships tr, wp_terms t2, wp_term_taxonomy tt2, wp_term_relationships tr2 WHERE p.id = tr.object_id AND t.term_id = tt.term_id AND tr.term_taxonomy_id = tt.term_taxonomy_id AND p.id = tr2.object_id AND t2.term_id = tt2.term_id AND tr2.term_taxonomy_id = tt2.term_taxonomy_id AND (tt.taxonomy = 'category' AND tt.term_id = t.term_id AND t.term_id = '22') AND (tt2.taxonomy = 'jobs' AND tt2.term_id = t2.term_id AND t2.name IN ()) made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/generic/page.php')
Here is the code snippet from the PHP template file.
<? else : $playlist = 0; $jobs = null; $job = null; $terms = null; $jobs = wp_get_object_terms( $post->ID, 'jobs', array('fields'=>'names')); $term_count = 0; foreach($jobs as $job) : if ($term_count > 0) : $terms .= ", '".$job."'"; else : $terms .= "'".$job."'" ; endif; $term_count++; endforeach; $sql = " SELECT DISTINCT p.* FROM $wpdb->posts p, $wpdb->terms t, $wpdb->term_taxonomy tt, $wpdb->term_relationships tr, $wpdb->terms t2, $wpdb->term_taxonomy tt2, $wpdb->term_relationships tr2 WHERE p.id = tr.object_id AND t.term_id = tt.term_id AND tr.term_taxonomy_id = tt.term_taxonomy_id AND p.id = tr2.object_id AND t2.term_id = tt2.term_id AND tr2.term_taxonomy_id = tt2.term_taxonomy_id AND (tt.taxonomy = 'category' AND tt.term_id = t.term_id AND t.term_id = '22') AND (tt2.taxonomy = 'jobs' AND tt2.term_id = t2.term_id AND t2.name IN ($terms)) "; if (! $results = $wpdb->get_results($sql)) : ?>
From what I can deduce from the logic in the PHP file, I see the variable
$terms is created with a
NULL value. A
foreach loop is used to find any
jobs terms and append them to the variable
$terms. Next, the SQL Select query uses this variable in the last AND condition.
If there are values in the
$terms variable, then the SQL evaulates w/o any problems.
But what happens if there are no values in the
$terms variable? It is
NULL. then the SQL generates a syntax error.
How can this be rewritten to handle
NULL values for the
$terms variable when the SQL query is run?
I hope I was complete enough in asking this question. Thanks ahead of time for any input.