[SOLVED] PHP/MySQL display question

[SocomNegotiator]

Here is my date set:

 

id     |  order_number     |     user_id       |      item_id      |    quantity        | date
4      |    1              |      4            |      3            |    3               | 10/2/04
5      |    1              |      4            |      2            |    3               | 10/2/04
6      |    2              |      4            |      1            |    5               | 10/2/04

 

Well I am trying to figure out how to display this to the user. I want the items to be grouped based on order_number. So order #1 will have those two items listed under it, and order #2 will have just that single item listed under it. This page is just an order history page...this way the user can click a link and see what they have gotten in the past.

 

What it should look like essentially on the page:

Order #1:
     Item_id                     Quantity             Date
        3                               3                 10/2/04
        2                               3                 10/2/04

Order #2:
     Item_id                     Quantity             Date
        1                               5                 10/2/04

 

Pretty easy I just can't figure out what the query should be..or what the while statement should be like to make it display like this

[Psycho]

Should the date really be associated with the items in the order, or just with the order. In other words, can you have items in a single order that are from two dates? You should probably have two different tables one for the orders and one for the items in the orders, but anyway...

 

 

<?php

$query = "SELECT * FROM table ORDER BY order_number, date";
$result = mysql_query($query) or die (mysql_error());

$current_order = '';
echo "<table>\n";

while ($record = mysql_fetch_assoc($result))
{
    //If first record for a new order display the header rows
    if ($current_order!=$record['order_number'])
    {
        $current_order = $record['order_number'];
        echo "<tr>\n";
        echo "<td colspan=\"4\"><b>Order# $current_order:</b></td>\n";
        echo "<td> </td><td>Item ID</td><td>Quantity</td><td>Date</td>\n";
        echo "</tr>\n";
    }

    //Display the record data
    echo "<tr>\n";
    echo "<td> </td>\n";
    echo "<td>" . $record['item_id'] . "</td>\n";
    echo "<td>" . $record['quantity'] . "</td>\n";
    echo "<td>" . $record['date'] . "</td>\n";
    echo "</tr>\n";
}
echo "</table>\n";

?>

[SocomNegotiator]

Yeah that does make sense...but what I do is when they submit something to the cart I have that in a pre-order table. Then when they do the final submit of the order I have the stuff they have in the pre_order table copy over to the order table with an order number. Then once that is complete the items in the pre_order table are deleted.

 

 

Should the date really be associated with the items in the order, or just with the order. In other words, can you have items in a single order that are from two dates? You should probably have two different tables one for the orders and one for the items in the orders, but anyway...

 

 

<?php

$query = "SELECT * FROM table ORDER BY order_number, date";
$result = mysql_query($query) or die (mysql_error());

$current_order = '';
echo "<table>\n";

while ($record = mysql_fetch_assoc($result))
{
    //If first record for a new order display the header rows
    if ($current_order!=$record['order_number'])
    {
        $current_order = $record['order_number'];
        echo "<tr>\n";
        echo "<td colspan=\"4\"><b>Order# $current_order:</b></td>\n";
        echo "<td> </td><td>Item ID</td><td>Quantity</td><td>Date</td>\n";
        echo "</tr>\n";
    }

    //Display the record data
    echo "<tr>\n";
    echo "<td> </td>\n";
    echo "<td>" . $record['item_id'] . "</td>\n";
    echo "<td>" . $record['quantity'] . "</td>\n";
    echo "<td>" . $record['date'] . "</td>\n";
    echo "</tr>\n";
}
echo "</table>\n";

?>