1

I've created an orders page for logged in users to see their orders in a table. My 'SELECT' statement was displaying duplicate results at first, but after trying to fix it, it is now showing only one order per user.

I have two tables for the orders orders and ordersdetail.

How do I display all of the users orders instead of just one order or duplicates?

<?php //CUSTOMER $user = $_SESSION['email']; $get_c = "SELECT * FROM customer WHERE email='$user'"; $run_c = $db->query($get_c); $row_c = $run_c->fetch(PDO::FETCH_BOTH); $c_id = $row_c['id']; //ORDERS $get_o = "SELECT * FROM orders WHERE user_id='$c_id'"; $run_o = $db->query($get_o); $row_o = $run_o->fetch(PDO::FETCH_BOTH); $o_id = $row_o['id'];/***/ $o_name = $row_o['name']; $o_status = $row_o['status']; $o_userid = $row_o['user_id']; $o_date = $row_o['date']; //ORDER DETEAILS $get_order = "SELECT * FROM ordersdetail WHERE ordersid='$o_id' "; $run_order = $db->query($get_order); while($row_o = $run_order->fetch(PDO::FETCH_BOTH)){ $order_id = $row_o['ordersid']; $o_price = $row_o['price']; $pro_id = $row_o['productid']; $o_qty = $row_o['quantity']; //PRODUCT DETAILS $get_pro = "SELECT * FROM items WHERE id='$pro_id'"; $run_pro = $db->query($get_pro); $row_pro = $run_pro->fetch(PDO::FETCH_BOTH); $pro_image = $row_pro['photo']; $pro_title = $row_pro['title']; ?> <tr align="center"> <td><?php echo $o_id;?></td> <td> <?php echo $pro_title;?> <b> <img src="images/<?php echo $pro_image;?>" width="50" height="50" /> </td> <td><?php echo $o_price;?></td> <td><?php echo $o_qty;?></td> </tr> <?php } ?> </table>

ORDERSDETAIL TABLE

 `ordersdetail` ( `id` int(10) NOT NULL, `productid` int(11) NOT NULL, `ordersid` int(11) NOT NULL, `price` float NOT NULL, `quantity` int(11) NOT NULL ) ENGINE=InnoDB

ORDERS TABLE

`orders` ( `id` int(10) unsigned NOT NULL, `name` varchar(100) NOT NULL, `status` tinyint(1) NOT NULL, `user_id` int(11) NOT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB
Improve this question
6
  • 3
    it's going to be hard to help without knowing your db structure... Commented Mar 25, 2016 at 0:21
  • I'll export my tables and edit the code now gimme a sec Commented Mar 25, 2016 at 0:24
  • @Mani log into your mysql shell mysql -u YOUR_USERNAME -p and then type use YOUR_DB_NAME and then type show tables to view the tables. This way you can copy the structure in text and simply paste it in your question. If you want to display the structure of a table simply run desc YOUR_TABLE_NAME. Commented Mar 25, 2016 at 0:37
  • its edited @webeno Commented Mar 25, 2016 at 0:39
  • I'm using phpMyAdmin Mysql @James111 not sure where to access that Commented Mar 25, 2016 at 0:46

1 Answer 1

Reset to default
1

There's a whole lot of queries there, and I'm not convinced you need all of them.

To get the orders for a given customer, you can use a join operation. For example:

SELECT o.id , o.name , o.user_id , o.date FROM orders o JOIN customer c ON c.id = o.user_id WHERE c.email = ? -- '$user' ORDER BY o.date DESC, o.id DESC

I would add an ORDER BY clause to make the result more deterministic. Without that, MySQL is free to return the rows in any sequence it chooses.

Since an order can contain (zero?), one or more order lines, doing a JOIN to the ordersdetails table would produce "duplicate" order information on each row returned.

There's a design choice to make here. Either

1) we run the orders query, and fetch each row in a loop. for each row we fetch, we run a separate query from the ordersdetails table, and process those rows for the lineitems. and then go back through the orders loop again, fetching the next order. The query above for the outer loop, and a query like this for the inner loop.

SELECT l.ordersid , l.price , l.productid , l.quantity FROM orderdetails l WHERE l.ordersid = ? -- o.id

The other big design alternative is to do just one big query, like this:

SELECT o.id AS o_id , o.name AS o_name , o.user_id AS o_user_id , o.date AS o_date , l.id AS d_id , l.price AS d_price , l.productid AS d_productid , l.quantity AS d_quantity FROM orders o JOIN customer c ON c.id = o.user_id LEFT JOIN orderdetails l ON l.ordersid = o.id WHERE c.email = ? -- '$user' ORDER BY o.date DESC, o.id DESC, l.id DESC

And process that. This will require some extra code.

If the o_id orderid from row we fetch is equal to the o_id from the previous row, we know we're processing the same order. If we're returning on the page a result something like this...

 order#55 date: 3/25/2016 product price qty status -------------- -------- --- ---------- 1) snugglyfluffy 14.99 12 shipped 2) bitersmiter 99.95 1 fulfillment 3) camelmammal 14,358.00 7 backordered order#42 date: 12/12/2012 product price qty status -------------- -------- --- ------- 1) hitchhikersguide 12.50 1 shipped 2) towel 4.00 1 shipped

Then we'd need to identify the "first row" from each order by checking for a difference in the order id (of the current row vs the previous row. We'd process the order header part when we get a new order, and then process the line item. On the next row for the same order, we just put out just another line item, repeating that, until we get a new order id.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for your help, the first solution has worked, however it is now giving me three times the result for some reason. Any ideas?
Actually I have tried your second query and it works better. Thanks alot.
Is email unique in the Customer table? I recommend you develop, test and debug the SQL statements apart from your code. Make sure the queries are returning the results you expect. When you introduce those queries into the code; and you are getting unexpected results from your program, you won't be wondering whether the problem is in the query, or in the code.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.