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

PHP
1

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

2

A clean way would be to build a new array format that suits what you want.


while ($row = fetch from db)
{
	$result[$row['person_unique_id']]['person_name'] = $row['person_name'];
	$result[$row['person_unique_id']]['mile_run_time'][$row['exercise_type_id']] = $row['mile_running_time'];
	$result[$row['person_unique_id']]['bench_press_weight_lbs'][$row['exercise_type_id']] = $row['bench_press_weight_lbs'];
	$result[$row['person_unique_id']]['squat_weight_lbs'][$row['exercise_type_id']] = $row['squat_weight_lbs'];
	$result[$row['person_unique_id']]['date_of_exercise_performed'][$row['exercise_type_id']] = $row['date_of_exercise_performed'];
}


#now to use the array, you would just use a foreach loop,

foreach ($result as $pid=>$val)
{
	echo $val['person_name']; 
    echo $val['mile_run_time'][1]; # before
    echo $val['mile_run_time'][2];  # end
}

#Just format it within the foreach loop and you're golden
3

Thanks for your response. I received another idea to use a Mysql query like so:

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

This works for me. Then I can use PHP explode to get the results for each type.

I think I’ll give your idea a shot too, since it looks nice and may work even easier for me. Thank you again.

Edit: I seem to be generating an infinite loop when trying your code. Maybe the problem is in my query? What should go in the “fetch from db” section of your example, line 1?

4

The query given works if there’s only two entries per unique person in the table. If at any future time more entries are added, the query will then generate more values than you’re expecting.

while ($row = fetch from db) will be whatever you use to pull the result set out.

if you’re using the native mysql_ functions, you’d do

while ($row = mysql_fetch_assoc($rs)), assuming $rs holds the result set reference