SocomNegotiator Posted July 25, 2008 Share Posted July 25, 2008 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! Quote Link to comment Share on other sites More sharing options...
SocomNegotiator Posted July 25, 2008 Author Share Posted July 25, 2008 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 Quote Link to comment Share on other sites More sharing options...
ranjuvs Posted July 25, 2008 Share Posted July 25, 2008 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") Quote Link to comment Share on other sites More sharing options...
SocomNegotiator Posted July 25, 2008 Author Share Posted July 25, 2008 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. Quote Link to comment Share on other sites More sharing options...
SocomNegotiator Posted July 25, 2008 Author Share Posted July 25, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.