order by date?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

order by date?

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it can be any of the numeric types or the date types..
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post 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
Last edited by pinehead18 on Tue Aug 17, 2004 6:00 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your date field is stored as a varchar or one of it's text siblings?
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post by pinehead18 »

varchar(25)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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?
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post 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/
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

yep.
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post by pinehead18 »

Thank you
Post Reply