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>';

}

It is a very bad idea to use `die($connessione->error);` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Jan 06 '21 at 10:58

score 0 · Answer 1 · answered Jan 06 '21 at 02:14

0

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;

answered Jan 06 '21 at 02:14

Tim Biegeleisen

502,043
27
286
360

Hi, thanks but if I add more values, I cannot everytime modify the query. I want a unique query with extract all values of product – pette Jan 06 '21 at 02:22
You can't do that without using dynamic SQL. Perhaps the best which would be easily accessible would be to add however many `CASE` expressions for all possible values. – Tim Biegeleisen Jan 06 '21 at 02:23
So, with all data extracted I can loop as an array in php script – pette Jan 06 '21 at 02:24

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

1 Answers1