php - Extract all value from table product option with a loop or array

Question

I have a simple marketplace. I have table product and table product_option

| id | codice_prodotto  | attributo_chiave  | valore_chiave |
| 1  | 001              | colore            | rosso         |
| 2  | 001              | taglia            | m             |
| 3  | 002              | taglia            | s             |

now I want to select all attributes and values for the product 001 So I have my query in php

$query = "SELECT * from table WHERE codice_prodotto = '001'";

now I want to loop this and extract the key and value

colore = rosso
taglia = m

how can I do this? Whit a while loop is not the right way

while ($riga_risultato = $eseguo_query_prodotto->fetch_assoc()) {

//    print_r($riga_risultato);
echo $riga_risultato['attributo_chiave'];

}

the output is (right)

tagliacolore

but I want to have attribute and value for this product in different variable, so if I have more records there is no problem. Can you help me? Maybe I have to transform the array from the query select? thanks

---

Hi, maybe this could be a solution or there is other simple way?

// query
$query_prodotto = "SELECT * from prodotto_opzioni WHERE codice_prodotto = '001'";
$eseguo_query_prodotto = $connessione->query($query_prodotto) or die ("errore query". $connessione->error);

//estraggo i valori
while ($riga_risultato = $eseguo_query_prodotto->fetch_assoc()) {

    // populate multidimensional array with all values I want
    $js_arr[] = [
        'attributo_chiave' => $riga_risultato['attributo_chiave'],
        'valore_chiave' => $riga_risultato['valore_chiave']
        
    ];
}

//extract array
foreach ($js_arr as $pette){
    echo  $pette['attributo_chiave'].'='.$pette['valore_chiave'].'<br>';

}

Answer 1

Your table is basically a denormalized key value store. One option here would be to use a pivot query with aggregation to turn out specific keys and values, e.g.

SELECT
    codice_prodotto,
    MAX(CASE WHEN attributo_chiave = 'colore' THEN valore_chiave END) AS colore,
    MAX(CASE WHEN attributo_chiave = 'taglia' THEN valore_chiave END) AS taglia,
    ...   -- other keys here
FROM yourTable
WHERE
    codice_prodotto = '001'
GROUP BY
    codice_prodotto;