Industrial Industrial - 4 months ago 14
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` = 'product.id'
) as values
FROM (`products`) WHERE`product`.`active` = 1


The current result looks like this:

Array
(
[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 = product.id
).

What am I doing wrong?

Answer
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;

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'];
}

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.