Mysql join get multiple rows at right

Question

I have two tables:

# products (id,name,value,time)

# product_data (id,product_id,field_name,field_value)
(1,1,color,red)
(2,1,size,big)
(3,1,whatever,value)

I want to make a query that takes field values from products table and also adds all product_data rows that have the same product id, like this:

$row = array(
  id => 1,
  name => Gloves,
  value => 15,
  color => red,
  size => big,
  ...
) 

Maybe its not very good way to store all that data and I should store all fields in one table but since there will be a lot of different product types there always would be a lot of empty fields. The queries would be a lot simpler like that because I would have to be able to sort products by color for example (by values from products_data).

I have tried this for example but this only lists field names, not values:

SELECT *,
       group_concat(product_data.fname) AS afield
FROM product_data
LEFT JOIN products ON products.id = product_data.pid

Answer 1

Just take a look at this solution and question, as they are somewhat similar. It involves firing two queries and using PDO::FETCH_GROUP. You will get a $product row similar to this one:

$product = [
  'id' => 1,
  'name' => 'somename',
  ...
  'product_data' => [
    ['field_name'=> 'color', 'value'=> 'red'],
    ['field_name'=> 'size', 'value'=> 'XXL'],
    .
    .
    .
    ['field_name' => 'fabric', 'value' => 'cotton']
  ]
];

Here is my version based on the hint above:

$db = getConnection();
$sql_products = "SELECT * FROM products";
$stmt = $db->prepare($sql_products);
$stmt->execute();
$products = $stmt->fetchAll(PDO::FETCH_OBJ);
$productIds;
foreach ($products as $product) {
    $productIds[]=$product->id; 
}
$productIds = implode(',',$productIds);
$sql_data = "SELECT product_id, field_name, field_value FROM product_data WHERE product_id IN ($productIds)";
$stmt = $db->prepare($sql_data);
$stmt->execute();
$products_data = $stmt->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_OBJ);
foreach ($products as &$product) {
    if(isset($products_data[$product->id])){
        $product->data = $products_data[$product->id];
    } else {
        // no data found, empty array
        $product->data = [];
    }
}
echo json_encode($products);