Simple Relational Database - Efficient Querying

Question

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:

Product1 URL
Product2 URL URL URL
Product3 URL

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.

My Approach

//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'];  
    }
}

The Result

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.

//The result from   print_r($prods);

Array
(
    [0] => Array
        (
            [name] => Swarovski Bracelet
            [id] => 1
            [urls] => Array
                (
                    [0] => http://foo-example-url.com/index.php?id=7327

                )
        )
    [1] => Array
        (
           [name] => The Stranger - Albert Camus
           [id] => 2
           [urls] => Array
               (
                   [0] => http://bar-example-url.com/index.php?id=9827
                   [1] => http://foo-foo-example-url.com/index.php?id=2317
                   [2] => http://baz-example-url.com/index.php?id=5644
                )
        )
)

My Database Schema

Products

id  product_name

1   Swarovski Bracelet
2   The Stranger - Albert Camus
3   Finnegans Wake - James Joyce

URLs

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

URLs.product_id links to products.id

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?

Answer 1

The SQL

 SELECT p.id, p.product_name, u.url 
 FROM products p, urls u 
 WHERE p.id = u.product_id ORDER BY p.id

PHP code to "flatten" the urls, I have just seperated the urls with a space.

$i = 0;
$curr_id;
while($product  = mysql_fetch_array($products))
{
    $prods[$i]['name'] = $product['product_name'];
    $prods[$i]['id'] = $product['id'];

    if($product['id'] == $curr_id)
    {
        $prods[$i]['urls'] .= " ".$url['url'];  
    }
    else
    {
        $prods[$i]['urls'] = $url['url']; 
    }
    $i++;
    $curr_id = $product['id'];
}

The Result

[0] => Array
        (
            [name] => Swarovski Bracelet
            [id] => 1
            [urls] => http://foo-example-url.com/index.php?id=7327
        )
  [1] => Array
        (
           [name] => The Stranger - Albert Camus
           [id] => 2
           [urls] => http://bar-example-url.com/index.php?id=9827 http://foo-foo-example-url.com/index.php?id=2317 http://baz-example-url.com/index.php?id=5644

        )

Answer 2

You are able to pull back that exact information with one query even if you want to pull back the csv of urls.

SELECT 
    p.id, p.product_name, 
    GROUP_CONCAT(u.url) AS URLS 
    FROM products p LEFT JOIN urls u 
    ON p.id = u.product_id 
    GROUP BY p.id, p.product_name

And then you have 1 query to your database that brings back what you need.

Or the cleaner approach is how bruce suggested and then let PHP merge all of the results together.

Answer 3

You can accomplish this in one query:

select * from products join urls on products.id = urls.product_id order by products.id

pseudocode:

while(query_result) {
     if (oldpid == pid) { print ", \"url\"" }
     else { print "pid, \"url\"" }
     oldpid = pid;
}