php MySQL将某些结果分组到一个数组中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14060332/
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
MySQL group certain results into an array
提问by emersonthis
I'm writing a complex MySQL query with multiple LEFT JOINs to combine data from several different tables.
我正在编写一个复杂的 MySQL 查询，其中包含多个LEFT JOINs 以组合来自多个不同表的数据。
$public_query =
"SELECT * FROM `wsat_ib`
LEFT JOIN wp_usermeta ON wsat_ib.user_id = wp_usermeta.user_id
LEFT JOIN wpjb_resume ON wsat_ib.user_id = wpjb_resume.user_id
LEFT JOIN wpjb_field_value ON wpjb_resume.id=wpjb_field_value.job_id
LEFT JOIN wpjb_application ON wpjb_application.user_id = wsat_ib.user_id
WHERE wp_usermeta.meta_key = 'target_employer'
AND wp_usermeta.meta_value = 'public'
AND wpjb_resume.is_active =1
";
My problem: The table wp_usermeta can have multiple rows for the same user_id. For example a user (with user_id = 5) may have more then one row in the table wp_usermeta with the field: meta_key that is 'target_employer'. In case that's not clear, the rows might look like this.
我的问题：表 wp_usermeta 可以有相同 user_id 的多行。例如，用户（user_id = 5）可能在 wp_usermeta 表中有多于一行，字段为：meta_key，即“target_employer”。如果不清楚，行可能看起来像这样。
id user_id meta_key meta_value
1 5 target_employer 13
2 5 target_employer 53
3 79 target_employer 21
My Question: Is there any way that I can return each matching row from wp_usermeta as an array in one of my result objects? So the var_dump()would look something like this:
我的问题：有什么方法可以将 wp_usermeta 中的每个匹配行作为我的结果对象之一中的数组返回？所以var_dump()看起来像这样：
object(stdClass)#2906 (14) {
["user_id"]=>
string(4) "5"
["firstname"]=>
string(6) "Bilbo"
["lastname"]=>
string(3) "Baggins"
...
["target_employer"]=>
array(2) {
[0]=>13,
[1]=>53
}
}
UPDATE: @bpanulla: Here's the real (unsimplified query)...
更新：@bpanulla：这是真实的（未简化的查询）...
"SELECT wsat_ib.user_id, wpjb_resume.firstname, wpjb_resume.lastname, wsat_ib.overall_score, wsat_ib.account_score, wsat_ib.econ_score, wsat_ib.math_score, wsat_ib.logic_score, wsat_ib.fsanaly_score, wsat_ib.corpval_score, wsat_ib.end_time, GROUP_CONCAT(wp_usermeta.meta_value) AS target_employers, wpjb_field_value.value AS school, wpjb_application.job_id
FROM `wsat_ib`
LEFT JOIN wp_usermeta ON wsat_ib.user_id = wp_usermeta.user_id
LEFT JOIN wpjb_resume ON wsat_ib.user_id = wpjb_resume.user_id
LEFT JOIN wpjb_field_value ON wpjb_resume.id=wpjb_field_value.job_id
LEFT JOIN wpjb_application ON wpjb_application.user_id = wsat_ib.user_id AND wpjb_application.field_id=3
WHERE (wp_usermeta.meta_key = 'target_employer'
AND wp_usermeta.meta_value = 'public'
AND wpjb_resume.is_active =1)
GROUP BY user_id
";
回答by bpanulla
I don't think MySQL can return an array to PHP, but it can return a delimited list using the GROUP_CONCAT aggregate function.Try something like:
我不认为 MySQL 可以将数组返回给 PHP，但它可以使用GROUP_CONCAT 聚合函数返回一个分隔列表。尝试类似：
SELECT user_id, firstname, lastname,
GROUP_CONCAT(wp_usermeta.meta_value) as target_employer_id
FROM `wsat_ib`
LEFT JOIN wp_usermeta ON
(wsat_ib.user_id = wp_usermeta.user_id
AND wp_usermeta.meta_key = 'target_employer')
GROUP BY user_id, firstname, lastname
By default you should get a comma-separated list of elements in target_employer_id. You can use the PHP explodefunction to turn that into an array in your PHP logic.
默认情况下，您应该在target_employer_id. 您可以使用 PHPexplode函数将其转换为 PHP 逻辑中的数组。
