[SOLVED] PHPMySql while statement

[SocomNegotiator]

Ok this code works, but it does not work how I want it to. Here is the code:

 

<?php
$user_id = $user->inf['id'];
$query = ('SELECT * FROM `order` WHERE user_id= '.$user_id.' ORDER BY order_number DESC LIMIT 1');
$result = mysql_query($query) or die (mysql_error());
$count = mysql_num_rows($result);

if ($count == 0)	{
	echo "<br /><div id='case'><p align='center' style='color: red;'>There are no previous orders to view.</p></div>";
	}

$db->query('SELECT item.id, item.name, item.description, order.id, order.amount, order.user_id, order.order_number FROM `order` LEFT JOIN `item` ON order.item_id = item.id') or die(mysql_error());
$iteminf = $db->fetch_array();

$current_order = '';
echo "<table cellpadding='2' cellspacing='2' align='center'>\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 style='padding-top:25px;' colspan='4 align='left'><b>Order # $current_order:</b> ".$record['date']."</td></tr>\n";
	echo "<tr><td colspan='3'><form id='resubmit_order' action='?page=order' method='post'><input type='hidden' name='order_number' value=".$record['order_number']." /><center><input type='submit' name='submit' value='Re-Submit Order #".$record['order_number']."'/></center>
	</form></td></tr>";
        echo "<tr><td style='border-bottom: 1px solid #000000'>Quantity</td><td style='border-bottom: 1px solid #000000'>Item Name</td><td style='border-bottom: 1px solid #000000'>Date</td>\n";
        echo "</tr>\n";
    }

    //Display the record data
    echo "<tr>\n";
    echo "<td>" . $record['amount'] . "</td>\n";
    echo "<td>" . $iteminf['name'] . "</td>\n";
    echo "<td>" . $record['date'] . "</td>\n";
echo "</tr>\n";
}
echo "</table>\n";
?>

 

What this code does is supposed to display is one full order in the order DB based on the order_number. However, if an order has multiple items all of those items will have the same order_number. So all those items should be displayed as one order (if they have the same order_number). Now even if an order has multiple items this code will only display one item in an order, because I put a LIMIT 1 on the query..but I want the "LIMIT 1" to limit one order (so all the items in an order, but only one order all together) not one item....if you get what I mean.

 

Thanks for your help!

[SocomNegotiator]

I am thinking it is mainly with the sql query I have, but I am not sure how to make to get all the items that have the same order_number

[ranjuvs]

try this out and let me know whether it works

$db->query('SELECT item.id, item.name, item.description, order.id, order.amount, order.user_id, order.order_number FROM `order` LEFT JOIN `item` ON order.item_id = item.id')

change this query to

 

$db->query('SELECT item.id, item.name, item.description, order.id, order.amount, order.user_id, order.order_number FROM `order` LEFT JOIN `item` ON order.item_id = item.id' AND order.item.id="the order id you get from the first query")

[SocomNegotiator]

try this out and let me know whether it works

$db->query('SELECT item.id, item.name, item.description, order.id, order.amount, order.user_id, order.order_number FROM `order` LEFT JOIN `item` ON order.item_id = item.id')

change this query to

 

$db->query('SELECT item.id, item.name, item.description, order.id, order.amount, order.user_id, order.order_number FROM `order` LEFT JOIN `item` ON order.item_id = item.id' AND order.item.id="the order id you get from the first query")

 

 

I got nothing to show...here is how my database is set up:

item table:

id   |    name    |    date
1    |   plum      |   10/2/2008
2    |   berry     |   10/2/2008
...and so forth

order table

id    |   order_number    |    item_id    |   user_id     |   quantity   |   date
1     |         1              |       1         |     8           |    100        |   11/2/2008
2     |         1              |       2         |     8           |    200        |   11/2/2008
....and so forth

 

As you see order_number 1 has two items in it. Well the code that I have posted only grabs one item when it should grab every item that has that same order_number of 1. I mean there could be 5 items that have the same order_number...I just made this to give you a better feel for how it should work.

[SocomNegotiator]

I got it fellas...and if your wondering how here is what I did

 

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



$query = ('SELECT * FROM `order` WHERE order_number= '.$num.' ORDER BY order_number DESC');
$result = mysql_query($query) or die (mysql_error());

 

I added the top query and got their last order they made. And then in the next query I could use that order_number to say WHERE order_number =

 

Thanks for your help