如何在php中针对嵌套循环优化mysql查询

Question

​

我有两个表，一个是category表，另一个是products.Catid表。我需要使用两个while循环来显示表中的数据。它工作得很好，但需要更多的时间，如果它处理更多的类别，就会抛出错误。

   <table>
<tr>
<?php
$cat=mysql_query("select * from category");
while($catquery=mysql_fetch_assoc($cat))
{?>
<td><?php echo $catquery['catid'] ?></td>
<td><?php echo $catquery['catname']?></td>
<?php 
 $catid=$catquery['catid'];
 }?>
<?php
$product=mysql_query("select * from product where catid=$catid");
 while($productquery=mysql_fetch_assoc($product))
{ 
?>
<td><?php echo $productquery['productname'] ?></td>
</tr>
</table>

Answer 1

select * from category a left join product b
on a.catid = b.catid

最好将列逐个定义，以便将数据导入PHP，如下所示：

select a.catid as category_catid, 
       a.catname as category_name,
       b.productname as product_productname
from category a left join product b
on a.catid = b.catid

如果你想显示所有的产品，即使catid已经在分类中被删除了，你可以使用这个(和FULL JOIN一样)：

select a.catid as category_catid, 
       a.catname as category_name,
       b.productname as product_productname
from category a left join product b
on a.catid = b.catid
union
select a.catid as category_catid, 
       a.catname as category_name,
       b.productname as product_productname
from category a right join product b
on a.catid = b.catid

下面是获取数据的方法：

while($catquery=mysql_fetch_assoc($cat))
{?>
<td><?php echo $catquery['category_catid'] ?></td>
<td><?php echo $catquery['category_name']?></td>
<td><?php echo $catquery['product_productname']?></td>
....

Answer 2

尝试此查询。它将列出所有类别的详细信息及其各自的产品名称(catproducts-在以下查询中)。

<table>

<?php
$cat=mysql_query("select *,(select group_concat(productname) as catproducts from product where product.catid= category.catid) as catproducts from category");
while($catquery=mysql_fetch_assoc($cat)) { ?>
  <tr>
  <td><?php echo $catquery['catid']; ?></td>
  <td><?php echo $catquery['catname'];?></td>
  <td><?php echo $catquery['catproducts']; ?></td>
  </tr>
<?php }//end while ?>

</table>

因此，您的输出将是

catid  catname      catproducts

1      stationary   book,pen
2      leather      wallets,bags