Problems selecting data from different tables

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))

Any help much appreciated

i’d be happy to

you will have to dump the tables (generate the CREATE TABLE statements with a few INSERTs) to provide sample data that i can check out

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?

can i make a couple of suggestions?

  1. the join should be an INNER JOIN – please ask, if you don’t know why

  2. don’t use unnecessary parentheses (like in your ON clause)

what is this 3rd table you want to add to the query?

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
     , [COLOR="blue"]atts.options[/COLOR]
  FROM orders     
INNER
  JOIN orders_products 
    ON orders_products.orders_id = orders.orders_id
INNER
  JOIN ( SELECT orders_id
              , [COLOR="Blue"]GROUP_CONCAT(
                      CONCAT(products_options
                            ,':'
                            ,products_options_values)
                   SEPARATOR ',') AS options [/COLOR]
           FROM orders_products_attributes  
         GROUP
             BY orders_id ) AS atts
    ON atts.orders_id = orders.orders_id
 WHERE orders.date_purchased > CURRENT_DATE - INTERVAL 1 MONTH 
ORDER 
    BY orders.date_purchased 
     , orders.orders_id DESC LIMIT 50

Hey Thanks very much for the help with this. I’ve managed to get the product name to show now using the following query

<?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
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)){
?>

I now need to try to get the query to work on a 3 table join instead of just 2.

Really appreciate all your help with this, thanks again.

Hi I’ve now managed to get the script to through up an error. The error is as follows:

Column ‘orders_id’ in field list is ambiguous

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)){
?>

Simply add orders_products.products_name to the SELECT clause

SELECT orders_products.products_name, etc, etc, …

:slight_smile:

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?

yes, it is :slight_smile:

luckily, it doesn’t matter which orders_id you use, because they’re guaranteed to be equal!!!

unfortunately the parser isn’t going to fix the error for you, even though it’s obvious that either table’s orders_id could be used

SELECT customers_name
     , customers_email_address
     , payment_method
     , [COLOR="red"]orders_id[/COLOR]
     , date_purchased
     , delivery_street_address
     , delivery_suburb
     , delivery_city
     , delivery_state
     , delivery_country
     , delivery_postcode 
  FROM orders     
INNER 
  JOIN orders_products 
    ON [COLOR="Blue"]orders_products.orders_id = orders.orders_id[/COLOR]
 WHERE orders.date_purchased > CURRENT_DATE - INTERVAL 1 MONTH 
ORDER 
    BY date_purchased 
     , [COLOR="Red"]orders_id [/COLOR]DESC

see the stuff in blue? those instances of orders_id are properly qualified by their table names

you need to do the same in the SELECT clause and also the ORDER BY clause

in fact, you should really qualify all the columns used in the query – i consider this “best practice” when the query contains more than one table

:slight_smile:

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?

Sorry, I’m really confused now!
Yes every order has products attached to it.

Here’s the code I’m using which is not displaying anything now in the echo statements:

<?php

$sql="SELECT customers_name, customers_email_address, 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)){
?>

 <tr>
    <td>
      	<? echo $rows_item['products_name']; ?>
      </td>
    <td><? echo $rows['customers_name']; ?></td>
    <td><? echo $rows['customers_email_address']; ?></td>
    <td><?php if($rows['payment_method']=="Credit Card via WorldPay") echo "WorldPay"; if($rows['payment_method']=="PayPal") echo "PayPal"; if($rows['payment_method']=="Check/Money Order") echo "Check/Money Order"; if($rows['payment_method']=="Bank Transfer") echo "Bank Transfer"; if($rows['payment_method']=="Credit Card - EPDQ Form") echo "EPDQ Form"; ?></td>
    <td><? echo $rows['orders_id']; ?></td>
    <td><? echo date ("d/m/Y", strtotime ($rows['date_purchased']));?></td>
    <td></td>
    <td></td>
    <td></td>
    <td><? echo $rows['delivery_street_address']; ?>, <? echo $rows['delivery_suburb']; ?>, <? echo $rows['delivery_city']; ?>, <? echo $rows['delivery_state']; ?>, <? echo $rows['delivery_country']; ?> <? echo $rows['delivery_postcode']; ?></td>
    <td><input type='text' name='extra_info' id='extra_info' value='<? echo $rows['extra_info']; ?>'></td>
  </tr>
  <?php
}
?>

Any ideas whats wrong with this? Thanks for taking the time to help me with this.

Why not use a JOIN ?


SELECT
   something
 , anything
 , just_not_the_dreaded_star
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

:slight_smile:

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?

i love you :wacko:

SELECT ‘customers_name’, ‘customers_email’,

those are character strings you’re selecting

as far as the syntax goes, that’s perfectly valid, but as far as the semantics goes, you probably want column values instead of those strings

remove da quotes :wink:

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>';
?>