admin管理员组

文章数量:1122846

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!

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!

• wpdb

Share Improve this question edited Mar 27, 2019 at 17:11 Bert ter Voert asked Mar 27, 2019 at 10:56 Bert ter VoertBert ter Voert 4111 silver badge44 bronze badges 6

• Hey Bert, welcome to WordPress development. We need to see your current $wpdb call to be able to tell you what's wrong. – leymannx Commented Mar 27, 2019 at 10:58
• Since wp_ratings isn't a standard table I'd guess you'd have to write your own SQL query for this rather than trying to get WP_Query to construct one for you. (But maybe it's more flexible than I remember!) If you need our help writing the SQL, can you show us or point us to the schema for wp_ratings please? – Rup Commented Mar 27, 2019 at 11:02
• 1 updated my question – Bert ter Voert Commented Mar 27, 2019 at 11:16
• WHERE comment_id =( SELECT - you need in: where comment_id in (. I doubt it makes any difference to the database if you do a join or a subquery like this. It should be easy enough to get the count and average in separate queries if that's good enough, but you can probably do it in a single query with the right syntax. And depending how expensive these are you probably want to cache the results in transients, and / or compute a lot of these at once if that's more efficient. – Rup Commented Mar 27, 2019 at 11:58
• @Rup: I discovered there is a mistake in the subquery. I ran the subquery as a query (deleted the outer part) which resulted in empty array. When I use the number of a post (that has ratings) instead of . the_ID () . I get the proper results in the array. What should I use instead of . the_ID() . ? – Bert ter Voert Commented Mar 27, 2019 at 12:14

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 0

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

本文标签： wpdbGet data from database table by postid to get data from second database table