TheMerovingian TheMerovingian - 3 months ago 8
MySQL Question

PHP - nested recursive SQL query doesn't retrieve values

What i'm trying to do is to print a table and in each row print, for each different value in the DB, the value, the number of the rows in the DB with that value and the sum of the values. Sorry this is not well explayned, more easy go straight to the code

$query_voucher1="SELECT * FROM voucher WHERE consegnato= 0 AND IDprestazione=0 ORDER BY codice";
$risultato_query_voucher1=@mysql_query($query_voucher1);
echo "<table class='table table-striped' style='margin-top:70px; width: 60%;'>
<caption>Riassunto Voucher Liberi</caption>
<tr>
<th>Codice di controllo</th>
<th>Quantità (solo liberi)</th>
<th>Data Emissione</th>
<th>Valore Pacchetto (solo liberi)</th>
</tr>";

$prevcodice= NULL;
$curcodice= 10;
while ($row_voucher1=mysql_fetch_row($risultato_query_voucher1)) {
if ($curcodice!=$prevcodice){
$array_temp_query_value=array();
if ($modifica==true){
$temp_query_value=mysql_query("SELECT valorelordo FROM voucher WHERE codice='$row_voucher1[2]' AND consegnato=0 ");
}else{
$temp_query_value=mysql_query("SELECT valorelordo FROM voucher WHERE codice='$row_voucher1[2]' AND consegnato=0 AND IDprestazione=0");
}
while(mysql_fetch_row($temp_query_value)){
array_push($array_temp_query_value, $temp_query_value[0]);
}
echo "<tr>
<td>" . $row_voucher1[2] . "</td>
<td>" . count($array_temp_query_value) . "</td>
<td>" . print_r($array_temp_query_value). "</td>
</tr>";
$prevcodice=$row_voucher1[2];

}
$curcodice=$row_voucher1[2];
}

echo "</table>";


So let's say i have a DB with the field
codice
and the many values in this fields are repeated. What i wanna do is take print once the value, once the number of rows where
codice
is the same and once the sum of the values where
codice
is the same.

Not good this explaination either so here the JSFiddle.

The
$array_temp_query_value
returns the correct number of values so i can count them to fill the second field but all the values in the array are empty so i can't sum them. The query looks good so i have really no idea.

Answer

You don't need to calculate this value yourself, just use count(), sum() and group by, e.g.

select codice, count(*), sum(codice)
from voucher
group by codice

This gives you all existing values in your table, the number of rows of each value and the sums.


Upfront, don't use mysql_* functions, because they are deprecated and removed in PHP 7.

With mysqli, taken from the online manual of mysqli_fetch_row, something along these lines (untested)

$result = mysqli_query($conn, 'select codice, count(*), sum(codice) from voucher group by codice');
while ($row = mysqli_fetch_row($result)) {
    echo '<tr><td>' . $row[0] . '</td><td>' . $row[1] . '</td><td>' . $row[2] . '</td></tr>';
}