How To Order By 3 or More Columns?

[phpBeginner06]

Does anyone know how to order mysql_query by 3 or more columns?

My datafields are "Date", "Time", and "SpecificTime"; and that is exactly the order that in need to put them in when they are display with in my web page. I think that it might be done with an array, but I do not know how to create an array for my database fields. I also do not know how/where to put the array in the mysql_query; so that it will display and order as I want it to.

Basically this is the set up:

Field Name = Date
Variable Name = $dated
$dated = date("m/d/Y");

Field Name = Time
Variable Name = $timed
$timed = date("g:i:s A", time()-(18000*1));

Field Name = SpecificTime
Variable Name = $alapac
$alapac = date("A", time()-(18000*1));

I want to order by Date, Time, and SpecificTime in descending order (desc).

Can this be done and how can I do it and put it into mysql_querry?

[phpBeginner06]

Does anyone know how to do this or if it can even be done?

[PC Nerd]

yes i beleive its the sql : "ORDER BY field1, field2, field3 descending"  ( or ascending )

i think thats right, but check in on google or something

[DarkendSoul]

The best way to do this would be to redesign the table, make the field a date type. Then ordering will only need to be done once to one field.

Afterwards using the [url=http://ca3.php.net/manual/en/function.explode.php]explode()[/url] function to better utilize the information is the easiest way, I think. But you may be more comfortable with [url=http://ca3.php.net/manual/en/function.mktime.php]mktime()[/url]

[Barand]

I agree with DarkendSoul. Use the database's datetime column type or as a Unix timestamp value.

A date formatted as "m/d/Y" is as useful as a chocolate teapot when it comes to sorting or comparing dates (01/01/2007 will sort before 12/31/2006)

Only format the dates and times as you want them on final output.

[phpBeginner06]

Well what I decided to do is not use any type of "order by" function. I think PC Nerd figured out what I was trying to make; I read one of the threads by PC Nerd that was posted after mine. I am trying to make a message system that works with an online form, a web page and a default email application. What I was trying to accomplish was ordering the date the message was recieved and the time the message was recieve (just like something you would find in any common email inbox). I have now come to the conclusion that I really do not have to use the "order by" function at all. Because the message from the online form will automatically be sent to the database and post to a web page in the order they were recieved. This will have accomplished what I was trying to do without all the complex sorting. "I was making things harder then what that had to be." I was also always bad to do that in math, when I was in school too; at least my math teachers use to tell me the same thing - LOL !!! :D

[color=red]But Thank You Guys For All The Help - I Greatly Appericiate It !!![/color]

[Barand]

Be cautious with that assumption. If an earlier post is deleted, a later one could be placed in the available gap so the physical order no longer matches the logical order.

[phpBeginner06]

yeah - I think your right. I just tested it and the latest post went to the bottom of the web page.

[phpBeginner06]

Well I am not quit sure where to go from here. I have been working on this off and on for the past 3 days now. I tried to use a datetime and timestamp to sort my table earlier, but it would keep returning a default of "0000/00/00 00:00:00"; even after I had set the default to specific numbers in the Time field with myPHPAdmin. I thought I could just sort with Date, Time desc; where Date would equal m/d/Y and Time would equal G:i:s; but I seem to crash and burn on that to.

So if anyone has gotten any ideas; I am still open to hearing them - Thanks.

[phpBeginner06]

The code I am using and have been using below; works ok, except with AM & PM. It does not order in descending for Date, Time, PM/AM (I wish it was that simple). That was the orginal reason I wanted to order by three columns. I was hoping someone could tell me how to do it with an array; if that was even possible, but that may not work either.

[code]$result = mysql_query("select * from messeges order by Date, Time desc");[/code]

I know this can be done; I have seen to many email inboxes set up the exact same way. So if any has any ideas, please let me know - Thanks.

[Barand]

[code] 
CREATE TABLE sample (
    id int not null auto_increment primary key,
    username varchar(20),
    posted datetime
)
[/code]

Use NOW() function to populate the datetime column
[code]<?php
mysql_query ("INSERT INTO sample (username, posted) VALUES ('barand', NOW())");
?>
[/code]

Format on output
[code]<?php
$sql = "SELECT username, posted FROM sample ORDER BY posted DESC";
$res = mysql_query($sql) or die(mysql_error());
while (list($u, $d) = mysql_fetch_row($res)) {
    echo "$u : " . date ('m/d/Y g:i A', strtotime($d)) . '<br>';
}
?>
[/code]