在其父级（类别）之后列出产品

Question

I'm relatively new to php and I am using oscommerce to create a list of category's, subcategory's, and the products available in the subcategory's, in a report. I've managed to create the list of category's, with the child(subcategory) after it. I have hit a dead end in trying to list the products after the subcategory's. Here is a snip of the coding:

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();

For listing products, I have two tables that will need to be used, product_to_cat, and prod_descrip, joined by using the product_id field. For it to be listed in the correct parent, the product_to_cat and cat table are joined with the category_id. How would I go about printing the correct products in the correct category?

Answer 1

Assuming the tables look like in this SQL Fiddle, then this is just a simple join between all tables

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

I don't know how the sort_order fits in this picture.

If you want the products for one category only, do the following select in your loop

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']