如何将两个 MySQL 行合并为一个并使用 PHP 在表中显示它们？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4091064/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I combine two MySQL rows into one and display them in a table using PHP?
提问by Michael
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”。人们在开始时输入他们的结果，然后两个月后输入他们的结果，看看他们有多大进步。它们的开始集的练习类型 ID 为“1”，结束集的练习类型 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'
回答by layalk
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 爆炸来获取每种类型的结果。
回答by vertazzar
$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);
回答by vichle
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 技能有限，所以没有代码示例。
回答by theazureshadow
If you are ordering on person_unique_idthen 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
}
