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

by

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() ) 
);

Leave a Comment