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

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!


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'


Can you provide an example of your expected output, based on the sample data?

Written by OMG Ponies

Sure. See my edit. Edit: I'm having trouble formatting the array nicely.

Written by Michael

Is the database fixed? I mean, could you change the tables?

Written by ckuetbach

Not really. There is already a lot of data in the tables being used in a production application.

Written by Michael

You can do this with the query by grouping by id and selecting MAX(IF(exercise_type_id=1,mile_running_time,'')) AS 'Start', MAX(IF(exercise_type_id=2,mile_running_time,'')) AS 'End' etc... can be a bit challenging but I just had to do this sort of rollup.

Written by methodin

Accepted Answer

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.

Written by nightS
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki