WordPress get_results按发布日期排序

Question

I'm using "get_results" in WordPress for the first time and need to order the results by date. What I've got so far is:

$wpdb->get_results( "SELECT * FROM asi_posts WHERE post_type = 'post' AND post_status = 'publish' AND YEAR(post_date) = '" . $year->year . "'" );

This works fine in retrieving the posts, although it displays them with the oldest at the top of the list, instead of the most recent.

What's the correct syntax that I should be using in that query to reverse the order?

Thanks.

In case it's more useful, here's the full code:

<?php
                // Get years that have posts
                $years = $wpdb->get_results( "SELECT YEAR(post_date) AS year FROM asi_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP BY year DESC" );

                //  For each year, do the following
                foreach ( $years as $year ) {

                    // Get all posts for the year
                    $posts_this_year = $wpdb->get_results( "SELECT * FROM asi_posts WHERE post_type = 'post' AND post_status = 'publish' AND YEAR(post_date) = '" . $year->year . "'" );

                    // Display the year as a header
                    echo '<div class="year-header-block">';
                    echo '<button class="btn-year-reveal">View</button>';
                    echo '<h3>' . $year->year . ' Industry News &amp; Comment</h3>';
                    echo '</div>';

                    // Start an unorder list
                    echo '<ul class="no-list yearly-archive-list">';

                    // For each post for that year, do the following
                    foreach ( $posts_this_year as $post ) {
                        // Display the title as a hyperlinked list item
                        echo '<li><a href="' . get_permalink($post->ID) . '"><span class="news-title">' . $post->post_title . '</span><span class="news-date">' . get_the_time('F j, Y', $post->ID) . '</span></a></li>';
                    }

                    // End the unordered list
                    echo '</ul>';
                }
            ?>

Answer 1

You should ORDER BY xxx ASC instead of DESC to get the oldest first as follows:

$years = $wpdb->get_results( "SELECT YEAR(post_date) AS year FROM asi_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP BY post_date ASC" );

Also, if you want to order the other query, it should look like this:

$wpdb->get_results( "SELECT * FROM asi_posts WHERE post_type = 'post' AND post_status = 'publish' AND YEAR(post_date) = '" . $year->year . "' ORDER BY post_date ASC" );

Note: by default SQL always orders ascendantly, so you can remove the instruction ASC. But if you want it order older first you replace ASC for DESC.