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
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!
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.