PHP DB - 'order by X Y' not working

[DanShadow]

Hey all,

The problem im having is the following SQL is supposed to order 'post messages' by the date/time they were posted, but it doesnt.. lol.

Quote:

$postquery=@mysql_db_query($dbname,"SELECT * FROM posts WHERE typereferenceid='" . $_SESSION['curr_uid'] . " ORDER BY postdate,posttime'",$connection) or die("Internal error #1, please contact administrator!".mysql_error());


$i = 0;
while($posts = mysql_fetch_array($postquery))
{
// Display posts
}

In fact.. the order of display doesnt even represent what was posted last either.

For example..

Post #1

Post #3 (last post inserted into DB)

Post #2

Anybody ever come across an issue like this?? I figure i could get around it by manually parsing the post date/time, but considering SQL has a command to do this for me, i'd rather figure out why its not working, lol.

Thanks!

[yoursecretninja]

I seldom deal directly with SQL queries anymore as I almost always use a framework with active record features. If you're doing a lot of php work, I'd recommend checking out CodeIgniter or some other framework.

That said, your query looks okay to me.

What is the data type of postdate and posttime? If they are not date and time formats, respectively, it is not going to order them properly.

Also, why not save the post date and time as something like postdatetime where you store the date and time together in the datetime format? You can always extract the date or time if needed but having it represented in one field is more convenient... case in point you'd only need to use orderby on one column column here.

[DemonWasp]

Your single-quote appears to be mismatched.

You have:

code:

SELECT * FROM posts WHERE typereferenceid='" . $_SESSION['curr_uid'] . " ORDER BY postdate,posttime'

You probably meant:

code:

SELECT * FROM posts WHERE typereferenceid='" . $_SESSION['curr_uid'] . "' ORDER BY postdate,posttime

[DanShadow]

Thanks DemonWasp, that fixed a big part of my problem!

@yoursecretninja

I havent looked into framework's yet.. but i've heard it's worth looking in to though.
My data types were DATE & TIME, and I chose to seperate them for ease of usage).