Little MySQL help needed: request from $wpdb->posts and $wpdb->comments

  • I am sure this is a very simple question, however not for me.

    OK, I have 2 tables in MySQL (the standard WP tables):

    I have 2 tables in MySQL:

    Table 1: wp_posts

    ID | post_date
    ———————————-
    1 | 2006-01-02 18:05:01
    2 | 2006-01-02 18:08:27

    Table 2: wp_comments

    comment_post_ID | comment_ID
    ————————————————
    1 | 101
    1 | 102
    2 | 103
    2 | 104
    1 | 105
    2 | 106
    2 | 107
    2 | 108
    2 | 109

    comment_ID (table wp_comments) = ID (table wp_posts).

    I need the following result:

    ID | *Number of comments*
    1 3
    2 6

    Which query do I need?

    I have so far:

    $query = ” SELECT ID, comment_post_ID, comment_ID FROM $tableposts, $tablecomments
    WHERE comment_approved = ‘1’ AND post_status = ‘publish’
    AND post_password=”
    ORDER BY post_date DESC
    LIMIT $limit “;

    Any help is appreciated.

    Bob

Viewing 1 replies (of 1 total)
  • Thread Starter bob78

    (@bob78)

    Anyone?
    I just would like to have the string to pass to $wpdb->get_results…

    $query = “SELECT ID, comment_post_ID, comment_ID FROM $tableposts, $tablecomments ………”;
    $queryresult = $wpdb->get_results($query);

    Thanks in advance.

Viewing 1 replies (of 1 total)
  • The topic ‘Little MySQL help needed: request from $wpdb->posts and $wpdb->comments’ is closed to new replies.