Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have this function:

$ids = $wpdb->get_col("SELECT DISTINCT comment_post_ID
FROM $wpdb->comments
ORDER BY comment_date DESC
LIMIT 0 , 30");

foreach ($ids as $id) {
  $post = &get_post( $id );
  setup_postdata($post); ?>
  <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
  <?php
}
?>

Which shows the latest commented posts in a list, which is fine. What I want to do is give a priority to this one and combine it with a "get newest post list". So let's say I commented today on a post called Hello World and someone else submitted a post yesterday... Than I want to get the recent commented post above this new post. The problem is that in my code snippet, there is nothing that says to get the newest posts. How can I combine them? So how to combine most recent commented posts and newest posts with each other? Is this even possible?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Give it a try works perfect for me what it is doing query get the all the posts with a left jon with comments table so when a post has comment then it also has the comment_date if no comments posted on the post then in result set it will be null so i have merged the comment_date with post_date so which post has the greater date (for comment_date or post_date) it will first and so on

SELECT p.*,
(CASE WHEN c.comment_date IS NULL THEN p.`post_date` ELSE c.comment_date END) order_column
 FROM `wp_posts` p
LEFT  JOIN `wp_comments` c  ON (p.ID = c.`comment_post_ID` ) WHERE p.post_type='post' AND p.post_status='publish'
GROUP BY p.ID
 ORDER BY order_column   DESC

For displaying the posts you have to first get the results by defining the WP's global variable for the database interaction i.e $wpdb

<?php
global $wpdb;
$results = $wpdb->get_results("    SELECT p.ID,
    (CASE WHEN c.comment_date IS NULL THEN p.`post_date` ELSE c.comment_date END) order_column
     FROM `wp_posts` p
    LEFT  JOIN `wp_comments` c  ON (p.ID = c.`comment_post_ID` ) WHERE p.post_type='post' AND p.post_status='publish'
    GROUP BY p.ID
     ORDER BY order_column   DESC LIMIT 20"); 
?>

HTML

<?php
foreach ($results as $id) {
  $post = &get_post( $id->ID );
  setup_postdata($post); ?>
  <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
  <?php
} ?>

Hope that is what you were looking for

share|improve this answer
    
I am getting a unexpected T_String error. I put php tags between the mysql code and put it in a variable. That's the way it should be done, right? Thanks btw. If you help me with implemnting it, I can test it thoroughly. –  user1627363 Jul 6 '13 at 10:34
    
My bad there was a space bw <? php remove the space –  M Khalid Junaid Jul 6 '13 at 10:36
    
How do I put the first Mysql code? I tried this, but still got a t_string error: <?php $testingtesting = SELECT p.*, (CASE WHEN c.comment_date IS NULL THEN p.post_date ELSE c.comment_date END) order_column FROM wp_posts p LEFT JOIN wp_comments c ON (p.ID = c.comment_post_ID ) WHERE p.post_type='post' AND p.post_status='publish' GROUP BY p.ID ORDER BY order_column DESC; ?> –  user1627363 Jul 6 '13 at 10:38
1  
you don't need to use the first mysql query it was just an example use the second one $results=get_results("....") this one –  M Khalid Junaid Jul 6 '13 at 10:41
    
Ah, very dumb of me, of course... (upvoted) Dianuj, it frikkin' works! Damn, very much appreciated! I have a last question: how can I limit the outputted list items? I want to show 20 for example (because it is too slow now). –  user1627363 Jul 6 '13 at 10:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.