[SOLVED] PHP/MYSQL small return problem...

[SocomNegotiator]

Ok I have two pages on my site. One page grabs only the most recent order. While the other page grabs every order in the database. My problem is that the items in the order are displaying in a different order for these two pages. And the only difference in the MySQL is something that should not effect the return.

 

Here is the MySQL for the page 1 that grabs one order:

$db->query('SELECT * FROM `order` ORDER BY order_number DESC LIMIT 1') or die(mysql_error());
$number = $db->fetch_array();
$num = $number['order_number'];

$query = ("SELECT *, DATE_FORMAT(order.date,'%m/%d/%y - %H:%i') AS order_date FROM `order` LEFT JOIN `item` ON order.item_id = item.id LEFT JOIN `docj_user` ON docj_user.id = order.user_id WHERE order_number = '$num' ORDER BY order_number DESC");
$result = mysql_query($query) or die (mysql_error());
$count = mysql_num_rows($result);

 

 

Here is the MySQL for the page 2 that grabs every order:

$query = "SELECT *, DATE_FORMAT(order.date,'%m/%d/%y - %H:%i') AS order_date FROM `order` LEFT JOIN `item` ON order.item_id = item.id LEFT JOIN `docj_user` ON docj_user.id = order.user_id ORDER BY order_number DESC";
$result = mysql_query($query) or die (mysql_error());

 

 

You can see that the way I only display one order for the first page is by using $num. However, in the second page I cannot use this because I need to get every order not just one. Even with that the  WHERE order_number = '$num'  should not effect the order of the item names.

 

 

Just in case you don't understand what is happening here is what's going on....

 

Page 1:

              Amount                Name                  Date

              1                      Nintendo                10/10/08

              1                      Apple                    10/10/08

              1                      Pair of socks          10/10/08

 

Now the order is fine because the items should display in the order that they are in on the database. So lets pretend that this is the order they are in on the database.

 

Well on Page 2 this is what I get:

              Amount                Name                  Date

              1                      Pair of socks          10/10/08

              1                      Apple                    10/10/08

              1                      Nintendo              10/10/08

 

 

Even though in the database it goes nintendo, apple, pair of socks this is what I get, and I do not understand why..? Any hellp would be much appreciated.

[Barand]

Don't rely on the physical order of records, add a column such as a sequence number or a datetime to get the logical order.

 

If you have auto incrementing ids and add three records you get

1, 2, 3

 

If you now delete the second and add a fourth could end up with either

1,3,4 or 1,4,3 if the space vacated by 2 is large enough to hold the new row.

[SocomNegotiator]

Don't rely on the physical order of records, add a column such as a sequence number or a datetime to get the logical order.

 

If you have auto incrementing ids and add three records you get

1, 2, 3

 

If you now delete the second and add a fourth could end up with either

1,3,4 or 1,4,3 if the space vacated by 2 is large enough to hold the new row.

 

Ok so with that how would I be able to put that into the query? Because I have to have ORDER BY order_number because that puts the single orders in order from most recent to less recent. It is the items in each order that needs to be displayed that way.

[Barand]

If you want consistency of sequence of the items then you need to introduce an item_number within each order number.

[SocomNegotiator]

If you want consistency of sequence of the items then you need to introduce an item_number within each order number.

 

Ok so lets say I add an item_number to the order table (I do have an item_id...would that work for what you are talking about?). How would I change around the MySQL or what would I need to add? Because like I said I have to have the order_number as the ORDER BY. Reason being because each individual order needs to be displayed in DESC order.

[SocomNegotiator]

Ok for anyone who is having the same problem I got it solved.

 

I just had to do two parameters in the MySQL ORDER BY syntax. For instance...

 

$query = "SELECT *, DATE_FORMAT(order.date,'%m/%d/%y - %H:%i') AS order_date FROM `order` LEFT JOIN `item` ON order.item_id = item.id LEFT JOIN `docj_user` ON docj_user.id = order.user_id ORDER BY order_number DESC, order.id ASC";
$result = mysql_query($query) or die (mysql_error());