Solved

Sort date style month.day.year

Posted on 2004-08-04
2
814 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 7

Accepted Solution

by:
madwax earned 100 total points
ID: 11721749
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
ID: 11721755
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Insert values are dynamic 11 71
PHP query / monitor data from Telnet to MySQL 8 128
MYSQL responding very slow 3 68
Building a glossary into your website 9 60
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 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