Question: I am tring to display all my products within their category, so something like:

Category Name1
 Product Name1, Product Name2 ...
Category Name2
 Product Name3, Product Name4 ...

I am using oscommerce, So the database structure is not my choice. The database tables I need to use are

products\_to\_categories: holds the products\_id and categories\_id
products\_description:holds products\_id and products\_name (other info in this table are not important)
category\_description: holds the categories\_id and categories\_name

I have tried everything and i only can get to echo the products (all together) and categories all together, but I can't get them in a way that all the products within a category sit under the specified name

As I said everything I tried resulted in a way that simply echoed all the categories AND THEN all the products

I really hope you can help

thanks

Answer: 
There are two ways of approaching this, and the idea behind is was already given as an answer here, by Zod.

The first way is using category\_description to get **all** categories (whether it's full of products or not), the second way is to only extract categories that are used (i.e., not getting a complete list of categories). I'll go through the first way; getting all categories.

Run through category\_description, and get the IDs and category names into an Array.

Now, run through that array with `foreach` and grab all product\_ids from products\_to\_categories. (`SELECT products_id FROM products_to_categories WHERE categories_id=[CATEGORY ID GOES HERE]`).

Now that you have your product IDs that are associated with the concurrent category, you can run through the products\_description and get their name. Essentially, your code should look like:

```
foreach ($categories as $category) {
   ...display category name and run code to get product ids...
   foreach ($products as $product) {
       ...get product name from products_description...
       ...display product name...
   }
}

```
You'll likely need a `LEFT JOIN` in your SQL, to merge the three tables together, and order the query by `category_name, product_name`.

Something like:

```
SELECT p.product_id, p.product_name, c.category_name FROM `products_description` p
LEFT JOIN `products_to_categories` linker ON linker.product_id=p.product_id
LEFT JOIN `category_description` c ON linker.category_id=c.category_id
ORDER BY c.category_name, p.product_name

```

Then your PHP to output the list would be something like:

```
$data = array(); // Data from query above
$cur_category = '';
foreach($data as $product) {
  if ($product['category_name'] != $cur_category) {
    // Category changed; display it
    echo $product['category_name'].": ";
    $cur_category = $product['category_name'];
  }
  echo $product['product_name'].", ";
}

```