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! Link to comment https://forums.phpfreaks.com/topic/116593-solved-phpmysql-while-statement/ 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 Link to comment https://forums.phpfreaks.com/topic/116593-solved-phpmysql-while-statement/#findComment-599568 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") Link to comment https://forums.phpfreaks.com/topic/116593-solved-phpmysql-while-statement/#findComment-599610 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. Link to comment https://forums.phpfreaks.com/topic/116593-solved-phpmysql-while-statement/#findComment-599723 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 Link to comment https://forums.phpfreaks.com/topic/116593-solved-phpmysql-while-statement/#findComment-599729 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.