Yet another tech support bleg

December 6, 2005 | 7 comments
By

An SQL problem of sorts has arisen, that is beyond our limited capabilities. Bryce to the rescue, once again. I say we double his T&S salary.

POST FORMERLY READ:

We have a recent comments plugin. It works. (Horray!)

It doesn’t have the capability of pulling comments by category type. In fact, I’ve scoured the net, but no such plugin exists at the moment (despite some false advertising). So I’m creating one, based on the other plugin. So far so good.

It’s a two step process, which should in theory be simple enough.

First, we collect all posts from category 62:

SQL query:
SELECT post_id FROM wp_post2cat WHERE category_id = ’62′

(That query works, by the way).

The next step will be to somehow merge that query into the existing plugin’s current query for pulling comments, which itself pulls data from two other tables. I’d like to get that same data, but only for posts that match the category check.

The query on the plugin as it now stands — pulling comments without regard to category — is this:

function get_bushman_comments () {
global $wpdb, $tablecomments, $tableposts;
$request = “SELECT ID, comment_ID, comment_content, comment_author, post_title, FROM $tablecomments LEFT JOIN $tableposts ON $tableposts.ID=$tablecomments.comment_post_ID WHERE post_status = ‘publish’”;

How can I add the prior query (pulling posts in category 62) to that, in order to limit the second query to getting data from those posts that come from the first query? (Does that question make sense?) Is there a good way to combine these two queries? (Or some other obvious, easy solution that I’m missing?) Glory, fame, and lucre await the savvy bloggernacker who comes up with a working query.

(Can’t Kaimi figure this out himself? We’ll just say that one or two blog-rattling attempts later, Kaimi decided that playing with SQL on a loaded blog is not a good idea. After all, we ain’t too proud to bleg; discretion is the better part of valor; he who fights and runs away; etc., etc.)

Muchas gracias.

7 Responses to Yet another tech support bleg

  1. Mark N. on December 6, 2005 at 2:53 am

    Given that I’m flying blind in your database, and that I don’t know the structure of your tables, I’m guessing that you’d want to do something like

    SELECT ID, comment_ID, comment_content, comment_author, post_title, FROM $tablecomments LEFT JOIN $tableposts ON $tableposts.ID=$tablecomments.comment_post_ID
    LEFT JOIN wp_post2cat ON wp_post2cat.post_id=$tablecomments.comment_post_ID
    WHERE post_status = ‘publish’

    But, like I said, it’s kind of a stab in the dark. You can string just about as many LEFT JOINs together as you want in these things.

  2. Mark N. on December 6, 2005 at 2:54 am

    Oops. Left something out:

    SELECT ID, comment_ID, comment_content, comment_author, post_title, FROM $tablecomments LEFT JOIN $tableposts ON $tableposts.ID=$tablecomments.comment_post_ID
    LEFT JOIN wp_post2cat ON wp_post2cat.post_id=$tablecomments.comment_post_ID
    WHERE post_status = ‘publish’ and wp_Post2cat.category_id = ‘62′

    Or something like that.

  3. Kaimi Wenger on December 6, 2005 at 9:15 am

    Hi,

    Yeah, I guess it would help if you knew the database structure. wp_posts, wp_comments, and wp_post2cat are all tables that are off of the main database, which is wpdb.

    Also, thanks for the suggestion (which, alas, isn’t working, though I’m continuing to tinker).

    Variations that are also not working:

    global $wpdb, $tablecomments, $tableposts, $tablepost2cat;
    $request = “SELECT ID, comment_ID, comment_content, comment_author, post_title, FROM $tablecomments LEFT JOIN $tableposts ON $tableposts.ID=$tablecomments.comment_post_ID WHERE post_status = ‘publish’ LEFT JOIN $tablepost2cat ON ($tableposts.ID = $tablepost2cat.post_id) WHERE category_id = ’62′”;

    and

    SELECT ID, comment_ID, comment_content, comment_author, post_title, FROM $tablecomments LEFT JOIN $tableposts ON $tableposts.ID=$tablecomments.comment_post_ID LEFT JOIN $tablepost2cat ON $tablepost2cat.post_id=$tablecomments.comment_post_ID WHERE post_status = ‘publish’ and category_id = ’62′

    and

    SELECT ID, comment_ID, comment_content, comment_author, post_title, FROM $tablecomments LEFT JOIN $tableposts ON $tableposts.ID=$tablecomments.comment_post_ID WHERE post_status = ‘publish’ LEFT JOIN $tablepost2cat ON $tablepost2cat.post_id=$tablecomments.comment_post_ID WHERE category_id = ’62′

    I’m a pro at generating non-working queries . . .

  4. Bryce I on December 6, 2005 at 10:45 am

    Kaimi –

    IM me.

  5. Bryce I on December 6, 2005 at 10:52 am

    SELECT SELECT ID, comment_ID, comment_content, comment_author, post_title
    FROM wp_comments INNER JOIN (wp_posts INNER JOIN wp_post2cat ON wp_posts.ID = wp_post2cat.post_id) ON wp_comments.comment_post_ID = wp_posts.ID
    WHERE (((wp_post2cat.category_id)=62));

    Make appropriate replacements of table names with php variables

    There may be some naming problems with fields.

  6. Kaimi Wenger on December 6, 2005 at 10:59 am

    Thank you, Bryce. It works like a charm!

  7. danithew on December 7, 2005 at 10:39 am

    Kaimi, speaking of coding changes … I think it would be good for the sideblog if people could comment on the links provided there — whether to express approval/disapproval of the comments provided or to add thoughts regarding the content of the links themselves. Any chance of that ever happening?