MySQL echo groups with unique value PHP

Question

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 1

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

this is 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>';
}

Answer 2

you could do something like this :

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

    $currentExercise = $workoutrowridge['ExerciseName'];
}
echo '</table>';