Posted 2 years ago #
Hi, I would like to display on each of my main categories page, the last comments posted. I can already get the last comments posted but they are from all my blog categories. How could I choose the last comments from a specific category/ID?
I don't want to use a plugin, I would like to this with some code if possible...
Thanks for your help!
Are you already using some code to get the comments? Perhaps it can be modified. In particular, if you are using a SQL query, post it for me to see.
Posted 2 years ago #
Hi vtxyzzy! Here is my code:
<?php
global $wpdb;
$sql = "SELECT DISTINCT ID, post_title, post_password, comment_ID, comment_post_ID, comment_author, comment_date, comment_approved, comment_type,comment_author_url, SUBSTRING(comment_content,1,100) 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 DESC LIMIT 4";
$comments = $wpdb->get_results($sql);
$output = $pre_HTML;
$output .= "\n";
foreach ($comments as $comment) {
$output .= "« ID)."#comments" . "\" title=\"Comment on ".$comment->post_title . "\">" . strip_tags($comment->com_excerpt)."... » Comment on ". strip_tags($comment->comment_author) ." , ". strip_tags($comment->comment_date) ."";
}
$output .= "\n";
$output .= $post_HTML;
echo $output;
?>Thanks for your help :)
I think this will do what you want. Replace the '??catidn' with your own category id's.
$sql =
"SELECT DISTINCT ID, post_title, post_password, comment_ID, comment_post_ID,
comment_author, comment_date, comment_approved, comment_type,comment_author_url,
SUBSTRING(comment_content,1,100) AS com_excerpt
FROM $wpdb->comments
LEFT OUTER JOIN $wpdb->posts ON ($wpdb->comments.comment_post_ID = $wpdb->posts.ID)
INNER JOIN $wpdb->term_relationships as r1 ON ($wbdb->posts.ID = r1.object_id)
INNER JOIN $wpdb->term_taxonomy as t1 ON (r1.term_taxonomy_id = t1.term_taxonomy_id)
WHERE comment_approved = '1'
AND comment_type = ''
AND post_password = ''
AND t1.taxonomy = 'category'
AND t1.term_id IN ('??catid1','??catid2')
ORDER BY comment_date DESC LIMIT 4";
Posted 2 years ago #
Hi vtxyzzy! I replaced my $sql query by yours and replaced the '??catidn' with my own category id but unfortunately, it doesn't show anything. The page is blank, nothing.
Any idea why?
Looks like I have a typo - change $wbdb->posts.ID to $wpdb->posts.ID and try again.
If this works, please be sure to mark this topic 'Resolved'.
Posted 2 years ago #
I corrected the typo but it still doesn't work :(
My full code is:
<?php
global $wpdb;
$sql =
"SELECT DISTINCT ID, post_title, post_password, comment_ID, comment_post_ID,
comment_author, comment_date, comment_approved, comment_type,comment_author_url,
SUBSTRING(comment_content,1,100) AS com_excerpt
FROM $wpdb->comments
LEFT OUTER JOIN $wpdb->posts ON ($wpdb->comments.comment_post_ID = $wpdb->posts.ID)
INNER JOIN $wpdb->term_relationships as r1 ON ($wpdb->posts.ID = r1.object_id)
INNER JOIN $wpdb->term_taxonomy as t1 ON (r1.term_taxonomy_id = t1.term_taxonomy_id)
WHERE comment_approved = '1'
AND comment_type = ''
AND post_password = ''
AND t1.taxonomy = 'category'
AND t1.term_id IN ('10','20')
ORDER BY comment_date DESC LIMIT 4";
$comments = $wpdb->get_results($sql);
$output = $pre_HTML;
$output .= "\n";
foreach ($comments as $comment) {
$output .= "« ID)."#comments" . "\" title=\"Comment on ".$comment->post_title . "\">" . strip_tags($comment->com_excerpt)."... » Comment on ". strip_tags($comment->comment_author) ." , ". strip_tags($comment->comment_date) ."";
}
$output .= "\n";
$output .= $post_HTML;
echo $output;
?>I don't know why it doesn't work...
If I copy the query and replace '$wpdb->' with my database prefix, and put in my categories, the query works for me.
If you are sure you have comments which fit the selection, dump out $comments to see if it has what you expect.
Otherwise, I have no clue why it doesn't work.
Posted 2 years ago #
I know why it doesn't work... Here are my categories:
Books
- Reviews
-- Fantasy
Books category ID is 10, and I want to show all the comments posted in that category, including comments posted in both Reviews and Fantasy. I have many categories (like 250) so I can't checked for everyone of them:
AND t1.term_id IN ('10','11', ... , '250')
The solution would be to get the comments from the main category: Books. Is that possible? I hope so... Or I'm really stucked...
Thanks a lot!
Please give this a try:
<?php
global $wpdb;
$selected_category = '10'; // Put your parent category id here
$children = get_categories("child_of=$selected_category");
$inlist = "$selected_category";
foreach ($children as $cat) {
$inlist .= ',' . $cat->cat_ID;
}
$sql =
"SELECT DISTINCT ID, post_title, post_password, comment_ID, comment_post_ID,
comment_author, comment_date, comment_approved, comment_type,comment_author_url,
SUBSTRING(comment_content,1,100) AS com_excerpt
FROM $wpdb->term_taxonomy as t1, $wpdb->posts, $wpdb->term_relationships as r1, $wpdb->comments
WHERE comment_approved = '1'
AND comment_type = ''
AND ID = comment_post_ID
AND post_password = ''
AND ID = r1.object_id
AND r1.term_taxonomy_id = t1.term_taxonomy_id
AND t1.taxonomy = 'category'
AND t1.term_id IN ($inlist)
ORDER BY comment_date DESC LIMIT 4";
$comments = $wpdb->get_results($sql);
$output = $pre_HTML;
$output .= "\n";
foreach ($comments as $comment) {
$output .= "« ID"."#comments" . "\" title=\"Comment on ".$comment->post_title . "\">" . strip_tags($comment->com_excerpt)."... » Comment on ". strip_tags($comment->comment_author) ." , ". strip_tags($comment->comment_date) ."";
}
$output .= "\n";
$output .= $post_HTML;
echo $output;
?>
Posted 2 years ago #
Thank you very much vtxyzzy, it works perfectly!! :)
(only thing not working well is how the comments appear, I corrected the HTML and it's perfect).
I wish I could help the others as much as you do but unfortunately I'm not good enough... But I'm learning.
Thanks a lot, you made my day! :)
Posted 1 year ago #
How can you format the $comment->comment_date ?
I'd like to have it like '17 june 2010'. Thanks!
Posted 1 year ago #
@AdmireNL
Me too!
Posted 1 year ago #
Ha!
<?php echo mysql2date('l jS F, Y, g:ia', $comment->comment_date);?>
This topic has been closed to new replies.
