How can I fetch a data while calculating it at first?

Question

$result = mysql_query("SELECT Country, Quantity FROM `table 1`");

$rows = array();
while($r = mysql_fetch_array($result)) {
$row[0] = $r[0];
$row[1] = $r[1];
array_push($rows,$row);
}

print json_encode($rows, JSON_NUMERIC_CHECK);

How can I fetch a data while calculating it at first? I need to add below code to the above code and display in graph.

while($row = mysql_fetch_array($result1)){
            echo "Total Quantity = ". $row['SUM(quantity)'];
            echo "<br />";

`$result` and `$result1` belong to the same or different queries? If the later, how to match the results? — Sirko, Jul 17 '16 at 14:32
*and display in a graph*: that is a whole other matter than the question title mentions. What have you attempted in order to produce your graph? — trincot, Jul 17 '16 at 14:35
I don't get what the OP is trying to do. Why not just `echo` what you need within the first `while` loop? — BeetleJuice, Jul 17 '16 at 14:36

score 1 · Answer 1 · answered Jul 17 '16 at 14:46

You can simply get your total by SUM in mysql

$result = mysql_query("SELECT SUM(column_name) AS TotalFROM `table 1`");

or you can sum it in your while loop

    $total = '';    
    $rows = array();
    while($r = mysql_fetch_array($result)) {
    $row[0] = $r[0];
    $row[1] = $r[1];
    $total = $r[1] + $total;
    array_push($rows,$row);
    }
    $rows[]['total'] = $total;
    echo "<pre>";print_r($rows );

Also use PDO or mysqli prepared statements mysql is depreciated and completely removed in PHP7

score 0 · Answer 2 · edited May 23 '17 at 10:29

You can add a total row by keeping a running total in your loop, and after the loop you can add that total to the array:

$total = 0;
while($r = mysql_fetch_array($result)) {
    $total += $r[1];
    $rows[] = $r;
}
$rows[] = ['Total', $total];

Note that:

you don't need to copy each value one by one (like $row[0] = $r[0];), you can just assign the whole array;
adding to an array can be done with array_push, but $rows[] = also works, and probably faster.
mysql_ functions have been deprecated for a long time now, and are no longer supported in the current version of PHP (7.x). Please use mysqli_ functions or PDO instead.

How can I fetch a data while calculating it at first?

2 Answers2