Get data from database table by post_id to get data from second database table

Question

I have two db tables: wp_comments and wp_ratings. I'm trying to create a query using $wpdb which has to do the following:

from wp_comments I have to get the comment_ID's where the comment_post_ID is equal to the current post id to get all the ratings from wp_ratings where comment_id is equal to the comment_ID's i got from wp_comments.

wp_comments table is the standard wp table for comments. wp_ratings is custom and looks like this:

wp_ratings:

• id
• date
• comment_id
• user_id
• rating

I'm struggling to determine wether to use JOIN or a subquery to achive this. I'm rather new to writing queries. So I only have a first part of a query so far:

$my_query = $wpdb -> get_results( "SELECT rating FROM wp_ratings WHERE comment_id =( SELECT comment_ID FROM comments WHERE comment_post_ID = " . the_ID () . " ) " );

I want the ratings to calculate (and show) the average value and to show the number of ratings.

EDIT: got the substringpart working:

$my_query = $wpdb -> get_results( "SELECT comment_ID FROM wp_comments WHERE comment_post_ID =  $post->ID" );

found a comment somwhere that when you use double quotes, you can insert a variable directly without closing the string!

Answer 1

There are a couple things you need to do differently to make this work well.

1. Make sure you're setting $wpdb to the global.
2. Use $wpdb->prefix instead of hard-coding wp_.
3. Wrap your variables in curly braces.
4. Use $wpdb variables instead of table names, like $wpdb->comments.
5. Always always always use $wpdb->prepare() before performing a query.

Using $wpdb->prepare()

From the Codex:

All data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks. The prepare method performs this functionality for WordPress, which supports both a sprintf()-like and vsprintf()-like syntax.

sprintf is called a bit different than most functions. Just like array_merge(), it takes as many arguments as you want to give it.

First, specify your SQL statement:

$wpdb->prepare( 
    "SELECT rating 
    FROM {$wpdb->prefix}ratings 
    WHERE comment_id = ( 
        SELECT comment_ID 
        FROM {$wpdb->comments} 
        WHERE comment_post_ID = %d 
    )", get_the_ID() )

Notice the %d in the prepare statement? This means you're expecting an integer here. If you don't get an integer from get_the_ID(), the process will error out to protect your data.

Next, you add in your expected parameters in the order they appear in the SQL statement. Since we only have one in this statement, we add get_the_ID() after the SQL statement.

Putting it all together

global $wpdb;
$my_query = $wpdb->get_results( 
    $wpdb->prepare( 
        "SELECT rating 
        FROM {$wpdb->prefix}ratings 
        WHERE comment_id = ( 
            SELECT comment_ID 
            FROM {$wpdb->comments} 
            WHERE comment_post_ID = %d 
        )", 
    get_the_ID() ) 
);