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'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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
    
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
    
Awesome, thanks Milo - worked perfectly! –  gerrod Jul 23 '11 at 20:42
add comment

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.