简单关系型数据库——高效查询

Question

这本质上非常简单，我只是对此说得相当冗长。我的数据结构如帖子底部所示。我的目标是以简单的电子表格格式组织数据，“扁平化”关系数据库。类似的：

Product1 URL
Product2 URL URL URL
Product3 URL

我最初的方法如下。我正在寻找替代方案的原因是我有 >产品表中有 10,000 行，每个产品平均有 5 个 URL。这会产生大量的 MySQL 查询（实际上超出了我的共享托管资源），一定有更好的方法，对吧？我的代码已被简化，以提炼我的问题的本质。

我的方法

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

结果

需要明确的是，这是预期的结果，输出是预期的。我只需要一种更有效的方法从原始数据库表获取此多维数组。我怀疑有更有效的方法来查询数据库。另外，我知道该数组在扩展到所需大小时会使用大量内存。由于我的目标是使用此数组将数据导出到 .csv，因此如果效率更高，我会考虑使用 XML（或其他内容）作为中介。因此，请随意提出完全不同的方法。

//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
                )
        )
)

我的数据库架构

产品

id  product_name

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

URL

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 链接到 products.id

如果您已阅读本文，感谢您的耐心等待！我期待着阅读您的回复。那么我怎样才能正确地做到这一点呢？

Answer 1

用于“扁平化”url 的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 代码，我只是用空格分隔了 url。

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

结果

[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

即使您想拉回 csv 网址，也可以通过一次查询拉回该确切信息。

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

然后您对数据库进行 1 次查询，即可返回您需要的内容。

或者更干净的方法是 bruce 建议的，然后让 PHP 将所有结果合并在一起。

Answer 3

您可以通过一个查询来完成此操作：

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

伪代码：

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