One way to accomplish what you want:
Code: Select all
Table stores:
| store_id | store_name | other store fields |
Table brands:
| brand_id | brand_name | other brand fields |
Table products:
| product_id | product_name | brand_id | other product fields |
Table stores_products:
| store_id | product_id |
To generate a result object with all the information you need to generate the list you want, execute:
Code: Select all
SELECT * from stores_products
inner join stores on stores.store_id = stores_products.store_id
inner join products on products.product_id = stores_products.product_id
inner join brands on brands.brand_id = products.brand_id
This will give you a result object whose rows look like this:
Code: Select all
store_id product_id store_id store_name product_id product_name brand_id brand_id brand_name
1 1 1 Walmart 1 Nike Shoes 1 1 Nike
1 2 1 Walmart 2 NB Shoes 2 2 New Balance
2 2 2 Target 2 NB Shoes 2 2 New Balance
Then, I'd build a nice array like this:
Code: Select all
$overallArray = array();
while ($row = mysql_fetch_assoc($result)) {
$store = $row['store_name'];
$brand = $row['brand_name'];
$product = $row['product_name'];
if (!in_array($store,$overallArray)) {
$overallArray[$store] = array($brand => array($product));
}
elseif (!in_array($brand,$overallArray[$store])) {
$overallArray[$store][] = array($brand => array($product));
}
else {
$overallArray[$store][$brand][] = $product;
}
}
The array would look something like this:
Code: Select all
Array (
'Walmart' => Array (
'Nike' => Array (
'Nike Shoes'
)
'New Balance' => Array (
'NB Shoes'
)
)
'Target' => Array (
'New Balance' => Array (
'NB Shoes'
)
)
)
Now that you've got that array, simply go:
Code: Select all
foreach ($overallArray as $store => $brandArray) {
echo "<b>{$store}</b><br />";
foreach ($brandArray as $brand => $productArray) {
echo "<u>{$brand}</u><br />";
foreach ($productArray as $product) {
echo "<i>-- {$product}</i><br />";
}
}
echo "<br />";
}
There you have it. I haven't tested it or anything, but I'm pretty sure it'll do the trick.