如何将两个MySQL行合并为一个并使用PHP在表中显示它们？

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

我有一张桌子“exercise_results”。人们在开始时投入他们的结果，然后在两个月之后投入他们的结果，看看他们有多少改进。它们的开始集的exercise_type_id为“1”，而结束集的exercise_type_id为“2”。

I need a way to display this out into a HTML table that looks like this:

我需要一种方法将其显示到HTML表中，如下所示：

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.

一个foreach循环，但这是单行。我在将两行合并为一行时遇到了麻烦。我想这可能就像某种MySQL加入一样简单？我们通过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'

4 个解决方案

#1

You can use GROUP_CONCAT() to get a two rows result like this:

您可以使用GROUP_CONCAT（）来获得两行结果，如下所示：

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.

然后你可以使用php explode和结果字段来获得每种类型的结果。

#2

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?).

提取整个表格，或者对您感兴趣的行，对人员ID进行排序，将每行的人员ID与下一行进行比较，以查看是否有针对HTML表格中所有列打印的结果。如果没有，跳到下一个并将字段留空（或其他一些解决方案，可能会忽略那些没有填写这两个字段的人？）。

My PHP skills are limited, so no code example.

我的PHP技能有限，所以没有代码示例。

#3

$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);

#4

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):

如果您在person_unique_id和exercise_type_id上订购，则可以执行此操作。如果每个人都有两行，则可以省略if（仅使用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
}

#1