DATETIME problem

[waynew]

I've been building a custom forum and my DB tables look like so:

 

<?php

mysql_query(

"CREATE TABLE thread(

	thread_id BIGINT(50) NOT NULL AUTO_INCREMENT,
	user_started BIGINT(50) NOT NULL,
	title TEXT NOT NULL,
	body TEXT NOT NULL,
	last_post_time DATETIME NOT NULL,
	latest_poster_id BIGINT(50) NOT NULL,
	PRIMARY KEY(thread_id))") or die(mysql_error());

mysql_query(

"CREATE TABLE thread_post(

	thread_post_id BIGINT(50) NOT NULL AUTO_INCREMENT,
	parent_thread_id BIGINT(50) NOT NULL,
	user_posting BIGINT(50) NOT NULL,
	time_posted DATETIME NOT NULL,
	body TEXT NOT NULL,
	PRIMARY KEY(thread_post_id))") or die(mysql_error());
?>

 

When you go to the forum main page, all forum topics are ordered by last_post_time, which is a DATETIME value. The query looks like so:

 

<?php

function get_topics_listing(){
$select = mysql_query("SELECT thread_id,title,user_started,latest_poster_id,last_post_time 
FROM thread ORDER BY last_post_time DESC LIMIT 30") or die(mysql_error());
return $select;
}
?>

 

This seemed to be working fine until today when a topic with the last post time of 2008-08-31 12:40:29 came out above another newer topic with the last post time of 2008-08-31 01:52:54

 

Why would this be? Cheers for the help.

[Guardian-Mage]

For that to work right the time must be in 24 hour format. The two dates you gave, the first one 2008-08-31 12:40:29 is newer than 2008-08-31 01:52:54. With 24 hour time,the two dates would be:

 

2008-08-31 12:40:29

2008-08-31 13:52:54

[waynew]

Thanks! I had just noticed that believe it or not. I'll just fix how I create the date. Thanks!

[waynew]

<?php

$date_time = date("Y-m-d")." ".date("H:i:s");

?>

 

Fixed. Thanks.

[Guardian-Mage]

Don't forget to mark it as solved 

[discomatt]

<?php

$date_time = date("Y-m-d")." ".date("H:i:s");

?>

 

Fixed. Thanks.

 

Durrr

 

<?php

$date_time = date("Y-m-d H:i:s");

?>

[PFMaBiSmAd]

Using the mysql now() function directly in the query would be many times faster as the php date() function is fairly slow (and calling it twice is causing php to take twice as long.)

[discomatt]

Agreed. I usually create my self-generating date columns like this

 

ADD `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP