Create multiple html tables, each grouped by a column from one mysql query

Beginners - Learning PHP
#1

I have a table with the following info:
Meal ID: 1
Food: Apple
Calories: 50

Meal ID: 2
Food: Banana
Calories: 75

Meal ID: 2
Food: Almonds
Calories: 100

I want query this info and then present the data in multiple tables, grouped by the Meal Id.
Currently I have working code to pull the data and create one table that contains all of the info. I just can’t wrap my head around how to best split this into multiple tables (there could be as many as 6 different meal ids) without writing multiple mysql queries. There must be a cleaner way.

Thank you

#2

Post the code you have so far.

#3

[php] $query = ‘SELECT food_name, food_calories, food_id, food_tracking_serving, food_serving_size, food_protein, food_total_carbs, food_total_fat, food_sodium, food_potassium, food_fiber, food_pic_url, meal_id FROM food natural join food_tracking where food_tracking_date = DATE(NOW()) ORDER BY meal_id DESC’;

// Run the query:
if ($result = mysql_query($query, $dbc)) {

print "<table class=responsive>";
print "<tr>";
print "<th>Breakfast </th>";
print "<th>Calories</th>";
print "<th>Protein</th>";
print "<th>Carbs</th>";
print "<th>Fat</th>";
print "<th>Sodium</th>";
print "<th>Potassium</th>";
print "<th>Fiber</th></tr>";

//set sum of columns to zero
$sum_calories = 0;
$sum_protein = 0;
$sum_total_fat = 0;
$sum_total_carbs = 0;
$sum_sodium = 0;
$sum_potassium = 0;
$sum_fiber = 0;
;
// Retrieve the returned record:
while ($row = mysql_fetch_array($result))
{
//This sets the var for food pictures
$food_pic_url = $row['food_pic_url'];

//add up sums of columns
$sum_calories +=  "{$row['food_tracking_serving']}"*"{$row['food_calories']}";
$sum_protein  +=  "{$row['food_tracking_serving']}"*"{$row['food_protein']}";
$sum_total_carbs +=  "{$row['food_tracking_serving']}"*"{$row['food_total_carbs']}";
$sum_total_fat +=  "{$row['food_tracking_serving']}"*"{$row['food_total_fat']}";;
$sum_sodium  +=  "{$row['food_tracking_serving']}"*"{$row['food_sodium']}";
$sum_potassium  +=  "{$row['food_tracking_serving']}"*"{$row['food_potassium']}";
$sum_fiber +=  "{$row['food_tracking_serving']}"*"{$row['food_fiber']}";

print " <tr><td>".  "<img align ='left' src='" ."{$row['food_pic_url']}" . "'</img><Span class='servings'>". "{$row['food_tracking_serving']}"  ." Serving of " . "{$row['food_serving_size']}"."</span></br>"." {$row['food_name']}"."</td> ";
print " <td>". "{$row['food_tracking_serving']}"*"{$row['food_calories']}" . "</td>";
print " <td>". "{$row['food_tracking_serving']}"*"{$row['food_protein']}" . "</td> ";
print " <td>". "{$row['food_tracking_serving']}"*"{$row['food_total_carbs']}" . "</td> ";
print " <td>". "{$row['food_tracking_serving']}"*"{$row['food_total_fat']}" . "</td> ";
print " <td>". "{$row['food_tracking_serving']}"*"{$row['food_sodium']}" . "</td> ";
print " <td>". "{$row['food_tracking_serving']}"*"{$row['food_potassium']}" . "</td> ";
print " <td>". "{$row['food_tracking_serving']}"*"{$row['food_fiber']}" . "</td> </tr>";

}
print " <tr><td>"."Total". "</td>";
print " <td> <strong>". $sum_calories . "</strong> </td>";
print " <td> <strong>". $sum_protein . "</strong> </td>";
print " <td><strong>". $sum_total_carbs . "</strong> </td>";
print " <td><strong>". $sum_total_fat . "</strong> </td>";
print " <td><strong>". $sum_sodium . "</strong> </td>";
print " <td><strong>". $sum_potassium . "</strong> </td>";
print " <td><strong>". $sum_fiber . "</td></strong></tr>";
print "</table>";

?>

  [/php]
#4

It sounds like you just need to build a multi-dimensional array with your results before you output them. For example:

[php]
// Build multi-dimensional array using meal_id
$grouped = array();
while ($row = mysql_fetch_array($result)) {
$grouped[$row[‘meal_id’]][] = $row;
}

// Output results
foreach($grouped as $meal_id => $values) {
// Start table for meal_id
foreach($values as $value) {
// Add columns for meal_id
}
// End table for meal_id
}
[/php]

Also, why are you converting your integers to strings here?

[php]$sum_calories += “{$row[‘food_tracking_serving’]}”*"{$row[‘food_calories’]}";[/php]

They should remain as integers (assuming they are integers in your table)

[php]$sum_calories += $row[‘food_tracking_serving’] * $row[‘food_calories’];[/php]

#5

Thanks M@tt, that worked great. Seeing that in practice helped to understand multi-dimensional arrays. Also thanks for pointing out the int to string. I’m a newbie so I mistakenly did that. I appreciate your help!

#6

No problem. Maintaining variable types is rather important.

If you are unsure if a variable type is correct you could use the function var_dump($row);

If it is not the proper type you should cast it. For example if you have a string that should be an integer you could type cast it using $int = (int) $string.

This can be useful in preventing SQL injection when using $_GET, $_POST (or $_REQUEST) variables which would prevent someone from using a string instead of an integer. For example,

$id = (int) $_GET[‘my_id’];

Would (help) to prevent anyone from using my_id to inject SQL strings into your queries.

Sponsor our Newsletter | Privacy Policy | Terms of Service