Php, Mysql . Generate table based from multidimensional array

I’m am new at php and I thought this will be easy because I’ve managed to do a lot more difficult stuff before with php, but somehow I got stuck. Appreciate if anyone will be able to help me..

For example, I have a three tables:

• 1st table I hold names of food (it is subcategory table)
• 2nd table I hold type of the food (it is category table)
• 3rd table I hold what each person by id likes to eat (main table, related to other two)

TblFood:

1. Food_ID;
2. Food_name

TblTypes:

1. Type_ID;
2. Type_name

TblLikes:

1. Person_id;
2. Food_ID;
3. Type_ID

These tables are just examples what I hold, but I think it will be easier to understand what I want to ask. So I need that I will be able to output types of food and exact names of food related to those types, based from TblLikes. For example if I choose person with id Person_id = ‘1’ I need to output”

Person 1 likes:

FRUITS

• apples
• bananas
• peaches
• watermelon

VEGETABLES

• potatoes
• onions

MEAT

• turkey
• pork

etc.

• Another name of food related to the type above

So basically I need that type of food and name of food will be associated with each other based upon TblLikes.

This is how I have been doing everything so far :

mysql_select_db($database_localhost, $localhost);

$query_likes = " SELECT TblLikes.Person_id, TblLikes.Type_ID, TblTypes.Type_name, TblLikes.Food_ID, TblFood.Food_name
FROM (TblFood RIGHT JOIN TblLikes ON TblFood.Food_ID = TblLikes.Food_ID) LEFT JOIN TblTypes ON TblLikes.Type_ID = TblTypes.Type_ID
 WHERE TblLikes.Person_id  = '1' ";
$likes = mysql_query($query_likes, $localhost) or die(mysql_error());
$row_likes = mysql_fetch_assoc($likes);
$totalRows_likes = mysql_num_rows($likes)开发者_JAVA百科;
?>
<table border="1" cellpadding="5" cellspacing="5">
  <tr>
    <td>Types of food</td>
  </tr>
  <tr>
   <?php  for ($i = 1; $i <= sizeof($row_likes['Type_name']); $i++)?>
    <td><?php echo $row_likes['Type_name'];?></td>
  </tr>
 <?php  for ($i = 1; $i <= sizeof($row_likes['Food_name']); $i++) {
     echo $row_likes[$i]['Food_name'];

 }?>

</table>

Thank you in advance..

---

There are many ways to do this, but I suggest you should join your 3 tables and add a group by clause for types, and then save it as an array. Then you should run a foreach through the array and select from the first query without the group by clause, but with a where clause for food type, then run the individual items through another foreach. e.g. :

$query_type_likes = "
    SELECT 
        TblLikes.Person_id, 
        TblLikes.Type_ID, 
        TblTypes.Type_name, 
        TblLikes.Food_ID, 
        TblFood.Food_name
    FROM 
        (TblFood RIGHT JOIN TblLikes ON TblFood.Food_ID = TblLikes.Food_ID) 
        LEFT JOIN TblTypes ON TblLikes.Type_ID = TblTypes.Type_ID
    WHERE 
        TblLikes.Person_id  = '1'
    GROUP BY
        TblLikes.Type_ID
";
$foodType = mysql_query($query_type_likes, $localhost) or die(mysql_error());

foreach ($foodType as $food_Type_Id => $food_Type_Data) 
{
    $query_food_likes = "
        SELECT 
            TblLikes.Person_id, 
            TblLikes.Type_ID, 
            TblTypes.Type_name, 
            TblLikes.Food_ID, 
            TblFood.Food_name
        FROM 
            (TblFood RIGHT JOIN TblLikes ON TblFood.Food_ID = TblLikes.Food_ID) 
            LEFT JOIN TblTypes ON TblLikes.Type_ID = TblTypes.Type_ID
        WHERE 
            TblLikes.Person_id  = '1' AND
            TblLikes.Type_ID = '".mysql_real_escape_string($food_Type_Id)."'
    ";
    $query_food_likes = mysql_query($query_likes, $localhost) or die(mysql_error());

    $html = "<ul>
                <li>$food_Type_Data['Type_name']
                    <ul>";
    foreach ($query_food_likes as $food_likes_Id => $food_likes_Data) 
    {
        $html .= "
                        <li>$food_likes_Data['Food_name']</li>
    }
    $html .= "
                    </ul>
                </li>
            </ul>";
}

echo ($html);