CODEWITHSUNDEEP
phpsqlwordpress
user2565035
user2565035

Reputation: 1

Wordpress sql query for last comment date

i would like to get date gmt of latest comment for post ID. Result i would like to get as a string.

Can somebody help me how to set result into string:

function GetLastCommentDate( $postId ) {
        global $wpdb;
        $dateOutput = '0000-00-00 00:00:00';

        $commentRes= $wpdb->get_results("SELECT DISTINCT `comment_date_gmt` FROM `wp_comments` WHERE `comment_approved` ='1' AND `comment_post_ID` = '". $postId. "' ORDER BY `comment_date_gmt` DESC LIMIT 1");
        if(!empty($commentRes)) {
            $dateOutput =  ...........
        }
        return $dateOutput;
    }

One answer is like this:

$commentRes= $wpdb->get_results("SELECT DISTINCT `comment_date_gmt` as `comment_date_gmt` FROM `wp_comments` WHERE `comment_approved` ='1' AND `comment_post_ID` = '". $postId. "' ORDER BY `comment_date_gmt` DESC LIMIT 1");
        if(!empty($commentRes)) {
            foreach($commentRes as $comment) {
                $dateOutput=$comment->comment_date_gmt;
            }
        }
        return $dateOutput;

But how to avoid foreach loop? There is only one row (sql limit set to 1).

Upvotes: 0

Views: 1215

Answers (3)

Ricardo Gonçalves
Ricardo Gonçalves

Reputation: 5094

I've stumbled upon this while searching for something else. I know that the question is old, and the aswers too, but somebody can find it like I did, and I'd like to add another updated solution.

function GetLastCommentDate( $postId ) {
    $dateOutput = false; //this will make easier to check if there are no comments
    //get_comments() args https://codex.wordpress.org/Function_Reference/get_comments
    $args = array(
         'post_id'  => $postId, //just comments of this post
         'number'   => 1, //just one comment
         'order_by' => 'comment_date_gmt', //order by comment date gmt
         'order'    => 'DESC', //latest first
         );
    //retrieve comments
    $comments = get_comments($args);
    if ($comments){
       $dateOutput = $comments[0]->comment_date;
    }
    return $dateOutput;
}

And you can use it wherever you want like this:

$postId = '12345';
$lastCommentDate = GetLastCommentDate($postId) ?: 'Never';
echo $lastCommentDate;

Upvotes: 0

bhaskarc
bhaskarc

Reputation: 9521

You need not query wordpress database directly. WP provides an API to retrieve this.

 $comments = get_comments( array(
    'post_id' => $post->ID,
    'orderby' => 'comment_date_gmt',
    'status' => 'approve', 
    'number' => 1 
  ) );

Check out this API reference. Specifying number as 1 returns only the last comment.

The date value for last comment can be retrieved as $comments[0]['date']

Now that you want to use this from outside, include the following at top of your php code

require('/the/path/to/your/wp-blog-header.php');

Check out this wordpress doumentation

If you get an out of loop error try adding this code.

The loop starts here:

<?php if ( have_posts() ) : while ( have_posts() ) : the_post(); ?>

and ends here:

<?php endwhile; else: ?>
 <p><?php _e('Sorry, no posts matched your criteria.'); ?></p>
<?php endif; ?>

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180987

I think you want something like this;

$commentRes= $wpdb->get_row(
      "SELECT `comment_date_gmt` FROM `wp_comments` ".
      "WHERE `comment_approved` ='1' AND `comment_post_ID` = '$postId' ".
      "ORDER BY `comment_date_gmt` DESC LIMIT 1");

if(!empty($commentRes))
    $dateOutput = date("Y-m-d H:i:s", $commentRes->comment_date_gmt);

Upvotes: 0

Related Questions