How can this array be created in PHP?

Question

Attributes DB:

   cod_atr=1 value=Color 
   cod_atr=2 value=Size

Values of each Attribute DB:

   cod_atr_val=1, cod_atr=1, value='Blue'
   cod_atr_val=2, cod_atr=1, value='Green'
   cod_atr_val=3, cod_atr=2, value='S'
   cod_atr_val=4, cod_atr=2, value='M'
   cod_atr_val=5, cod_atr=2, value='L'

Array result I am looking for

$attr[0] = array('attr' => 'Color', 'values' => array('Blue', 'Green') ) ;
$attr[1] = array('attr' => 'Size', 'values' => array('S','M','L') ) ;

I tried the following but I am not getting it quite right

$rs_attr = $DB->Execute(SQL);
while ($arrayattr = $rs_attr->FetchRow()){

   $rs_attr_values = $DB->Execute(SQL);
   while ($arrayattr_values = $rs_attr_values->FetchRow()) {

   }

}

Any ideas? Thanks

Answer 1

Use the SQL:

SELECT a.value AS attr, GROUP_CONCAT(v.value) AS `values`
FROM Attributes a
JOIN Values_of_Attributes v ON a.cod_atr = v.cod_atr
GROUP BY attr

Then your loop will be:

$attr = array();
while ($row = $rs_attr->FetchRow()) {
    $row['values'] = explode(',', $row['values']);
    $attr[] = $row;
}