php - 在其父级(类别)之后列出产品

Question

我对 php 比较陌生，我正在使用 oscommerce 在报告中创建类别、子类别和子类别中可用产品的列表。我设法创建了类别列表，后面是子类别(子类别)。我在尝试在子类别之后列出产品时遇到了死胡同。这是编码的片段:

function category_list( $category_parent_id = 0 )
{
$sql  = 'select cd.categories_name,c.categories_id, c.parent_id, c.sort_order from ' . TABLE_CATEGORIES . ' c, ' . TABLE_CATEGORIES_DESCRIPTION . ' cd where c.categories_id = cd.categories_id AND c.parent_id='.$category_parent_id;
$res  = tep_db_query( $sql );
$cats = array();
 while ( $cat = tep_db_fetch_array( $res ) )
  {
    $cats[] = $cat;
  }
 if (count($cats) == 0)
    {
  return '';
  }
 $list_items = array();
foreach ( $cats as $cat )
 {
$list_items[] = '<tr class="dataTableRow"><td class="dataTableContent">'; 
if($category_parent_id != 0)$list_items[] = '&nbsp;&nbsp;&nbsp;';
if($category_parent_id == 0)$list_items[] = '<b>';
$list_items[] = $cat['categories_name'];


if($category_parent_id == 0)$list_items[] = '</b>';
$list_items[] = '</td><td class="dataTableContent">'; 
$list_items[] = category_list( $cat['categories_id'] );
$list_items[] = '</td></tr>'; 
}
$list_items[] = '';
return implode( '', $list_items );

}  
echo category_list();

为了列出产品，我有两个需要使用的表，product_to_cat 和 prod_descrip，通过使用 product_id 字段连接.为了在正确的父级中列出，product_to_cat 和 cat 表与 category_id 连接在一起。我将如何在正确的类别中打印正确的产品？

Answer 1

假设表格看起来像这个 SQL Fiddle ,那么这只是所有表之间的简单连接

select c.parent_id, c.categories_id, cd.categories_name,
       p.product_id, pd.description
from categories c
join categories_description cd on cd.categories_id = c.categories_id
left join product_to_cat p on p.category_id = c.categories_id
left join prod_descrip pd on pd.product_id = p.product_id
order by c.parent_id, c.categories_id, p.product_id

我不知道 sort_order 如何适合这张图片。

如果您只想要一个类别的产品，请在循环中执行以下选择

select p.product_id, pd.description
from product_to_cat p
left join prod_descrip pd on pd.product_id = p.product_id
where p.category_id = $cat['categories_id']