Ugh! Just want to sort 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
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Ugh! Just want to sort date

Post by xterra »

I realized that when using plain text datatype fields it can't sort the date. However, when I set it to the DateTime field, my date has to be formatted like this:



0000-00-00 00:00:00


That's fine with me. I just need it to work since Im making a forum and it needs to be sorted by new posts. So I figured to get it to that format I made this code:


$date=date('Y-m-d G:i:s');


But it will not go in the database? Can someone show me how to correctly make the date and time, put it in the database but allow it to be sorted later?


Thanks.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Store it as a normal date time field and use mysql date functions to format it :)

http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

or just use a unix timestamp to sort, and use date() to print the date in a readable format
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post by printf »

I would just store in the database like hawleyjr said, then use the database when selecting to format the date and time in your SELECT query. Less coding is always better!

pif!
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

ill toss down my 2 cents and agree with scottayy. if you store it in unix timestamp it is very easy to order it in a SQL statement and you can always change the layout of the date using the date() function. Different places in the world show the time and the date in different formats and you should be able to show it to them accordingly.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Ugh! Just want to sort date

Post by RobertGonzalez »

xterra wrote:That's fine with me. I just need it to work since Im making a forum and it needs to be sorted by new posts.
Why not sort by post ID instead of date/time? Post ID should be as accurate of a sort field as a date/time field and a lot easier to query/sort by.

As for the time issue, I tend to go with what all the other posters are saying. Use the MySQL UNIX_TIMESTAMP() function or use the strtotime() PHP function along with the date() function.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

All you need to do is use:

INSERT INTO posts(title,body,date) VALUES('$title','$body','NOW()');
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

NOW() shouldn't be quoted ;)
Post Reply