[SOLVED] Ordering by MySQL timestamp?

[Azu]

I have a column in MySQL called datetime in this format[code]1999-1-20 23:50:50[/code]
When I try using[code]order by datetime[/code]it has an effect.. but it doesn't work right. It should order it by years/months/days/hours/minutes/seconds but it doesn't.

And[code]order by ".strtotime(datetime)."[/code]has no effect whatsoever.

Can somebody please tell me how I should do this so that it will properly order?

[NovaHaCker]

You have to select the data from the database in unix timestamp format then order it

[code]
SELECT UNIX_TIMESTAMP(datetime) As timestamp,
          datetime
FROM table
ORDER BY timestamp
[/code]

[Azu]

That gives me the error "Fatal error: Call to undefined function: unix_timestamp()"  :(

Here is the exact code I am using..

[code]$result=mysql_query("SELECT * FROM Topics WHERE Parent=0 ORDER BY Pinned DESC, ".unix_timestamp(datetime)."",$forumdb);[/code]

[NovaHaCker]

[code]
$result=mysql_query("SELECT UNIX_TIMESTAMP(datetime) As timestamp, datetime FROM Topics WHERE Parent=0 ORDER BY timestamp", $forumdb);
[/code]

[NovaHaCker]

Also it is a very good habbit to get into at an early stage to write proper SQL Queries, do not use SELECT * where ever you can, include specific column names in your queries.

eg SELECT column1, colum2, column3 FROM table

It reduces the load on the database and it also protects your scripts from failing if the database is updated in a manner that affects your code.

[trq]

[quote]That gives me the error "Fatal error: Call to undefined function: unix_timestamp()"[/quote]

UNIX_TIMESTAMP is a mysql function, not php. Besides that....

What field type is this table? It needs to be of DATETIME or TIMESTAMP in order to order by it. If it is varchar or something other than a specific data / time data type it wont work.

[Azu]

Thanks. I'm using * because I need to use all of the data from that table.

[code]$result=mysql_query("SELECT UNIX_TIMESTAMP(datetime) as timestamp,* FROM Topics WHERE parent=0 ORDER BY Pinned DESC, timestamp DESC",$forumdb);[/code]and[code]$result=mysql_query("SELECT *,UNIX_TIMESTAMP(datetime) as timestamp FROM Topics WHERE parent=0 ORDER BY Pinned DESC, timestamp DESC",$forumdb);[/code]both give an error;[quote]Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource[/quote]

:( so in other words the query is failing for some reason..

[Azu]

bump =\

[Azu]

hello?  :-\

[alpine]

This one should work as you want it to:
[code]

<?php

$result = mysql_query("SELECT * FROM Topics WHERE Parent=0 ORDER BY UNIX_TIMESTAMP(datetime) DESC",$forumdb) or die(mysql_error());

?>

[/code]

[Azu]

Thanks.. I tried that and it didn't give an error.. it still isn't ordering it right though =S

Here is the exact format of my sql table[code]create table `Topics` (
`id` mediumint(8) unsigned not null auto_increment,
`parent` mediumint(8) unsigned default null,
`topic` varchar(255) default null,
`detail` longtext,
`name` varchar(65) default null,
`datetime` varchar(25) default null,
`origtime` varchar(25) default null,
`subs` int(10) unsigned default '0',
`reply` int(10) unsigned default '0',
`locked` tinyint(2) unsigned default null,
`pin` tinyint(2) unsigned default null,
primary key  (`id`)
)engine=MyISAM  default charset=latin1 auto_increment=112 ;[/code]And this is the exact format the date is put in as[code]date("y-n-j G:i:s")[/code]

I'm not sure if there is any other info that could be effecting this..

Oh and this is hosted on a linux server I think. I'm not sure what version though.. it's the one at bluehost.com

Why am I running into so many problems trying to do something as simple as ordering by date? Is there some fundamental flaw in my coding (il)logic that I should change?  :-\

Oh and here is an example of what the datetime looks like in the sql table.. "07-10-20 23:59:59"

[corbin]

MySQL can't order a varchar field by unix timestamp if i remember right...

[Azu]

What kind of field does it need to be then?

[alpine]

It works if set as 'timestamp' and presumeable as 'datetime'

[ShogunWarrior]

I would just insert a timestamp into the database instead of a date/time.
That way when you are ordering it will always work as you want and you can easily present the date in any form you want after a select.

[Azu]

Hmm.. that's how I tried to have it before.. but it gave me an error saying I could only have 1 field in that format.. but I need to have two..

They both start out the same, but one of them gets updated once in a while, and sometimes it will need to be set to the other one.

:-\

[Azu]

Bump

[ShogunWarrior]

Instead of using a time field use an INT field and insert the time from PHP.

[Azu]

Thank you! With your help, I got it working  :)