Multiple plugins messing up SQL query

  • I’ve been trying to figure out how to get both private categories and front page categories to play nice… And what I am seeing has me baffled.

    As far as I can figure out the code, each plugin concatenates its own stuff to the SQL query. So far so good – except that each plugin appears to use the same bit of code – like this:

    $query . ‘ LEFT JOIN ‘ . $table_prefix .
    ‘post2cat ON (‘ . $table_prefix . ‘posts.ID = ‘ .
    $table_prefix . ‘post2cat.post_id) ‘

    If you have two plugins, you get a double LEFT JOIN query like this:

    SELECT COUNT(ID) FROM wp_posts /*index*/ LEFT JOIN wp_post2cat ON wp_posts.ID = wp_post2cat.post_id LEFT JOIN wp_post2cat ON (wp_posts.ID = wp_post2cat.post_id) WHERE 1=1 AND post_date_gmt <= ‘2005-08-16 13:27:59’ AND (post_status = “publish” OR post_author = 1 AND post_status != ‘draft’ AND post_status != ‘static’) AND category_id <> 4 AND category_id <> 5

    which pukes…. SQL must reformat that query before spewing forth the error, since the concat statement actually results in something like this:

    SELECT COUNT(ID) FROM wp_posts /*index*/ LEFT JOIN wp_post2cat ON wp_posts.ID = wp_post2cat.post_id WHERE 1=1 AND post_date_gmt <= ‘2005-08-16 13:27:59’ AND (post_status = “publish” OR post_author = 1 AND post_status != ‘draft’ AND post_status != ‘static’) LEFT JOIN wp_post2cat ON (wp_posts.ID = wp_post2cat.post_id) WHERE category_id <> 4 AND category_id <> 5

    It seems to me that instead of a simple ‘.=’ there should be a wp_join($new_query) that would check the content of the join statement and correctly add the new search requirements, no?

    If someone is willing to check my SQL syntax, I will make a stab at writing that function….

Viewing 2 replies - 1 through 2 (of 2 total)

  • Chuck the two plugins, after using them both to create the one super plugin that you need. Works all the time!

    Thread Starter cptdondo

    (@cptdondo)

    ?? started down that road. It’s a pain… Neither plugin is simple….

    So I hit on a simpler sol,ution – add one line to the ecg plugin:

    function ecg_cat_join($query) {
    global $table_prefix;
    /****** new line ******/
    if (strstr($query,’JOIN’)) { return $query ;}
    $newquery = $query . ‘ LEFT JOIN ‘ . $table_prefix .
    ‘post2cat ON (‘ . $table_prefix . ‘posts.ID = ‘ .
    $table_prefix . ‘post2cat.post_id) ‘;
    return $newquery;
    }

    You also have to load the ecg plugin after the private categories plugin….

    But it still seems to me that there should be a standard wp-join function to make sure the plugins create a valid SQL query…..

    –Yan

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Multiple plugins messing up SQL query’ is closed to new replies.