Hey guys... I have built a web application that allows users to input data into the database, and each entry is marked with the date and time in its seperate DATETIME DB column using the NOW() mysql function inside the INSERT query.
But now that it has passed new years... I've noticed that all new entires in 2007 are being placed at the bottom of the sort order when SELECT'ing all records from the DB and using ORDER BY datetime_col (which is the NOW() DATETIME column). Why is this happening? Is NOW() or DATETIME not a good function/type to use to record the date and time of a DB record insert? What can I do to fix this or is there another solution? Thanks a bunch for any help.
DATETIME/NOW() not sorting correctly?
Moderator: General Moderators
- seodevhead
- Forum Regular
- Posts: 705
- Joined: Sat Oct 08, 2005 8:18 pm
- Location: Windermere, FL
- seodevhead
- Forum Regular
- Posts: 705
- Joined: Sat Oct 08, 2005 8:18 pm
- Location: Windermere, FL
feyd | Please use [/syntax]
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
[syntax="sql"]
CREATE TABLE posts (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
post TEXT NOT NULL,
datetime_col DATETIME NOT NULL,
PRIMARY KEY (id)
);
Code: Select all
$query9 = "INSERT INTO posts (post, datetime_col) VALUES ('{$clean['post']}', NOW() )";
$result9 = @mysql_query($query9) or trigger_error("Query: $query9\n<br />MySQL Error: " . mysql_error());Code: Select all
$query1 = "SELECT id, post, DATE_FORMAT(datetime_col, '%c/%d/%y') AS date_posted FROM posts ORDER BY date_posted DESC";
$result1 = @mysql_query($query1) or trigger_error("Query: $query1\n<br />MySQL Error: " . mysql_error());feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]- seodevhead
- Forum Regular
- Posts: 705
- Joined: Sat Oct 08, 2005 8:18 pm
- Location: Windermere, FL