Sort By Date???

[Syntax12]

I'm trying to sort by the date for some mysql results... and in the table, the date field is "date" format, so the rows have like, 2009-3-5...

however when I sort by the date... it doesn't work right. I'm guessing PHP is treating it as a math problem in my query. How can I fix this? I've tried quotes around date. Any ideas?

thanks

[nav33n]

select * from table order by datecolumn desc

Will work fine. How does php consider this as a math problem ? What is the query you are using ?

[danielpataki]

Yes, the code above should work for you. In fact, even if you used a non-date format it should work.

And also, please post the code you are using in php, since so many other things could go wrong. If I don't know if the query is wrong or the PHP I always run the query in PhpMyAdmin without all the PHP code. If it works there the error is in your PHP, not the query itself.

[almostbob]

the error is caused by storing the date as a text string in a 'date' formatted field
the database is not meant to be human readable, data constructs for human readability mess things up
the date should be stored as a unix timestamp, php strtotime() time() sql now()
solely numeric representation, in a numeric field
then sort by, and all other functions will work
the database will be smaller and faster and the operations on the data will be likewise faster
not an issue now perhaps, but eventually there may be millions of records in the database
the timestamp is output in human readable form by php date time functions.

[danielpataki]

I agree that databases are not meant to be read by humans, but I disagree that this is causing the error.

If a field is in the datetime format for example you will still have no problem sorting it. Just as yuo will have no problem sorting text fields either, since it will be sorted alphabetically.

Therefore the error I think is not caused by the database setup (obviuosly this might not be true).

However, on all the other parts, I agree with almostbob.

[NormandP]

2009-03-05 is a better way to store textual datetime than 2009-3-5... and will probably sort correctly !

I agree with almostbob : Consider to convert your textual datetime description into a timestamp with strtotime

[danielpataki]

Yes, I agree, in my last post I mean timestamp, not datetime. Timestamp is also a great way to hold dates because MySQL can insert the current timestamp in when you insert entries automatically.

[cwarn23]

Also it is possible to reorder the date in a different format than it is recorded. So say you had Year-Day-Month, you would use the following to order it and display it as Day-Month-Year while in order:

//set date array to list of dates in y-d-m format
$date=array('2009-01-05','2009-01-15','2009-01-14');

for ($i=0;isset($date[$i]);$i++) {
$datess=explode('-',$date[$i]);
$dates[$i]=$datess[0].'-'.$datess[2].'-'.$datess[1];
unset($datess);
}
unset($date);
asort($dates);
for ($i=0;isset($dates[$i]);$i++) {
$datess=explode('-',$dates[$i]);
$date[$i]=$datess[1].'-'.$datess[2].'-'.$datess[0];
unset($datess);
}
print_r($date);

[james hank]

its suprising to find out that most of you web designers/developers don't know how to get the codes. So this is how you can get any html, php, ect.... code from any website like facebook, deviantart..ect. all you have to do is on Internet Explorer you hit on upper tab bar where it says View down to where it says source when you are on a website.
Now close the website window or minimize the screen and behind it is the page with the codes. Remember you have to link pages that exist in a website for it to work like a gallery has to be linked to login and submit button page once you copy and paste the codes to dreamweaver or save it.

Basically any important pages in a website that would make it properly work. Some unnecessary things like selling materials codes need to be taken out. All this can been done in Adobe dreamweaver which you can download as a trial version for 30 days. The bad thing is you have to replace the website links in the scripts with your own website links to make it into your own websites which is the only thing you have to do. So there aren't you glad know you have a blueprint for your website. In dreamweaver the features you don't need you can take out easily. So hit internet explorer tab -view, source.

CSS links should be deleted to prevent it from misdirecting it to the original website.

[almostbob]

its suprising to find out that most of you web designers/developers don't know how to get the codes. So this is how you can get any html, php, ect.... code from any website like facebook, deviantart..ect. all you have to do is on Internet Explorer you hit on upper tab bar where it says View down to where it says source when you ........

your ignorance is amazing,
view source only shows the output html, and only where the site is not secured, and does not display the asp jsp php that makes the site run, and is not output to browser. this thread is entirely about serverside code

[EvolutionFallen]

+1 almostbob.
Also, thank you, james hank, for creating an account on this site solely for the purpose of 1) flaming a bunch of intelligent, knowledgeable folk kindly offering their excellent guidance, 2) providing misinformed, irrelevant advice, 3) reviving a thread that has been dead for a year and a half, and finally (and possibly worst of all :twisted:) 4) referencing IE and Dreamweaver as design/development tools in a serious web design forum.

You are full of win.