Page 1 of 1
order by date?
Posted: Tue Aug 17, 2004 5:29 pm
by pinehead18
if i have a mysql statement and want to use order date by desc what type of format does the date have to be in? right now it is in date() php function format. Do i just use sql field date to do the order by? How should it be setup?
Thank you
Anthony
Posted: Tue Aug 17, 2004 5:31 pm
by feyd
it can be any of the numeric types or the date types..
Posted: Tue Aug 17, 2004 5:56 pm
by pinehead18
$sql = "(SELECT author,subject,tid FROM threads) UNION ALL (SELECT
author,subject,tid FROM topics) ORDER date BY DESC LIMIT 8";
That is my code..
This is my date..
Tuesday, Aug, 17
Suggestions?
thank you
Anthony
btw this is my error
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'tid BY DESC LIMIT 8' at line 2
Posted: Tue Aug 17, 2004 6:00 pm
by feyd
your date field is stored as a varchar or one of it's text siblings?
Posted: Tue Aug 17, 2004 6:02 pm
by pinehead18
varchar(25)
Posted: Tue Aug 17, 2004 6:08 pm
by feyd
it'd be a pain to correctly sort that without converting it back into a usable timestamp.. hmmm.. does the field have to be varchar? Why not use a DATE or INT type?
Posted: Tue Aug 17, 2004 6:11 pm
by pinehead18
No, it doens't have to be var char, that is just what i used that the time. After i fix this prob i'm out to purchase a mysql book.
Anyway, What field type do you recamend and how do you recamend for it to work/
Posted: Tue Aug 17, 2004 6:19 pm
by feyd
since I convert all times to GMT for storage, and then to the user's local time when displaying it, I set it as INT..
Posted: Tue Aug 17, 2004 6:30 pm
by pinehead18
Then what format should i set the time in? Should i not use date() anymore? Becuase i'm sure tha tmight be a prob with the order fault?
Posted: Tue Aug 17, 2004 6:39 pm
by feyd
you can still use the date function, just pass it the timestamp that's stored as an INT.. if you're following my usage..
instead of storing the result of date(), you store time() lets say.. then when you want to print out that timestamp.. you use the date function to get the proper naming..
Posted: Tue Aug 17, 2004 6:45 pm
by pinehead18
so, use time() and upload that to the table. In int form. Then pull the time and use the date() function to make that time legable in the same manner that it is currently in?
This will fix the order by date problem of my sql?
Posted: Tue Aug 17, 2004 6:46 pm
by feyd
yep.
Posted: Tue Aug 17, 2004 6:57 pm
by pinehead18
Thank you