Need some help with line items

[versatilewt]

So, here's my problem. I'm running php5 and mysql5.

 

I have an app with "quotes" in it, and each quote has line items (products). The database, abbreviated version is:

 

QUOTE: quote_id

QUOTE_LINE_ITEM: quote_line_item_id, quote_id, qty, price

 

I'm trying to set up a summary page, which will list quotes as well as the related quote line items.

 

For instance (I would have specific things like customer name on the "details of quote" line and product name, qty, etc for the "details of product request" lines):

 

Details of quote 1

    Details of product request 1 for quote 1 (quote line item 1)

    Details of product request 2 for quote 1 (quote line item 2)

 

Details of quote 2

    Details of product request 1 for quote 2 (quote line item 3)

 

 

Details of quote 3

    Details of product request 1 for quote 3 (quote line item 4)

    Details of product request 2 for quote 3 (quote line item 5)

    Details of product request 3 for quote 3 (quote line item 6)

 

I have a query which gets the results just fine, each line has the full quote details for the respective quote, and then the line item is at the end of the result set on each line.

 

SELECT q.quote_id, qli.quote_line_item_id
FROM quote q
JOIN quote_line_item qli
USING ( quote_id )

 

I'm using ADODB, and to run the query ($sql = the query listed above), I call

 

$result = $db->GetAssoc($sql)

 

However, when I do a print_r($result), only one of the rows for a quote appears, even if there are multiple line items which appear when querying directly through phpMyAdmin.

 

If I change the query to something like

 

SELECT CONCAT (q.quote_id, qli.quote_line_item_id) AS uniqueID, q.quote_id, qli.quote_line_item_id
FROM quote q
JOIN quote_line_item qli
USING ( quote_id )

 

All of the results show up, but I'll have a hard time looping because now the quote_ids aren't really associated.

 

Can anyone help me to loop through this in the one query? My alternative is to do a foreach where for each quote I do a separate query for the quote line items, which seems really inefficient to me.

 

Hopefully I was clear enough. Thanks in advance.