One method would be to store the entire result set into a nested array in one loop, then output the array in another loop:
$sql = "SELECT category, subcategory
FROM products
ORDER BY category, subcategory";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
$products[$row['category']][] = $row['subcategory'];
}
foreach ($products as $category => $subcategories)
{
$num_subcategories = count($subcategories);
echo "{$category} ({$num_subcategories})\n";
foreach ($subcategories as $subcategory)
{
echo "-----------{$subcategory}\n";
}
}
This is a little bit more confusing, I know. In particular, I think the line that creates the arrays deserves attention:
$products[$row['category']][] = $row['subcategory'];
This is a simple assignment statement -- the right hand side is being assigned to the left hand side:
<some variable> = <some value>
You can tell immediately that the variable being assigned to on left hand side is an array index. What might be confusing is all the square brackets floating around.
The two empty square brackets ("[]") is a special notation in PHP that appends a new numerical index to the end of an array. For example, the following code:
$foo[] = "Hello";
$foo[] = "World";
Creates an array with two indexes. It's equivalent to this:
$foo = array("Hello", "World");
but spares us from having to call the array() function. This is useful when we aren't CREATING an array, but just tacking things onto the end of an existing one.
So now we understand this much:
<some array>[] = <some value>;
The right hand side is easy -- it's just the value stored in the "subcategory" index of the result row variable.
So far, what we're saying is "Append the subcategory in the current result row to the end of some array":
<some array>[] = the subcategory;
Now let's look at the rest of the left hand side, arguably the most complex and confusing part of the line:
$products[$row['category']]
We see that there are two sets of square brackets. We know that you can access an array using square bracket notation:
$array_variable[<some index>]
Knowing this, you can see that we're accessing an array called "products":
$products[<some index>]
The index we're using is the VALUE stored in another array -- more specifically, it's the value stored in the "category" index of the result $row variable.
Let me restate this -- we're not setting the index to "category", we're setting it to the VALUE stored in another array's "category" index.
To use your example, the values stored in the "category" rows are "Shampoo", "Soup", and "ToothPaste".
Suppose we're looking at the "PertPlus" row. The "category" is "Shampoo" and the "Subcategory" is "PertPlus".
Going back to our code:
$products[$row['category']][] = $row['subcategory'];
We can immediately substitute the values we know:
$products["Shampoo"][] = "PertPlus";
This looks a lot easier to understand, right?
The last part we need to discuss is the concept of a "nested array". A nested array is an array where the value stored an an index is another array. This is also known as a "multidimensional" array.
So here, we're saying that $products["Shampoo"] is itself another array.
I _strongly_ suggest using the print_r() function to view the contents of arrays. It will be of an incredible aid to understanding the concept of multidimensional arrays and help incredibly when debugging applications that use them.
For example, this is what the print_r() output would look like for our $products array after the first while() loop:
Code:
Array
(
[Shampoo] => Array
(
[0] => "PertPlus"
[1] => "SunSilk"
)
[Soup]
(
[0] => "Lux"
)
[ToothPaste]
(
[0] => "Creast"
[1] => "Signal2"
[2] => "Collgate"
)
)
This should help you understand the multidimensional $products array.
In the output loop, we use the foreach() construct. foreach() iterates over each element in the array, and extracts the index and value from the array into variables that you name. In our code, we had this:
foreach ($products as $category => $subcategories)
Here, $category holds the index, and $subcategories is the value.
If you look at the print_r() output I typed above, you can determine that the first iteration of the foreach() loop will set $category to "Shampoo", and $subcategories to an Array.
We use the count() function to get the number of elements in the $subcategories array. This lets us print it out next to the category name.
Then, we use foreach() again to iterate over all the items in the subcategory array:
foreach ($subcategories as $subcategory)
Notice that this foreach() looks a little different than the first one... There's only one variable after the "as". This is because we don't want (or care about) the indexes in this array. These indexes, if you recall, were created by the empty brackets ("[]") in the first loop. All we care about are the values in that array. We print each value ot the screen.
After we're done printing all the values, we print a newline character to separate this category from the next.
Does this all make sense?
Again, there are several ways of doing this. Another way would've involved making multiple queries, but I think that this way is sufficient and pretty easy to understand.
It does have its drawbacks, though -- notice that we store the entire result set into the $products array before printing it out. If you have a very large database table, this approach might take up too much memory because the $products array might get large, especially when you consider that you're creating it for every user that's running the script at the same time.
The other approach would be to make one query to return just the names of the categories and the number of rows belonging to each category, and another query to return all the sub categories.
This approach has the benefit that you don't have to store any large amounts of data in a PHP array, but has the drawback that you need to make multiple queries of the database.
HTH!
Take care,
Nik
http://www.bigaction.org/
MultiLevel PHP
