title: Simple Relational Database - Efficient Querying
body: <p>This is essentially very simple I've just been rather verbose about it. My data is structured as shown at the bottom of my post. My goal is to organise the data in a simple spreadsheet format, 'flattening' the relational database. Something like:</p>
  
  <pre><code>Product1 URL
  Product2 URL URL URL
  Product3 URL
  </code></pre>
  
  <p>My initial approach is as below. The reason I'm looking for an alternative is I have > 10,000 rows in the products table and each product has an average of 5 URLs. This generates an enourmous amount of MySQL queries (in fact exceeding my shared hosting resources), there must be a better way, right? My code has been simplified to distill the essence of my question.</p>
  
  <h1>My Approach</h1>
  
  <pre><code>//Query to get all products
  $products = mysql_query("SELECT * FROM products");
  
  $i = 0;
  //Iterate through all products
  while($product  = mysql_fetch_array($products)){
  
      $prods[$i]['name'] = $product['name'];
      $prods[$i]['id'] = $product['id'];
  
      //Query to get all URLs associated with this particular product
      $getUrls = mysql_query("SELECT * FROM urls WHERE product_id =".$product['id']);
  
      $n = 0;
  
      while($url = mysql_fetch_array($getUrls)){
  
          $prods[$i]['urls'][$n] = $url['url'];  
      }
  }
  </code></pre>
  
  <h1>The Result</h1>
  
  <p>To be clear, this is the intended result, the output was expected. I just need a more efficient way of getting from the raw database tables to this multidimensional array. I suspect there's more efficient ways of querying the database. Also, I understand that this array uses a lot of memory when scaled up to the required size. As my aim is to use this array to export the data to .csv I would consider using XML (or something) as an intermediary if it is more efficient. So feel free to suggest a different approach entirely.</p>
  
  <pre><code>//The result from   print_r($prods);
  
  Array
  (
      [0] =&gt; Array
          (
              [name] =&gt; Swarovski Bracelet
              [id] =&gt; 1
              [urls] =&gt; Array
                  (
                      [0] =&gt; http://foo-example-url.com/index.php?id=7327
  
                  )
          )
      [1] =&gt; Array
          (
             [name] =&gt; The Stranger - Albert Camus
             [id] =&gt; 2
             [urls] =&gt; Array
                 (
                     [0] =&gt; http://bar-example-url.com/index.php?id=9827
                     [1] =&gt; http://foo-foo-example-url.com/index.php?id=2317
                     [2] =&gt; http://baz-example-url.com/index.php?id=5644
                  )
          )
  )
  </code></pre>
  
  <h1>My Database Schema</h1>
  
  <p><strong>Products</strong></p>
  
  <pre><code>id  product_name
  
  1   Swarovski Bracelet
  2   The Stranger - Albert Camus
  3   Finnegans Wake - James Joyce
  </code></pre>
  
  <p><strong>URLs</strong></p>
  
  <pre><code>id  product_id  url                                                price
  
  1   1           http://foo-example-url.com/index.php?id=7327       £16.95
  2   2           http://bar-example-url.com/index.php?id=9827       £7.95
  3   2           http://foo-foo-example-url.com/index.php?id=2317   £7.99
  4   2           http://baz-example-url.com/index.php?id=5644       £6.00
  5   3           http://bar-baz-example-url.com/index.php?id=9534   £11.50
  </code></pre>
  
  <p>URLs.product_id links to products.id</p>
  
  <p>If you've read this, thank you for your incredible patience! I look forward to reading your response. So how can I do this properly?</p>