Solved

Sort date style month.day.year

Posted on 2004-08-04
2
810 Views
Last Modified: 2011-09-20
I am looking for help on sorting dates with mysql. Here is a query that I am working with:
$result = mysql_query("select * from shows ORDER by showdate");

This is working but not as I need it to. Here is a sample date systen I want to use:
month.day.year
03.17.04
08.13.04
11.14.04
01.23.05

How can I set up mysql to work with this date system? Note - I am working with PHPAdmin so if you can speak to me in that lingo it would be helpful because I am not fully versed with mysql command prompt. Also does it matter if I use "/" or "." to separate the month.day.year

Thanks
0
Comment
Question by:waffe
2 Comments
 
LVL 7

Accepted Solution

by:
madwax earned 100 total points
Comment Utility
Hi waffe,

I would recommend you saving your dates as timestamps since that will give you the most possible functionality. Namely, a timestamp is a integer which is easy to save, and both PHP and MySQL have built-in functions for dealing with timestamps.

E.g.

1. then you can php to output your timestamp as: <?=date("m.d.y",$timestampFromDB?> where you may change the "m.d.y" to whatever you want by to get _ANY_ output. See: http://se.php.net/manual/en/function.date.php

2. MySQL has the built-in function FROM_UNIXTIME() which you can use as well: See:
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

Feel free to ask more if you don't belive me... :)

Regards,
//madwax


0
 
LVL 17

Assisted Solution

by:akshah123
akshah123 earned 50 total points
Comment Utility
Instead of
$result = mysql_query("select * from shows ORDER by showdate");

try

$result = mysql_query("select * from shows ORDER BY DATE_FORMAT(showdate, '%m/%d/%Y')");
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now