Working with multiple tables

[kletig]

Hi guys

Currently working on a webshop and have been running in to some noob problems.

So i have three tables that i need to pull some data out of. One table stores the suppliers, the other one orders and the third ordered products. It looks something like this:

::: Table: suppliers
supplier_id
supplier_name
supplier_email

::: Table: orders
order_id
customer_id
order_date

::: Table: orderitems
id
order_id
product_id
product_name
product_qty
supplier_id

My goal is to send a mail to each supplier containing the list of products that has been ordered with their supplier id in it.

The only thing i could think of was doing some kind of query inside a foreach, but that seems to be bad practice...right?

I want to start off by using the order_id and then track my way down to each supplier list.

Anyone that could give me some pointers on how to get there?

[Javvy]

You might want to look into JOIN.

So basically your query would have a condition where suppliers.supplier_id = orderitems.supplier_id

[rch1231]

Hello,

I think what you are trying for could look something like this:

Select 
Suppliers.supplier_name,
Suppliers.supplier_email,
orderitems.product_id,
orderitems.product_name,
orderitems.product_qty
from
orderitems
Inner join orders on orderitems.order_id = orders.order_id
Inner join suppliers on orderitems.supplier_id = suppliers.supplier_id 
where 
orders.order_date > '01/01/2012' AND
orders.order_date < '02/01/2012'

What the code does:
The Inner join causes SQL to create a row of output for every record in the orderitems table where there is a record in the suppliers and order table. It joins the suppliers table using the supplier_ID and the orders table using the order_id and only print the records where the order date is between the dates listed. Hope that makes sense...

[kletig]

Well, to select the data is no problem. I just don't know how to do it for each supplier in a good way.

Let's say i select all supplier ID's from the supplier table. How do i pull the associated data out of the other tables for each supplier ID?

[rch1231]

Hello,

I think you are trying to run from the wron table. don't run from suppliers run from the table you are trying to get results for. In this case it would be the order_items table. If you run for all supliers then you get supliers with nothing ordered. If you run from order_items then you get all iteems and their supliers. That is what the join does. It links the data in the individual record to a record in the other table. If you change the select I gave you the new output will be sorted by supplier and give one line per product with a total quantity.

Select
Suppliers.supplier_name,
Suppliers.supplier_email,
orderitems.product_id,
orderitems.product_name,
sum(orderitems.product_qty) as quantity
from
orderitems
Inner join orders on orderitems.order_id = orders.order_id
Inner join suppliers on orderitems.supplier_id = suppliers.supplier_id
where
orders.order_date > '01/01/2012' AND
orders.order_date < '02/01/2012'
group by orderitems.product_id
order by orderitems.product_id asc, 
Suppliers.supplier_name asc

Your output should be like this:

supplier_name   supplier_email       product_id    product_name     quantity
Tom             [e-mail]        67455         Widget                14    
Tom             [e-mail]        67456         Widget Large           4
Victory         [e-mail]   A5551212      Smoke                100

[kletig]

But i want a array/list of products for each supplier. That way i can generate a PDF and attach it to a mail for each supplier.

This is the reason why i tried starting from the suppliers table. Like foreach supplier_id -> get all rows in orderitems where supplier_id=supplier_id AND order_id=x. And put it in an array i can use to create the list to the supplier.

How do i select the ordered items for a specific order and put them in a array for each supplier?

[rch1231]

Try the query I gave you and I believe it is what you are asking for. The date part was just me trying to limit the orders to specific daytes so you can drop it. DO you want output for a supplier if there are no orders for them? There a 50 different ways you could generate the data just trying to give you the easiest. Which looks easier a for loop like you want to do or the code I gave you. Trust me you will be suprised when you see the results.

[kletig]

Your solution gives me a single list with all order items and all suppliers. That's not what i want.

I want a list for each supplier containing the orderitems flagged with their supplier ID.

[pritaeas]

Just separate it in code. SQL will always return a single list. Loop the results, output your list, and when the supplier changes, start a new list.