How to update Wordpress custom SQL Select query for custom taxonomies so that syntax is correct?

Question

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:

(wp_term_taxonomy.taxonomy) jobs

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.

Answer 1

You should not use a direct SQL query, instead try the WP_Query tax queries e.g.:

$args = array(
    'post_type' => 'post',
    'tax_query' => array(
        'relation' => 'AND',
        array(
            'taxonomy' => 'category',
            'field' => 'id',
            'terms' => array( 22 )
        ),
        array(
            'taxonomy' => 'job',
            'field' => 'name',
            'terms' => $terms,
            'operator' => 'IN'
        )
    )
);
$query = new WP_Query( $args );

Where $terms is an array of term names.

Answer 2

What is meant to happen if $terms is empty? No records are selected?

If that's the case, you can probably get away with just setting the initial value of $terms to a dummy string:

$terms = 'no terms';