[SOLVED] Question regarding line items / summary page

[versatilewt]

I have a "quote" table and a "quote_line_item" table. Thus, there can be multiple quote_line_items per quote.

 

A simplified example of the tables:

 

QUOTE: quote_id

QUOTE_LINE_ITEM: quote_line_item_id, quote_id, product_id

 

What I'm trying to get

quote_id: 1

      - quote_line_item_id: 2

      - quote_line_item_id: 3

quote_id: 2

        - quote_line_item_id: 4

quote_id: 3

        - quote_line_item_id: 5

        - quote_line_item_id: 6

 

In my application, I want to display a summary of say 20 quotes per page, but I also want to list the line items from those quotes below it. Right now I'm doing a nested foreach (looping through the quotes, performing a select * from quote_line_item_id WHERE quote_id= $quote_id), but I feel like this is inefficient.

 

Can someone please offer advice on a better way to do this? Getting the line items for one quote (for example, a quote detail page) is easy, I am just not sure the best way to list the line items from multiple quotes on a summary page easily.

 

Thank you in advice.

[fenway]

You need to JOIN these two tables together... you can then use PHP to "indent" whenever the id changes.

[versatilewt]

I used the query

 

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

 

And I get the results that I'm looking for, however I'm facing a php issue regarding the output now, so I'm marking this as solved and rewriting the request in the php help forum.