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?

---

I'm not sure if you can return subqueries like this. A solution would be to use GROUP_CONCAT in your subsquery to concatinate all values to a string. Then, in the result, you can split this string into an array using explode.

update : Subquery can only match 1 row
source : http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html

---

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.

---

I think the 'values' attribute will always be one variable. Think pure SQL, regardless of PHP - had you run a query like that in your sql editor - you would get the values attribute as one column. Had you asked for more than one column to be retrieved, you would have gotten an error.
In general, SQL queries are always flat - one level.
My suggestion - run 2 queries, and put them together in the PHP layer.

---

MySQL can only return a two dimensional result set, it can't return a more complex data structure. You will have to make two separated queries and join a result in your application.

Example in PHP:

<?php

//getting a list of products
$result = mysql_query("SELECT `products`.`id`, `product`.`price`
                        FROM (`products`)
                        WHERE`product`.`active` = 1");

$product_ids = array();
$products = array();
while($r = mysql_fetch_assoc($result))
{
    $products[$r['id']] = $r;
    $product_ids[] = $r['id'];
}

$comma_separated_ids = implode(",", $product_ids);

//getting a list of all product values for given products
$result = mysql_query("SELECT `value`, `product`
                        FROM (`productValues`)
                        WHERE `productValues`.`product` IN ('$comma_separated_ids')");

//joining them together
while($r = mysql_fetch_assoc($result))
{
    if (!isset($products[$r['product']]['values']))
        $products[$r['product']]['values'];

    $products[$r['product']]['values'][] = $r['value'];
}

?>

A bit more elegant variant based on GolezTrol's answer:

<?php
$result = mysql_query("SELECT `products`.`id`, GROUP_CONCAT(`productValues`.`value`) AS `values` 
                        FROM `products`, `productValues`
                        WHERE`product`.`active` = 1 AND `productValues`.`product` = `products`.`id`
                        GROUP BY `products`.`id`");

$products = array();
while($r = mysql_fetch_assoc($result))
{
    $products[$r['id']] = $r;
    $products[$r['id']]['values'] = explode(',', $products[$r['id']]['values']);
}
?>

Good luck!

---

This came up as the top result when i searched for "mysql 5.7 subquery into array", so even though the question is old it could help others to mention that since MySQL 5.7.22 a possible solution is to use the aggretate function JSON_ARRAYAGG(), though with JOIN rather than a subquery.

Alteration of the example given by Silver Light:

SELECT `products`.`id`, JSON_ARRAYAGG(`productValues`.`value`) AS `values` 
FROM `products`, `productValues`
WHERE`product`.`active` = 1 AND `productValues`.`product` = `products`.`id`
GROUP BY `products`.`id`"

https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_json-arrayagg