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

I h开发者_StackOverflowave 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.

Extract the whole table, or whichever rows are interesting to you, sort on person id, compare person id of each row with the next to see if there is a result to print for all columns in your HTML table. If not, jump to the next and leave the fields blank(or some other solution, maybe ignore persons who have not filled in both fields?).

My PHP skills are limited, so no code example.

$query = mysql_query("select ...your data");

 while ($row = mysql_fetch_assoc ($query) ) {
    if ($row['exercise_type_id']==1)  
      $exe1[]=$row;
   else  
      $exe2[]=$row;


 }

 print_r($exe1);
 print_r($exe2);

from what I've understood

edit:

try this

$query = mysql_query("select ...your data");

 while ($row = mysql_fetch_assoc ($query) ) {

   $rows[]=array('Exercise'=>$row);


 }

 print_r($rows);

If you are ordering on person_unique_id then exercise_type_id, you can do this. If you have two rows for everyone, you can leave out the if (only use the else):

for( $i = 0; $i < count($exercise_results); $i++ )
{
  $first = $exercise_results[$i];
  if( !isset($exercise_results[$i+1])
      || $first['person_unique_id'] != $exercise_results[$i+1]['person_unique_id' ) {
    $second = array(
      'person_name'=>$other['person_name'],
      'mile_running_time' => null // fill in the rest with defaults (like null)
    );
  } else {
    $second = $exercise_results[$i+1];
    $i++; // skip ahead one, since you've already used the second result.
  }
  // perform your normal printing, but use $beginning and $end to get the respective results
}