How to combine two MySQL rows into one and display them in a table using PHP?

I have a table "exercise_results". People put in their results at the beginning and then two months later put in their results to see how much they improved. Their beginning set has the exercise_type_id of "1" and the end set has the exercise_type_id of "2".

I need a way to display this out into a HTML table that looks like this:

a foreach loop, but that's with single rows. I'm having trouble combining two rows into one. I think this may be as simple as some kind of MySQL join? We identify each person by their person_unique_id.

Here are my fields:

id | person_unique_id | person_name | exercise_type_id | mile_running_time | bench_press_weight_lbs | squat_weight_lbs | date_of_exercise_performed

Sample rows:

1 | J123 | John Smith | 1 | 8  | 200 | 300 | 2010-03-20
2 | J123 | John Smith | 2 | 7  | 250 | 400 | 2010-05-20
3 | X584 | Jane Doe   | 1 | 10 | 100 | 200 | 2010-03-20
4 | X584 | Jane Doe   | 2 | 8  | 150 | 220 | 2010-05-20

I've tried a few solutions but I'm lost. Any help would be great. Thanks!

EDIT:

In response to the comment below, I would hope for some data like:

array  0 =>
array
  'Exercise' =>
    array
      'person_unique_id' => string 'J123'
      'person_name' => string 'John Smith'
      'begin_mile_running_time' => string '8'
      'end_mile_running_time' => string '7'
1 =>
array
  'Exercise' =>
    array
      'person_unique_id' => string 'X584'
      'person_name' => string 'Jane Doe'
      'begin_mile_running_time' => string '10'
      'end_mile_running_time' => string '8'

You can use GROUP_CONCAT() to get a two rows result like this:

SELECT person_unique_id, person_name,
       group_concat( mile_running_time ) AS miles,
       group_concat( bench_press_weight_lbs ) AS bench,
       GROUP_CONCAT( squat_weight_lbs ) AS squat
FROM exercise_result
GROUP BY person_unique_id

Your result will be like:

J123  |  John Smith  |  8,7  |  200,250  |  300,400

X584  |  Jane Doe  |  10,8  |  100,150  |  200,220

And then you can use php explode with the result fields to get the results for each type.

Related Articles