title: PHP/MySQL Orders project with items and accessories tables
body: <p><strong>Edited, please see final paragraph.</strong></p>
  
  <p>I am building a order system that has the following tables and example data:</p>
  
  <ul>
  <li><strong>Products</strong>
  <ul>
  <li><em>id</em>: 1, <em>Name</em>: Speaker, <em>Price</em>: £12.00</li>
  <li><em>id</em>: 2, <em>Name</em>: Mic, <em>Price</em>: £4.00</li>
  </ul></li>
  <li><strong>Orders</strong>
  <ul>
  <li><em>id</em>: 1, <em>Date</em>: 2011-01-01</li>
  </ul></li>
  <li><strong>Orders_items</strong>
  <ul>
  <li><em>id</em>: 1, Order_id: 1, Product_id: 1, Qty: 2</li>
  <li><em>id</em>: 2, Order_id: 1, Product_id: 2, Qty: 1</li>
  </ul></li>
  <li><strong>Accessories</strong>
  <ul>
  <li>id: 1, <em>Name</em>: Cable</li>
  <li>id: 2, <em>Name</em>: Mic Stand</li>
  <li>id: 3, <em>Name</em>: Speaker Stand</li>
  </ul></li>
  <li><strong>Link_table</strong>
  <ul>
  <li>id: 1, Product_id: 1, Acc_id: 1, Qty_ship: 2</li>
  <li>id: 2, Product_id: 1, Acc_id: 3, Qty_ship: 1</li>
  <li>id: 3, Product_id: 2, Acc_id: 2, Qty_ship: 1</li>
  </ul></li>
  </ul>
  
  <p>At the moment I use the PHP <code>while</code> to write a table containing the line items (no accessories).</p>
  
  <p>Now for the question: I would like to output to a table using PHP that retrieves the line items and also the accessories for that item. Like a pick list.</p>
  
  <pre><code>Order 1
  Qty Name     Price   Accessories
  1   Speaker  £12.00  2 x Cable
                       1 x Speaker stand
  
  1   Mic      £ 4.00  1 x Mic stand
  </code></pre>
  
  <p>Is this possible? The only way i can think about doing it is add a <code>mysql_query</code> in the while statement that looks up the accessories when the line item is being written?</p>
  
  <h1>I guess what i am asking is.</h1>
  
  <pre><code>SELECT * FROM Order_items WHERE Order_id = 1
  </code></pre>
  
  <p>Then at the end of each row i would like to include the results for Accessories</p>
  
  <pre><code>SELECT Qty_ship, Acc_id FROM Link_table WHERE Product_id = [NUMBER]
  </code></pre>
  
  <p>Can this be done as one lookup from the database?</p>