Ordering an SQL database through PHP

Question

I am trying to order a database by date but I am having trouble sorting it. The database is created in SQL through PHP and the column I want to use to sort by adds the date data in this format:

2011-10-26 07:10

The above date is also a 'varchar'. EDIT: changed to 'datetime' thanks to suggestions

Any idea so that I can sort the rows in the table using the date column?

I made a separate script to narrow this problem down:

<?php
$con = mysql_connect("localhost","root","");
mysql_select_db("thoughtsdata",$con);


$order = "SELECT * 
FROM  `thoughts` 
ORDER BY  `thoughts`.`DateTime` ASC 
LIMIT 0 , 30";

if(!mysql_query($order,$con))
{
die('Could not order database: ' . mysql_error());
}


mysql_close($con);
?>

I now get no errors when running this script in my browser but my table remains unsorted:

  Edit    Inline Edit     Copy   Delete Hey sdfasdf    2011-10-26 19:41:00
  Edit    Inline Edit     Copy   Delete Hey asdfasdf    2011-10-26 07:47:00 
  Edit    Inline Edit     Copy   Delete Hey dfasdfasdf    2011-10-26 19:47:00   
  Edit    Inline Edit     Copy   Delete aaa how to sort this    2011-10-26 07:54:00 
  Edit    Inline Edit     Copy   Delete aaa any progress!!??    2011-10-26 08:13:00 
  Edit    Inline Edit     Copy   Delete oh no!  grrr    2011-10-26 08:21:00 
  Edit    Inline Edit     Copy   Delete zzTOp   thinking about sorting this out!    2011-10-27 19:12:00

What seems to me happening, is that the database is sorted but it does not stay like that.

wow, thanks for the quick response. I was able to change the datatype easily in php to datetime...now I am having trouble sorting the table from the php script. I have this line in php: mysql_query('SELECT * FROM thoughts', 'ORDER BY DateTime'); — Stagleton
– Stagleton, Commented Oct 26, 2011 at 17:57
The correct syntax would be: mysql_query("SELECT * FROM thoughts ORDER BY fieldname"); — WWW
– WWW, Commented Oct 26, 2011 at 18:54
DateTime is the column name....is that the same as the fieldname? — Stagleton
– Stagleton, Commented Oct 26, 2011 at 19:17
Add any output from the script (including all errors) to your post. If there is no output, check your web server error logs to find out what happened. — WWW
– WWW, Commented Oct 26, 2011 at 19:21

WWW · Accepted Answer · 2011-10-26 17:38:24Z

1

You could try using an order clause like this:

ORDER BY UNIX_TIMESTAMP(fieldname) ASC

However, if your field is in YYYY-MM-DD hh:mm format like it looks, an alpha sort should work correctly. This is all under the assumption that you are working with an existing system and can't / aren't allowed to change the field's datatype.

edited Oct 26, 2011 at 17:38

answered Oct 26, 2011 at 17:29

WWW

9,8682 gold badges32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stagleton Over a year ago

I can change the fields data type, but I am having trouble sorting hte column from the php script I made. Any suggestion?

WWW Over a year ago

If you're actually sorting it in the script itself, look at PHP's usort() function: us3.php.net/manual/en/function.usort.php

Stagleton Over a year ago

hmm, I want to sort the data in the SQL table but from the PHP script. Here is what I have tried to execute: mysql_select_db("thoughtsdata",$con); $order = mysql_query("SELECT * FROM thoughts ORDER BY DateTime"); if(!$order,$con) { die('Could not order database: ' . mysql_error()); }

Alexey Morozov · Accepted Answer · 2011-10-26 17:33:01Z

1

Better way to convert it, for ex. with php, to "normal" datetime/timestamp and than use sort.

Main reason to do it is it's far more faster than using sorting by string.

answered Oct 26, 2011 at 17:33

Alexey Morozov

1,28910 silver badges12 bronze badges

Comments

JonH · Accepted Answer · 2011-10-26 17:29:59Z

0

You can CAST or convert the varchar to a real datetime, that is what it should of been since it is really a datetime.

answered Oct 26, 2011 at 17:29

JonH

33.2k13 gold badges94 silver badges149 bronze badges

Collectives™ on Stack Overflow

Ordering an SQL database through PHP

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related