在类别中拆分数据集，并在PHP / MySQL中计算这些类别中的数据集

Question

I have a menu. Every meal has a category like beef, pork, chicken, salad. Now I want to fetch every meal, split them according to the category and save them in an array. So that I have for every category an Array with meals. The last step would be counting the amount of meals in each category but this is easy when I am there.

My current attempt is very bad. I make a mysql_query with a filter for every single category and a second to count the datasets. So I have for one category two queries. If I have at the end like 30 categories, the server have to process 60 queries for one page view.

for($i=0; $i < $30; $i++)
{
    $fetchMeal="SELECT * FROM Menu WHERE Categorie = '$categorie[$i]'";
    $countMeals = mysql_query("SELECT count(*) from Menu WHERE Categorie = '$categorie[$i]'");
}

I want only one to fetch all datasets, split them in categories and count the datasets within these categories. Does anyone know a good and easy way to accomplish this?

Example table:

name          | categorie
-----------------------
tomato-salad  | salads
potatotfish   | fish
peppersteak   | beef
schnitzel     | pork
ceasar-salad  | salad
another-fish  | fish
fish-fish     | fish
roast pork    | pork

etc...

now I want to have arrays for each category:

salads[0] -> tomato-salad
salads[1] -> ceasar-salas

fish[0] -> potatofish
fish[1] -> another-fish
fish[2] -> fish-fish

beef[0] -> peppersteak

pork[0] -> schnitzel
pork[1] -> roast pork

Answer 1

My solution is now that I order datasets by their categories. If I echo with the results it should look like this:

category | meal
---------+----------
fish     | cookes fish
fish     | potato-fish
fish     | fish-fish
fish     | another-fish
pork     | roast pork
pork     | schnitzel
beef     | peppersteak
salad    | tomato-salad
salad    | ceasar-salad 
salad    | another-salad

Then you have to find out how many categories you have. Because I have the name of the categories in another table I can count them with:

$result = mysql_query("SELECT count(*) from categories");
$NumberOFCategories = mysql_result($result, 0);

When you don't have an extra table you have to make this query:

SELECT DISTINCT categorie FROM menu

and count your gotten datasets with a PHP foreach loop. Then I saved every dataset in an array with a foreach loop.

$i = 0 //counter
$menu = array();

while($tabellenInhalt = mysql_fetch_object($erg))
{
    $menu[$i][0]=$tabellenInhalt -> Category ;
    $mneu[$i][1]=$tabellenInhalt -> Meal;

    $i = $i+1; 
}

Now there comes the tricky part. You save the first category in a holder variable. And create a variable for a counter which you will need later in the foreach loop.

Then you make a for-loop which is as long as many categories you have (you got the Number at the beginning). After that you make a foreach loop through your array as long as your holder variable and the category are equal. I show you my code:

$m = 0; //counter for every dataset
$j = 0; //counter for the datasets in a category
$holder = $menu[$m][0]; //holds the actual/next category
$splittedMenu = array();

for($n = 0; $n < $NumberOfCategories; $n++) //Count three every company
{
        while($menu[$m][2] == $halter) //test if Category is still the same.
        {
            $splittedMenu [$n][$j][0] = $menu[$m][0];
            $splittedMenu [$n][$j][1] = $menu[$m][1];
            $m = $m + 1; //count threw all datasets
            $j = $j + 1; // count threw all datasets from one category.
        }
    $j = 0; //counter gets resetted when the next category gets called.
    $halter = $splittedMenu [$m][2]; //go to next category
}

Now your first dimension of you array separates the categories. The second dimension is for your meals and in the third you hold the different data (name of the category and the name of the meal).