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:
Normally I do this using 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
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.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki