Page 1 of 1

PHP MySQL Query with date field being incorrect

Posted: Sat Oct 31, 2009 4:17 pm
by julzk
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:

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");
I have a few set variables that read:

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-YYYY
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

// ##### 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 <-

Re: PHP MySQL Query with date field being incorrect

Posted: Sat Oct 31, 2009 4:58 pm
by deejay
Hi

what is the 'type' of the field 'comments_date' . If it's 'date' then I think you shouldn't have a problem with the ordering.

Re: PHP MySQL Query with date field being incorrect

Posted: Sat Oct 31, 2009 5:04 pm
by julzk
comments_date field type is varchar(25). I changed the type to Date and it wiped all the date data, but every field now has 0000-00-00

So I do not have to change a bunch of stuff around, is there another way of doing it? For example, adding something like

Code: Select all

DATE_FORMAT(tbl_comments.comments_date, '%d %m %Y')
or

Code: Select all

CONVERT(VARCHAR(25), GETDATE(), 105) AS [DD-MM-YYYY]
?

My current query is:

Code: Select all

$result = mysql_query("SELECT * FROM tbl_comments WHERE comments_date ORDER BY comments_date DESC, comments_time DESC");

Re: PHP MySQL Query with date field being incorrect

Posted: Sat Oct 31, 2009 6:52 pm
by califdon
When you're dealing with dates, you should always use inherent date formats, not varchar. That's what it's for, so you can do things like sort, search, compare, etc. When you don't use date formats, you have to invent your own ways of doing all those things, treating every value like a string, and also allowing users to input invalid dates like February 30, etc. I wouldn't even begin to try to work with dates that are not stored as dates.

Re: PHP MySQL Query with date field being incorrect

Posted: Wed Nov 04, 2009 1:12 am
by deejay
califdons right I'm affraid, you'll be forever having problems with your program unless you change the field over to 'date' . Best to cut your loses and go back to the drawing board IMO