CODEWITHSUNDEEP
phpmysql
Karthik Malla
Karthik Malla

Reputation: 5820

Change order of records from MySQL in PHP

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

Upvotes: 0

Views: 103

Answers (2)

Edper
Edper

Reputation: 9322

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.

Upvotes: 0

Nicholas Byfleet
Nicholas Byfleet

Reputation: 579

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.

Upvotes: 1

Related Questions