Dates and Ordering

tribe

Ok Kids here's the Situation:

The boss wanted me to create an application that listed things in order by date. (Our clients were filling out a network request form)

Everything was going great UNTIL....the new year, when it started to order the dates by 2001 instead of 2002. (I need to display my info from newest to oldest.)

I'm using PHP and MySQL. I don't know a lot about development, but the application seemed to be working fine. I think I may have made some mistakes on many levels, and there's great hope that the experts on this board can help me!

I'm storing the dates in the database as:
m - d - Y via PHP

Should I be storing the dates as something else?

How can I make the month/day/year show up with 2002 heading the top of the charts instead of bringing up the rear?

Anxiously waiting a response

amcgrath

Just outta curiousity, is the field in the database a date type?

Anon

should be Y-M-D

tribe

no, I made it varchar, what should I make it?

toma42

You should have made it a date type. You could have then done
select unix_timestamp(datecolumn) from table;
to get your dates in a very easy to work with format.

yendor

For MySQL to order things by date you column would need to be a date type which stores the date as YYYY-MM-DD in. Since you used a column type of varchar and stored the dates as MM-DD-YYYY MySQL is not considering these values as dates.
One possibility, without restructuring your database, is to use PHP to convert the dates to a unix timestamp, then use PHP to sort them. However, this could cause a slight performance hit.
Really the best solution, as Tom said, is to use date type for you MySQL field and the unixtimestamp() function for easy date manipulation. That is if you have the time to restructure your database and adjust any code that writes/reads to/from it.

HTH,

Rodney

tribe

Ok, I have the time to work the code and the db is really new. It's not that big of a deal. I understand the problem but I'm still not clear on some things.

1) I have the form populating the db with $user_date.

$user_date = date('m - d - Y');

how do I use the UNIX_TIMESTAMP() function to populate the DB. (I changed it to date already from varchar)

2) how do I pull the info from the DB to display the same format as month/day/year in PHP?

Sorry for the rook questions, but we are talking ROOK here! You guys have been great so far!

yendor

First of all the MySQL "date" field type only accepts dates in the YYYY-MM-DD (that is (4digit year dash 2digit month dash 2digit day) format so you need :

$user_date = date('Y-m-d');

Watch your letter CaSe, it is important. Y in the PHP date() function is "Year as four digits" whereas y is "Year as two digits". M is "Abbreviated month name(Jan, Feb,...)" whereas m is "Month number from 01 to 12".

I think you get the picture!

Now just insert the var $user_date into the database just like you were before. The UNIX_TIMESTAMP() only comes into play when retrieving the date. PHP uses a unix timestamp for it's date() function. A shortcut to get MM/DD/YYYY would be.

SELECT DATE_FORMAT(datefieldname,"%m/%d/%Y") FROM dbname WHERE blah=blah ORDER BY datefieldname;

This way MySQL will convert the date from YYYY-MM-DD to MM/DD/YYYY, and you don't have to worry about the timestamp.

Hope this makes some sense,

Rodney

yendor

The alternative would be:

SELECT UNIX_TIMESTAMP(datefieldname) FROM dbname WHERE blah=blah ORDER BY datefieldname;

Then you would need to use the PHP date() function to get the format MM/DD/YYYY. Like this:

$ts = result_from_db

$result_date = date("m/d/Y",$ts);

The var $result_date should be the date pulled from the db as MM/DD/YYYY. If all you want to do is convert the date I would use the MySQL example. It's less to type!.!. 😉

Once again hope this is helpful,

Rodney

yendor

Here's some refrences in case I wasn't clear:

http://www.php.net/manual/en/function.date.php <--This one for php date()

http://www.mysql.com/doc/D/a/Date_and_time_functions.html <---This one for both unix_timestamp() and date_format() in MySQL