0

I'm definitely a rookie WordPress/PHP developer, so please forgive ignorance :-)

Q: How do I format a date/time returned from a custom query against $wpdb?

Here's the scenario: I'm trying to write a page which displays some simple statistics about my blog. One of the statistics I'm showing is the most popular posts, as determined by comment count. I've essentially got the query working, except I can't for the life of me work out how to format the post date/time the way I want to! Here the code (apologies for the long snippit):

<?php
    $queryStr = "
        SELECT 
            posts.ID,
            posts.post_title,
            posts.post_date,
            posts.guid,
            author.user_nicename as author,
            count(comments.comment_ID) as comment_count

        FROM
            $wpdb->posts posts
            INNER JOIN
                $wpdb->comments comments
                ON posts.ID = comments.comment_post_ID
            INNER JOIN
                $wpdb->users author
                ON posts.post_author = author.ID

        WHERE
            posts.post_status = 'publish'
            AND posts.post_type = 'post'
            AND comments.comment_approved = 1
            AND comments.comment_type = ''

        GROUP BY
            posts.ID

        ORDER BY
            count(comments.comment_ID) DESC,
            posts.ID DESC

        LIMIT
            30
    ";

    $popularPosts = $wpdb->get_results($queryStr);

    if ($popularPosts) {
?>
    <table class="stats_table">
        <thead>
            <tr>
                <th>Post</th>
                <th>Author</th>
                <th>Posted At</th>
                <th># comments</th>
            </tr>
        </thead>
        <tbody>

<?php
        foreach ($popularPosts as $popPost) {
?>
            <tr>
                <td><a href="<?= $popPost->guid ?>"><?= $popPost->post_title ?></a></td>
                <td><?= $popPost->author ?></td>
                <td><?= $popPost->post_date ?></td>
                <td><?= $popPost->comment_count ?></td>
            </tr>
<?php           
        }
?>
        </tbody>
    </table>
<?php
    } else {
        echo("<strong>No stories!</strong>\n");
    }
?>

This is obviously the line in question:

<td><?= $popPost->post_date ?></td>

I've tried a number of different things but none of them quite get me there. This gets me the format that I'm after, but uses the wrong date (obviously $wpdb is not returning a timestamp in the query):

<td><?= date('j M Y h:i A', $popPost->post_date) ?></td>

I also tried a solution along these lines:

<?php
        global $popPost;
        foreach ($popularPosts as $popPost) {
            setup_postdata($popPost);
?>
            <tr>
                <td><a href="<?= $popPost->guid ?>"><?= $popPost->post_title ?></a></td>
                <td><?= $popPost->author ?></td>
                <td><?= the_time('j M Y h:i A') ?></td>
                <td><?= $popPost->comment_count ?></td>
            </tr>
<?php           
        }
?>

But that simply repeats the timestamp for the page over and over, rather than the timestamp for each post.

What am I doing wrong?

1

A much simpler method to bypass these issues is using WP_Query:

$popularQuery = array('orderby' => 'comment_count', 'posts_per_page' => '-1', 'author' => 1);
$popularPosts = new WP_Query($popularQuery);
while($popularPosts->have_posts()) : $popularPosts->the_post();
?>
    <tr>
        <td><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></td>
        <td><?php the_author(); ?></td>
        <td><?php the_time('j M Y h:i A') ?></td>
        <td><?php comments_number(); ?></td>
    </tr>
<?php
endwhile;

Also note I've replaced your use of guid with the permalink. In some scenarios the guid of a post will not actually be the permalink, so this should never be assumed. The only exception to this is attachments.

and <?= should be <?php

  • Thanks Milo! I'll give that a try; even though it may solve the problem for this post, I'm displaying other statistics on the page which use a more complex query (so I can't just roll back to WP_Query for them) - so I'd still love to know how to format the date/time using $wpdb. Thanks also for the <?= tip versus <?php - is that just a style thing, or is it convention? The shorthand <?= seems to work ok... – gerrod Jul 23 '11 at 8:47
  • @gerrod - you can use get_the_time('j M Y h:i A', $popPost->ID);. <?= is just non-standard and won't work on a lot of servers. – Milo Jul 23 '11 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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