Programming C, C++, Java, PHP, Ruby, Turing, VB
Computer Science Canada 
Programming C, C++, Java, PHP, Ruby, Turing, VB  

Username:   Password: 
 RegisterRegister   
 PHP DB - 'order by X Y' not working
Index -> Programming, PHP -> PHP Help
View previous topic Printable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic
Author Message
DanShadow




PostPosted: Sun Oct 31, 2010 3:20 pm   Post subject: PHP DB - 'order by X Y' not working

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!
Sponsor
Sponsor
Sponsor
sponsor
yoursecretninja




PostPosted: Sun Oct 31, 2010 3:31 pm   Post subject: RE:PHP DB - \'order by X Y\' not working

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




PostPosted: Sun Oct 31, 2010 5:32 pm   Post subject: RE:PHP DB - \'order by X Y\' not working

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




PostPosted: Sun Oct 31, 2010 9:22 pm   Post subject: RE:PHP DB - \'order by X Y\' not working

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).
Display posts from previous:   
   Index -> Programming, PHP -> PHP Help
View previous topic Tell A FriendPrintable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic

Page 1 of 1  [ 4 Posts ]
Jump to:   


Style:  
Search: