any ideas?

stebut05

Hi,

Thanks for taking a look at this. I need to generate a report by querying the database, but i keep having problems. The report i need to generate should look like something below

                        SS10 | SS20 | SS30 | SS40 | SS50 | SS60 | (product codes)

Company A 1 1

Company B 1 1 1 1

Company C 1 1 1

Basically i delivery date is posted and i need to display all product codes, then display all the companies that have ordered for that delivery date (not all companies are diaplyed) and display which particular product they have ordered. Thanks in advance for any help, advice, suggestions. Have a nice day!!

Regards,

Steven

stebut05

I should mention that products, companies and , order and order details are in seperate tables. The relationship details:

order - delivery date (posted date)
companies and order are linked with company id
order details and orders are linked with order id
order details and products are linked with product id

tomhath

My first cut would be to read all company/product combinations and loop through the result in php.

Something like:

SELECT DISTINCT company_id, product_id
FROM orders o
INNER JOIN companies c ON c.company_id_id = o.company_id
INNER JOIN order_details od ON od.order_id = o.order_id
INNER JOIN products p ON p.product_id = od.product_id 
WHERE o.delivery_date = 'mm/dd/yyyy'
ORDER BY company_id, product_id

Building an HTML table from that shouldn't be difficult. You'll just have to keep track of which column each product_id goes in.