Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
    
What prevents this being done using WP_Query? –  Tom J Nowell Mar 28 '13 at 17:54
add comment

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
    
I see how the WP_Query tax_queries are the replacement for direct SQL queries. I am not as knowledgeable on SQL syntax and formatting so what happens if the variable $terms is an empty array, i.e. a post does not have any 'job' terms associated with this? Does that break the query or will it gracefully ignore the empty array? –  mzwarg Mar 28 '13 at 18:46
    
If I understand you, in my PHP file, I would copy your code to add variable $args & change the variable $sql= to new WP_Query( $args ); Correct? –  mzwarg Mar 28 '13 at 19:23
    
Then you have this: "SERVANT!! Find me all the things in the house starting with the letter '' ", "But master which letter did you refe..", "I SAID DO IT!!". Since everything in the house had a name that began with a letter, either no items were found, or the servant threw an SQL syntax error –  Tom J Nowell Mar 28 '13 at 19:36
    
Also, no, WP_Query is an object, a WP_Query object, it should be treated as such. I suggest you read up on Post loops and the main loop, specifically the "You don't know Query" presentation by Andrew Nacin. If you attempt to use it as an array of returned rows, you'll have issues as it is not an array. –  Tom J Nowell Mar 28 '13 at 19:38
1  
Thanks for the updates. I will take a look at the presentation (wordpress.tv/2012/06/15/andrew-nacin-wp_query) and slideshare presentation (slideshare.net/andrewnacin/…) –  mzwarg Mar 28 '13 at 20:24
show 1 more comment

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';
share|improve this answer
    
I believe that if $Terms is empty, then that would work. Let me try it. –  mzwarg Mar 28 '13 at 16:47
    
I tested this and I still got the SQL syntax errors. thanks anyways. :-( –  mzwarg Mar 28 '13 at 20:13
    
The WP_Query route is the best one to take, for sure, but if you just need a quick'n'dirty fix to get things back on the road, then you could try initializing $terms with a dummy string, ie $terms = 'no terms'. That should at least bypass that SQL error. –  vancoder Mar 28 '13 at 20:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.