Change order of records from MySQL in PHP

Question

How can I change the order by time either in MySQL or in the While Loop?

I have a query like

`"SELECT `start-time` from `table_name`"`

and my start-time will be in format 02:00pm, 02:00am, 05:00am, in likewise. If that's just number without am/pm I can use order by.

Or, if the above way not possible, can I use PHP to order the start time in while loop?

I am using the following way to retrieve the data

$doquery = mysql_query("SELECT `start-time` FROM `table_name`");
while($fetch_query = mysql_fetch_array($doquery)){
echo $fetch_query['start-time'];
}

Thanks

so, `start-time` is a string, not a datetime? why would you do that? you can always format it how you want on output. The easiest way would be to use MySQL's `ORDER BY`. Also, MySQL is deprecated, use MySQLi or PDO instead. — serakfalcon, Aug 26 '14 at 01:48

score 1 · Answer 1 · answered Aug 26 '14 at 01:53

Are you storing your times as TIMESTAMP in MySQL? If not, you should be. The most efficient way to sort the results is going to be by adding an ORDER BY clause to your SQL statement. For instance, to sort so that the most recent times occur first in the result set, try this:

SELECT `start-time` FROM `table_name` ORDER BY `start-time` DESC;

To order so that the most recent times occur last in the result set, change the query to:

SELECT `start-time` FROM `table_name` ORDER BY `start-time` ASC;

The other thing that I would encourage you to explore is PDO. All of the mysql_* PHP functions are deprecated. Prepared queries are a lot more secure and flexible. There are a lot of tutorials out there... this one looks alright: http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html Anyway hope that helps. Happy coding.

You would format the value when necessary in PHP, but no, AM and PM wouldn't be stored in the actual MySQL DB. See http://stackoverflow.com/questions/136782/convert-from-mysql-datetime-to-another-format-with-php — Nicholas Byfleet, Aug 26 '14 at 02:00

Edper · Answer 2 · 2014-08-26T02:01:18.080

If it is simply a string you could use STR_TO_DATE or CASE WHEN, like:

Using STR_TO_DATE

SELECT `start-time` from `table_name`
ORDER BY STR_TO_DATE(start-time,'%h.%i%p')

Using CASE

SELECT `start-time` from `table_name`
ORDER BY
CASE WHEN start-time LIKE '%am%'
  THEN 0
  ELSE 1
END, start-time

Bu I agree with others you could probably store this as TIMESTAMP and use mySQLi or PDO for your database handling in php.

Change order of records from MySQL in PHP

2 Answers2