I have a 3 MySQL tables which contain customer order details.
1 table for order (this contains customer details and order id number)
1 table for order_products (this contains the order id number, product id number, product name)
1 table for order_products_attriburtes(order id number, orders_products_id and all the different attributes associated with that product, ie size colour etc)
I’m trying to set up a page that will show me all the details of each order.
I need to show the item ordered including attributes, the order id and the customer details for each order but this data is spread across 3 tables. Can anyone tell me how I would set up a query to achieve this?
I tried the following but it doesn’t work:
$sql="SELECT * FROM orders WHERE date_purchased > CURRENT_DATE - INTERVAL 1 MONTH ORDER BY date_purchased, orders_id DESC";
$result=mysql_query($sql);
// Count table rows
$count=mysql_num_rows($result);
while($rows=mysql_fetch_array($result)){
$sql_item="SELECT * FROM orders_products WHERE orders_id = '$rows[orders_id]'";
$result_item=mysql_query($sql_item);$count=mysql_num_rows($result_item);
while($rows_item=mysql_fetch_array($result_item))
I’ve just realised the code is not wokring exactly right. If an order has 2 items in it now the all the product attributes in the db for both items are being show with each product. ie item 1 has colour and size attribute. item 2 has colour and custom attribute. The way this is now showing is item 1 with all the attributes in the order and item 2 with all the attributes in the order. I think I need to put some sort of qualifier in the code for the product id but don’t know where to put it. Can you help please?
You are the man!!! Thank you so much for all your help with this stuff. Your code and patience with me on this has been great. I’m goint o try to build on what you’ve told me here. If I get stuck I might need to come back for some advice. Can’t thank you enough!
I thought I was doing it all right with the 3rd table but I’ve just realised the data that is now showing is not right.
Here’s what I’m using:
<?php
$sql="SELECT orders.customers_name, orders.customers_email_address, orders.payment_method, orders.orders_id, orders.date_purchased, orders.delivery_street_address, orders.delivery_suburb, orders.delivery_city, orders.delivery_state, orders.delivery_country, orders.delivery_postcode, orders_products.products_name, orders_products_attributes.products_options, orders_products_attributes.products_options_values
FROM
orders
LEFT JOIN
orders_products
ON
(orders.orders_id=orders_products.orders_id)
LEFT JOIN
orders_products_attributes
ON
(orders.orders_id=orders_products_attributes.orders_id)
WHERE
orders.date_purchased > CURRENT_DATE - INTERVAL 1 MONTH
ORDER BY
orders.date_purchased
, orders.orders_id DESC LIMIT 50";
$result=mysql_query($sql) or die(mysql_error());
// Count table rows
$count=mysql_num_rows($result);
while($rows=mysql_fetch_array($result)){
?>
The query does return results but they are a bit screwed up.
I’ll explain what I mean.
Order Number 14310 has 2 products in it. 1 boys shirt, 1 adults shirt. Both have a custom name printed on them so for each item there are 2 attributes, the size and the custom printing.
The way this should display on my page is as 2 lines, 1 for each item in the order with the corresponding attributes displayed next to the product name.
What the code is currently doing is displaying 8 lines for this order number. Its basically doing 2x2x2 and I don’t know where or what to change to make it work correctly.
Can you help me with this or have I not been clear enough describing the problem?
Excellent! I now have data showing! Thanks so much for your help.
I have one other question if you’d be kind enough to give me a bit more help please.
Can you tell me how I would add to the query so that it would select the products_name from the orders_products table that relates to the orders_id?
Here’s the query I now have based on your help so far:
<?php
$sql="SELECT orders.customers_name, orders.customers_email_address, orders.payment_method, orders.orders_id, orders.date_purchased, orders.delivery_street_address, orders.delivery_suburb, orders.delivery_city, orders.delivery_state, orders.delivery_country, orders.delivery_postcode
FROM
orders
LEFT JOIN
orders_products
ON
(orders.orders_id=orders_products.orders_id)
WHERE
orders.date_purchased > CURRENT_DATE - INTERVAL 1 MONTH
ORDER BY
orders.date_purchased
, orders.orders_id DESC";
$result=mysql_query($sql) or die(mysql_error());
// Count table rows
$count=mysql_num_rows($result);
while($rows=mysql_fetch_array($result)){
?>
The 3rd table I need to JOIN is the table that holds all the product attributes that the customer selected when making the order. for the above example I used it holds the size attribute for each shirt and the custom name attribute for each shirt. Also in this table are the following columns order id the product id.I’ll look into the difference between LEFT and INNER as you’ve probably guessed I don’t know the difference. Can anyone shed any light on the issues I’m having with the 3rd table?
Ok trying your code, which makes sense, just not very sure how to execute it very well. Can you help me out with this please? What I have just now is the following:
$sql="SELECT 'customers_name', 'customers_email', 'payment_method', 'orders_id', 'date_purchased', 'delivery_street_address', 'delivery_suburb', 'delivery_city', 'delivery_state', 'delivery_country', 'delivery_postcode'
FROM
orders
INNER JOIN
orders_products
ON
orders.orders_id=orders_products.orders_id
WHERE
orders.date_purchased > CURRENT_DATE - INTERVAL 1 MONTH
ORDER BY
date_purchased
, orders_id DESC";
$result=mysql_query($sql);
// Count table rows
$count=mysql_num_rows($result);
while($rows=mysql_fetch_array($result)){
But its not working correctly when I try to echo the variables! It doesn’t show anything. Have I done it wrong?
Does every order have products attached to it? If not, change INNER JOIN to LEFT JOIN.
Otherwise I can’t see anything wrong with the code.
If the above doesn’t work could you please post the complete code?
The order ids for the data dump don’t match up. So that dump is pretty much useless.
Anyways, this what I was working with before I realized I couldn’t test the dump data.
Breaking your problem into pieces worry about getting the data first than the display of it.
With that said if the below script runs is the dumped array correct in that data that it has retrieved?
<?php
// replace all your db credentials here
mysql_connect('localhost', 'root', 'ccc');
mysql_select_db('sp_products_690663');
/*
* Fill this in to test
*/
$orders_id = 145505; // change this to an actual order with products associated with it
$sql =
'SELECT
p.orders_products_id
,p.products_name
,p.products_quantity
,p.final_price
,a.products_options
,a.products_options_values
FROM
orders o
INNER
JOIN
orders_products p
ON
o.orders_id = p.orders_id
LEFT OUTER
JOIN
orders_products_attributes a
ON
p.orders_products_id = a.orders_products_id
WHERE
o.orders_id = '.mysql_real_escape_string($orders_id);
echo $sql;
$result = mysql_query($sql);
if(!$result) {
trigger_error('Query Invalid');
}
$products = array();
while($row=mysql_fetch_assoc($result)) {
if(!isset($products[$row['orders_products_id']])) {
$copy = $row;
unset($copy['products_options'],$copy['products_options_values']);
$products[$row['orders_products_id']] = $copy;
}
if($row['products_options'] !== null) {
$products[$row['orders_products_id']]['attributes'][$row['products_options']] = $row['products_options_values'];
}
}
echo '<pre>',print_r($products),'</pre>';
?>
