Support » Plugins » Hacks » MySQL query error driving me nuts

  • Resolved SpikeTheLobster

    (@spikethelobster)


    I’m trying to run a MySQL query via WordPress, to bring back a list of posts that I want to delete because they have no “like” votes. The query works perfectly in phpMyAdmin but gives a syntax error when I run it through WP… and I see absolutely no reason why it would do this.

    Here’s the query code, which checks for posts over 30 days old which have no corresponding “like” entry in wti_like_post (whether positive or negative):

    $novotesquery = "SELECT * FROM $wpdb->posts
    WHERE $wpdb->posts.post_type = 'post'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->posts.post_date < DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND $wpdb->posts.ID NOT IN (SELECT DISTINCT post_id FROM $wpdb->wti_like_post)" ;
    
    $result = $wpdb->get_results($novotesquery);
    foreach ($result as $post) {
      setup_postdata($post);
      $postid = $post->ID;
      wp_delete_post($postid);
    }

    The syntax error says there’s a problem on the last line (the SELECT in parentheses): “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 5”.

    If anyone can tell me why the SQL query will run on the server and not in WP, I’d appreciate it.

    Thanks in advance.

Viewing 1 replies (of 1 total)
  • Thanks to a prompt on Stackoverflow, I figured out what was wrong. As usual, assumption is the mother of all screwups!

    I assumed “$wpdb->wti_like_post” converted to “DBprefix_wti_like_post”, as “$wpdb->posts” converts to “DBprefix_posts”. My assumption was wrong. It doesn’t convert to anything at all, as far as I can see, presumably because it’s not a built-in WordPress table.

    What’s needed is “{$wpdb->prefix}wti_like_post“, which forces the DB prefix into place.

    It works now.

    Thanks very much to both commenters for the mental prompt from asking how the value comes out!

Viewing 1 replies (of 1 total)
  • The topic ‘MySQL query error driving me nuts’ is closed to new replies.