MySQL的:子查询数组? [英] mySQL: Subquery to array?
问题描述
我正在处理一个稍微复杂的(至少对我来说)包含子查询的mySQL查询,说实话这不太好.
I am working on a slight complex (at least for me) mySQL query containing a subquery and it isn't going to well to be honest.
SELECT `products`.`id`, `product`.`price`,
( SELECT `value` FROM (`productValues`)
WHERE `productValues`.`product` = 'product.id'
) as values
FROM (`products`) WHERE`product`.`active` = 1
当前结果如下:
Array
(
[0] => Array
(
[id] => 1
[active] => 1
[price] => 1000
[values] =>
)
)
我想要的是values元素也要成为一个数组,其中Values表中的所有元素都匹配(WHERE productValues.product = product.id
).
What I want is the values element to also become an array with all elements in the Values table which matches (WHERE productValues.product = product.id
).
我在做什么错了?
推荐答案
SELECT p.id, p.price, pv.`value`
FROM products p
JOIN productValues pv
ON p.product_id=pv.product
WHERE p.active = 1
ORDER BY p.id;
为每个pv.value给出一个表,其中每一行(BTW,建议不要使用诸如"value"之类的保留字).通过p.id对输出进行排序可确保特定产品的所有行都在一起.因此,在应用程序层中,遍历您的行,每次p.id更改时都会更改产品.
gives a table with one row for each pv.value (BTW, using reserved words like 'value' is not recommended). Ordering the output by p.id ensures that all the rows for a particular product are together. So, in the application layer, loop through your rows, changing product each time the p.id changes.
$old_id=NULL;
$datastructure=array();
while($arr=$result->fetch_assoc()){
if($arr['id']!=$old_id){
$datastructure[$arr['id']][price]=$arr['price'];
$old_id=$arr['id'];
}
$datastructure[$arr['id']]['values'][]=$arr['value'];
}
我提供的结构可能比您要求的结构更灵活,因为它允许您通过array键访问特定的产品.
The structure I've given is perhaps more flexible than the one you asked for in that it allows you to access a particular product through the array key.
这篇关于MySQL的:子查询数组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!