Industrial Industrial - 6 months ago 41
MySQL Question

mySQL: Subquery to array?

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` = ''
) as values
FROM (`products`) WHERE`product`.`active` = 1

The current result looks like this:

[0] => Array
[id] => 1
[active] => 1
[price] => 1000
[values] =>

What I want is the values element to also become an array with all elements in the Values table which matches (
WHERE productValues.product =

What am I doing wrong?

SELECT, p.price, pv.`value`
FROM products p
  JOIN  productValues pv
  ON p.product_id=pv.product

gives a table with one row for each pv.value (BTW, using reserved words like 'value' is not recommended). Ordering the output by 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 changes.


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.