PHP MySQL Query with date field being incorrect
Posted: Sat Oct 31, 2009 4:17 pm
I am having an issue with an SQL query of mine, and I am not too sure if it has anything to do with my date code. But, I can add data to my DB fine and read it some what fine and sort it by Date. But here's the problem:
I have a few set variables that read:
Each return and display correctly. However, when I do an SQL Query and ORDER BY comments_date DESC, comments_time DESC, it works to some degree.. As my date format in the date field is dd-mm-yyyy, it will order only by the first "dd" and ignore the "-mm-yyyy" ordering. What this means is, we just finished up in October and are now in November When I display all data, it's ordering by days and ignoring the rest of what's in the date field being -mm-yyyy. So as it has rolled over into November, it's showing all of October data from the 1st of october to the 31st of October and now instead of showing November data on top of October data, it is showing the 01-11-2009 data along side the 01-10-2009 data. It's sorting by days and not the whole dd-mm-yyyy as what's in the fields in the DB :*(
Just FYI, here's my date code:
Code: Select all
$result = mysql_query("SELECT * FROM tbl_comments WHERE comments_date <= '$datetodaydate' AND comments_date <= '$dateyesterdaydate' ORDER BY comments_date DESC, comments_time DESC");Code: Select all
$datetodaydate // This will display current date DD-MM-YYYY
$dateyesterdaydate // Yesterday's DD-MM-YYYY
$datedaybefore // Day before yesterday's DD-MM-YYYYJust FYI, here's my date code:
Code: Select all
// ##### Date and Timezone Formatting ##### Start ->
$currenttime=gmt2aest(strftime("%Y-%m-%d %H:%M:%S"));
$tmp0=explode(" ",$currenttime);
$currdate=$tmp0[0];
$currtime=$tmp0[1];
$datetoday=gmt2aest(strftime('0 days'));
$tmp1=explode(" ",$datetoday);
$datetodaydate=$tmp1[0];
$datetodaytime=$tmp1[1];
$dateyesterday=gmt2aest(strftime('-1 days'));
$tmp2=explode(" ",$dateyesterday);
$dateyesterdaydate=$tmp2[0];
$dateyesterdaytime=$tmp2[1];
$datedaybefore=gmt2aest(strftime('-2 days'));
$tmp3=explode(" ",$datedaybefore);
$datedaybeforedate=$tmp3[0];
$datedaybeforetime=$tmp3[1];
// Daylight Savings function
function gmt2aest ($time){
date_default_timezone_set('GMT');
$date = new DateTime($time);
$aest_time= new DateTimeZone('Australia/Hobart');
$date->setTimezone($aest_time);
$newtime = $date->format("d-m-Y H:i:s");
return $newtime;
}
// Daylight Savings function
function aest2gmt ($time){
date_default_timezone_set('Australia/Hobart');
$date = new DateTime($time);
$gmt_time=new DateTimeZone('GMT');
$date->setTimezone($gmt_time);
$newtime = $date->format("d-m-Y H:i:s");
date_default_timezone_set('GMT');
return $newtime;
}
// ##### Date and Timezone Formatting ##### End <-