Querying WordPress with SELECT SQL_CALC_FOUND_ROWS
<p>So, I've recently taken on a job making changes to the WordPress theme for arena . govspace . gov . au and I've been asked to look into why posts posted on the same day do not appear in order according to the time they were posted.</p>

<p>Now, the way the content is pulled in is not the way I am used to doing it in WordPress. I would normally use a WP_Query, so the examples below are a little out of my wheelhouse.</p>

<p>This function is responsible querying for the output for this page: <a href="https://arena.govspace.gov.au/news-media/media-releases/" rel="nofollow">https://arena.govspace.gov.au/news-media/media-releases/</a></p>

<pre><code>function SQLQueryPost( $post_type,$meta_key,&amp;$paged,&amp;$rows,&amp;$total_row, $limit_query = "limit 10"){
    $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;

    global $wpdb;

    $test = $wpdb-&gt;posts;

    $sql = 'SELECT SQL_CALC_FOUND_ROWS meta_value, id, post_title 
            FROM '.$wpdb-&gt;posts.' join '.$wpdb-&gt;postmeta.' on '.$wpdb-&gt;posts.'.id = '.$wpdb-&gt;postmeta.'.post_id 
            WHERE 
            meta_value != "" and meta_key = "'.$meta_key.'"
            and post_status = "publish"
            and post_type = "'.$post_type.'"
            order by STR_TO_DATE( meta_value, "%d-%m-%Y") DESC
            '.$limit_query.' offset '.($paged - 1 )*10;

    $rows = $wpdb-&gt;get_results($sql);

    $total_row = $wpdb-&gt;get_var( "SELECT found_rows();" );

}
</code></pre>

<p>For this query, I simply changed the line <code>order by STR_TO_DATE( meta_value, "%d-%m-%Y") DESC</code> to <code>order by post_date DESC</code> and magically it now orders them by date and time.</p>

<p>This function is responsible for pulling more than one content type for this page: <a href="https://arena.govspace.gov.au/news-media/" rel="nofollow">https://arena.govspace.gov.au/news-media/</a></p>

<pre><code>function SQLQueryPostMulti( $post_types,$meta_key,&amp;$paged,&amp;$rows,&amp;$total_row, $limit_query = "limit 10"){
    $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;

    global $wpdb;

    $test = $wpdb-&gt;posts;

    $sql = 'SELECT SQL_CALC_FOUND_ROWS meta_value, id, post_title, post_type 
            FROM '.$wpdb-&gt;posts.' join '.$wpdb-&gt;postmeta.' on '.$wpdb-&gt;posts.'.id = '.$wpdb-&gt;postmeta.'.post_id 
            WHERE 
            meta_value != ""
            and post_status = "publish"
            and post_type IN (' . $post_types . ')
            order by STR_TO_DATE( meta_value, "%d-%m-%Y") DESC
            '.$limit_query.' offset '.($paged - 1 )*10;

    $rows = $wpdb-&gt;get_results($sql);

    $total_row = $wpdb-&gt;get_var( "SELECT found_rows();" );
}
</code></pre>

<p>This query seems to be a little trickier. I attempted the same change (<code>order by STR_TO_DATE( meta_value, "%d-%m-%Y") DESC</code> to <code>order by post_date DESC</code>) but in this case the page returns the exact same entry for all 10 results.</p>

<p>Basically, if anyone could provide any kind of insight as to how I can make this query not only order the posts by day, but to also order them by time posted on that day, I would be extremely grateful.</p>