?
Solved

most popular comments PHP

Posted on 2011-02-23
5
Medium Priority
?
524 Views
Last Modified: 2012-05-11
Hi,
How can I edit following code to display most commented posts first?

<?php
global $wpdb;
$sql = "SELECT DISTINCT ID, post_title, post_password, comment_ID,
comment_post_ID, comment_author, comment_date_gmt, comment_approved,
comment_type,comment_author_url,
SUBSTRING(comment_content,1,30) AS com_excerpt
FROM $wpdb->comments
LEFT OUTER JOIN $wpdb->posts ON ($wpdb->comments.comment_post_ID =
$wpdb->posts.ID)
WHERE comment_approved = '1' AND comment_type = '' AND
post_password = ''
ORDER BY comment_date_gmt ASC
LIMIT 5";
$comments = $wpdb->get_results($sql);
$output = $pre_HTML;
$output .= "\n<ul>";
foreach ($comments as $comment) {
$output .= "\n<li>".strip_tags($comment->post_title)
.":" . "<a href=\"" . get_permalink($comment->ID) .
"#comment-" . $comment->comment_ID . "\" title=\"on " .
$comment->post_title . "\">" . strip_tags($comment->com_excerpt)
."</a></li>";
}
$output .= "\n</ul>";
$output .= $post_HTML;
echo $output;?>

Open in new window

0
Comment
Question by:static86
  • 2
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
LAMASE earned 1600 total points
ID: 34962929
Do you want to show the comments or the posts? for the posts add a COUNT(*) as NUM_COMMENTS in the select, GROUP BY post_ID and ORDER BY NUM_COMMENTS. DESC
0
 

Author Comment

by:static86
ID: 34962982
I want to display most popular posts.
Following code does not return anything:(.
<?php
global $wpdb;
$sql = "SELECT DISTINCT ID, post_title, post_password, comment_ID,
comment_post_ID, comment_author, comment_date_gmt, comment_approved,
comment_type,comment_author_url,
SUBSTRING(comment_content,1,30) AS com_excerpt COUNT(*) as NUM_COMMENTS
FROM $wpdb->comments
LEFT OUTER JOIN $wpdb->posts ON ($wpdb->comments.comment_post_ID =
$wpdb->posts.ID)
WHERE comment_approved = '1' AND comment_type = '' AND
post_password = ''
GROUP BY post_ID
ORDER BY NUM_COMMENTS.DESC
LIMIT 5";
$comments = $wpdb->get_results($sql);
$output = $pre_HTML;
$output .= "\n<ul>";
foreach ($comments as $comment) {
$output .= "\n<li>".strip_tags($comment->post_title)
.":" . "<a href=\"" . get_permalink($comment->ID) .
"#comment-" . $comment->comment_ID . "\" title=\"on " .
$comment->post_title . "\">" . strip_tags($comment->com_excerpt)
."</a></li>";
}
$output .= "\n</ul>";
$output .= $post_HTML;
echo $output;?>

Open in new window

0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34963448
Does $sql query return anything when you run it through phpmyadmin ?
Just echo $sql; and copy the result into pma.
0
 

Author Comment

by:static86
ID: 34963508
The first code works perfectly. It displays most recent comments. Instead of that I want to display first 5 posts which are most popular (are most commented ).
0
 
LVL 27

Assisted Solution

by:Lukasz Chmielewski
Lukasz Chmielewski earned 400 total points
ID: 34963551
You're missing , in the second query
"...com_excerpt, COUNT(*)..."
0

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
The viewer will learn how to count occurrences of each item in an array.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question