Where子句和Order By未在WordPress中按预期返回

Question

I am positive I am missing something here.

I have a MySQL database table setup with: kdate, kday, kmonth, and kontent

kdate is set with an expires date (admin input) formatted as 01-01-2014 date(m-d-Y)

I am trying to get dates older than today to not show on execution of the script.

global $wpdb;
$today = date('m-d-y');
$result = $wpdb->get_results( "SELECT * FROM wp_kaltable WHERE kdate >= $today ORDER BY kdate ASC    LIMIT 6 ");

//echo "<pre>"; print_r($result); echo "</pre>";

foreach($result as $row)
{

echo "<div class='kalcontain'><div class='kal'><div class='kdate'><p class='kmonth'>".$row->kmonth."</p><p class='kday'>" .$row->kday."</div><div class='kontent'><p class='ktext'>".$row->kontent. "</p></div><div class'clear'></div></div></div>";

}

When I execute this script, I get all data, even the ones with dates less than today's date. Also, when using ORDER BY, it is ordering by month and day, but not year.

All assistance is appreciated.

(*This code is for testing purposes only)

Answer 1

Try instead:

"SELECT * FROM wp_kaltable WHERE kdate >= '$today' ORDER BY kdate ASC    LIMIT 6 "

The single quotes around the $today are important.

If kdate is date format then you could just use CURDATE() from mysql so you don't have to muck around in php:

"SELECT * FROM wp_kaltable WHERE kdate >= CURDATE() ORDER BY kdate ASC    LIMIT 6 "

If kdate is not date format, which it may not be since your ORDER BY isn't working correctly, then neither of those SQL statements will work. You will need to convert to date format in order to the >=