Ridge Robinson Ridge Robinson - 23 days ago 8
PHP Question

MySQL echo groups with unique value PHP

I apologize if there are similar questions...I had trouble finding them! What I would like to do is select some rows in a database, and then fetch them, but echo them in groups based on unique rows. Let me explain with an example (I have omitted some things for brevity):

I have a select query like this:

SELECT
e.exercisename ExerciseName,
eh.reps Reps,
eh.weight Weight
FROM workouts w
JOIN users u ON w.userid = u.id
JOIN workouts_history wh ON w.id = wh.workoutid
JOIN exercises e ON wh.exerciseid = e.id
JOIN exercises_history eh ON wh.id = eh.workouts_historyid


Right now, this gives me a table based off of this while:

while($workoutrowridge = $workoutresultridge->fetch_assoc()) {
$workoutoutputridge .= '<tr>';
$workoutoutputridge .= '<td>'.$workoutrowridge['ExerciseName'].'</td>';
$workoutoutputridge .= '<td>'.$workoutrowridge['Reps'].'</td>';
$workoutoutputridge .= '<td>'.$workoutrowridge['Weight'].'</td>';
$workoutoutputridge .= '</tr>';
}


that looks like this:

Exercise | Reps | Weight
---------------------------
Squats | 8 | 135
Squats | 8 | 225
Squats | 6 | 315
Squats | 2 | 405
Squats | 1 | 485
Bench (DB) | 8 | 60
Bench (DB) | 6 | 80
Bench (DB) | 4 | 90
Bench (DB) | 2 | 95
Pullup | 4 | 0
Pullup | 3 | 25
Pullup | 1 | 45
Pullup | 1 | 70


What I would like to happen though, is to have a new table echoed for each unique 'ExerciseName' (for example). A workout may have only 1 exercise, or it may have 20, with a different amount of sets for each one, like this:

Exercise | Reps | Weight
---------------------------
Squats | 8 | 135
Squats | 8 | 225
Squats | 6 | 315
Squats | 2 | 405
Squats | 1 | 485

Exercise | Reps | Weight
---------------------------
Bench (DB) | 8 | 60
Bench (DB) | 6 | 80
Bench (DB) | 4 | 90
Bench (DB) | 2 | 95


I feel like there is a way to keep this as one query, and just use some type of foreach in the PHP to do this...but I have been unable to get this. Any suggestions?

Answer

if the query result is sorted by the ExerciseName I would do it like this

this your your loop , I will just add couple of lines

$lastExercise = ""; // my edit
while($workoutrowridge = $workoutresultridge->fetch_assoc()) {
    if($workoutrowridge['ExerciseName'] != $lastExercise ){
    $workoutoutputridge .= "<tr><td>Exercise</td><td>Reps</td><td>Weight</td></tr>";
 }
    $lastExercise = $workoutrowridge['ExerciseName'];

//the rest is your original code
    $workoutoutputridge .= '<tr>';
         $workoutoutputridge .= '<td>'.$workoutrowridge['ExerciseName'].'</td>';
         $workoutoutputridge .= '<td>'.$workoutrowridge['Reps'].'</td>';
         $workoutoutputridge .= '<td>'.$workoutrowridge['Weight'].'</td>';               
    $workoutoutputridge .= '</tr>';
}