Jump to content

osCommerce - 5 table query not pulling correct dataset


Recommended Posts



I am having trouble figuring out the best way to structure a query that is pulling data from 5 tables. I need to display a report in PHP that shows something like the following:


customer 1 >> order 1 >> Product 1 >> Manufacturer
                    >> Product 2 >> Manufacturer
                    >> ..        >> ..
         >> order 2 >> Product 1 >> Manufacturer
                    >> Product 2 >> Manufacturer 

customer 2 >> order 1 >> Product 1 >> Manufacturer
                    >> Product 2 >> Manufacturer
                    >> ..        >> ..
         >> order 2 >> Product 1 >> Manufacturer
                    >> Product 2 >> Manufacturer 


I need to display the orders and all products within the orders for each customer. Below is the query that I have that only displays the first order and first product for each customer.



SELECT op.products_id, manufacturers_name, c.customers_lastname, op.products_name, op.products_quantity
FROM orders AS o, orders_products AS op, manufacturers AS m, products AS p, products_to_categories AS pc, customers AS c
WHERE o.orders_id = op.orders_id
AND op.products_id = p.products_id
AND p.manufacturers_id = m.manufacturers_id
AND o.customers_id = c.customers_id
GROUP BY c.customers_lastname
ORDER BY manufacturers_name ASC , c.customers_lastname ASC




This results in the following:


customer 1 >> order 1 >> product 1 >> manufacturer
customer 2 >> order 1 >> product 1 >> mfr
customer 3 >> order 1 >> product 1 >> ...



Can anyone tell what I'm doing wrong so that I only get the first order and product within that order for each customer? 

I  am a little rusty so any help/advice on how to get the results the way I want them would be greatly appreciated. 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.