1

I'm having a small query issue that maybe you can help me with. I am trying to get all comments from a post that has a particular comment meta. This is what I have so far:

$comments = $wpdb->get_results("SELECT * FROM $wpdb->comments INNER JOIN wp_commentmeta WHERE comment_post_ID = '256' AND comment_approved = '1' AND meta_key = 'bestcomment' AND meta_value = 'yes' ");  ?>
<ul id="bestcomment">
<h2>Best Comment</h2>
<?php
if ( $comments ) : foreach ( (array) $comments as $comment) :
echo  '<li class="recentcomments">' . sprintf(__('%1$s on %2$s'), get_comment_author_link(), '<a href="'. get_comment_link($comment->comment_ID) . '">' . get_the_title($comment->comment_post_ID) . '</a>') . '</li>';
endforeach; endif;?></ul>

This post should only have one comment. Unfortunately, the query is displaying the same result 24 times. So there has to be something wrong with the loop. Thoughts?

John Woo
  • 238,432
  • 61
  • 456
  • 464
Joel Acevedo
  • 33
  • 4
  • Thanks to JW, I fixed the issue: get_results("SELECT * FROM $wpdb->comments INNER JOIN $wpdb->commentmeta ON($wpdb->comments.comment_ID = $wpdb->commentmeta.comment_id) WHERE comment_post_ID = '256' AND comment_approved = '1' AND meta_key = 'bestcomment' AND meta_value = 'yes' "); ?> – Joel Acevedo Jan 15 '13 at 06:36

1 Answers1

2

There is nothing wrong with the loop. The problem is that your query produces cartesian product (CROSS JOIN) because you didn't specify the column on which the two tables should be link.

The fastest answer I can give is to define the linking columns on the ON clause,

$comments = $wpdb->get_results("
SELECT * 
FROM $wpdb->comments a
      INNER JOIN wp_commentmeta b
         ON a.ColumnName = b.Columnname           <== define here
WHERE comment_post_ID = '256' AND 
      comment_approved = '1' AND 
      meta_key = 'bestcomment' AND 
       meta_value = 'yes' ");

just change ColumnName into the real name of the column in your table.

To learn more about JOIN, please take a look at the article below,

John Woo
  • 238,432
  • 61
  • 456
  • 464
  • 1
    You were completely right! Thanks. I changed it and now it is displaying correctly. – Joel Acevedo Jan 15 '13 at 06:34
  • As a sidenote, the query is vulnerable with [`SQL Injection`](http://bobby-tables.com/) if the value(*s*) came from the outside. Please take a look at the article below to learn how to prevent from it. By using `PreparedStatements` you can get rid of using single quotes around values. [see How to *prevent SQL injection* in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) – John Woo Jan 15 '13 at 06:35