php - Mysql join get multiple rows at right -

i have 2 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 make query takes field values products table , adds product_data rows have same product id, this:

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

maybe not way store data , should store fields in 1 table since there lot of different product types there lot of empty fields. queries lot simpler because have able sort products color example (by values products_data).

i have tried example lists field names, not values:

select *,        group_concat(product_data.fname) afield product_data left join products on products.id = product_data.pid 

just take @ solution , question, similar. involves firing 2 queries , using pdo::fetch_group. $product row similar one:

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

here version based on hint above:

$db = getconnection(); $sql_products = "select * products"; $stmt = $db->prepare($sql_products); $stmt->execute(); $products = $stmt->fetchall(pdo::fetch_obj); $productids; foreach ($products $product) {     $productids[]=$product->id;  } $productids = implode(',',$productids); $sql_data = "select product_id, field_name, field_value product_data product_id in ($productids)"; $stmt = $db->prepare($sql_data); $stmt->execute(); $products_data = $stmt->fetchall(\pdo::fetch_group|\pdo::fetch_obj); foreach ($products &$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);