Sorting results by time and date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
goochy808
Forum Newbie
Posts: 1
Joined: Fri Sep 12, 2008 8:00 am

Sorting results by time and date

Post by goochy808 »

Hi

I have written a flash/php app that pulls records out of a MySQL database and populates variables in the flash movie. That it works OK but what I am trying to do is the following:

I have a series of football fixtures with a date and kick-off time. I want to be able to automatically show the next game in the list at the time it is requested but for some reason it pulls up the right date but not the right time.

How do I select the next result according to TIME and DATE?

I am using dreamweaver connecting to MySQL through PHP. What would be the correct syntax for the MySQL/PHP query?

Many thanks

Peter
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Sorting results by time and date

Post by jaoudestudios »

What is your current query?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Sorting results by time and date

Post by jayshields »

What do you mean it pulls up the right date but not the right time?

You'll probably want to use an ORDER BY and LIMIT clause, something like this:

Code: Select all

ORDER BY 
  `date_time` DESC 
LIMIT 1
BETA
Forum Commoner
Posts: 47
Joined: Fri Jul 25, 2008 3:21 am

Re: Sorting results by time and date

Post by BETA »

Mm yeah i would use NOW() and save it to a datetime table so it saves the time and date and use DATE_FORMAT(datetablename,' %d/%m/%Y, %H:%i:%s') as datevariablename FROM tablename ORDER BY DATE DESC";
hope it helps!
Post Reply