calculate total value instead of rows using php and mysql

Question

I'm trying to calculate the total number of tokens collected instead of the number of records in the mysql db. Although I have already put the SUM function in the query but it still counts the no. of rows.

<?php 
   $conn = mysqli_connect(server, dbuser, dbpw, db);

   $query = " SELECT SUM(nooftokensused) FROM bidding WHERE biddedon >= DATE_FORMAT(CURRENT_DATE(), '%Y-01-01') GROUP BY biddingid";
   $query_run = mysqli_query($conn, $query);

   $row = mysqli_num_rows($query_run); 

   echo '<h1>'.$row.'</h1>';
?>

Can you please show us some example data and the result that you see? — Arno, Feb 01 '20 at 06:33
Why would you think there is a difference between total rows and SUM(nooftokensused) ? — P.Salmon, Feb 01 '20 at 06:42
It's strange that you don't care which total belongs to which biddingid — Strawberry, Feb 01 '20 at 07:41
basically im trying to calculate the total nooftokensused for each month. So for each row the nooftokens varies and i want to sum up the total value instead of the rows — maddybobby, Feb 01 '20 at 08:47

score 0 · Answer 1 · answered Feb 01 '20 at 11:23

I have added comments with my code. Also, you are using mysqli_connect(server, dbuser, dbpw, db) , you have not declared them as variables with $ symbol.

<?php
    //Make connection with DB
    $con = mysqli_connect($server, $dbuser, $dbpw, $db);
    if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      exit();
    }

    $sql = "SELECT SUM(nooftokensused) as summ FROM bidding WHERE biddedon >= DATE_FORMAT(CURRENT_DATE(), '%Y-01-01') GROUP BY biddingid";
    $result = mysqli_query($con, $sql);

    if(mysqli_num_rows($result)>0)
    {
        // Associative array
        $row = mysqli_fetch_assoc($result);
        echo $row["summ"];
    }
    else
    {
        echo 0;
    }
    // Free result set
    mysqli_free_result($result);
    mysqli_close($con);
    ?>

score -1 · Answer 2 · answered Feb 01 '20 at 07:26

Use mysqli_fetch_assoc() instead of mysqli_num_rows().

<?php 
                $conn = mysqli_connect(server, dbuser, dbpw, db);

                $query = " SELECT SUM(value_sum) AS nooftokensused FROM bidding WHERE biddedon >= DATE_FORMAT(CURRENT_DATE(), '%Y-01-01') GROUP BY biddingid";
                $query_run = mysqli_query($conn, $query);

                $row = mysqli_fetch_assoc($query_run); 

                echo '<h1>'.$row['nooftokensused'].'</h1>';
 ?>

this error msg pops out: PHP Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in — maddybobby, Feb 01 '20 at 08:44

calculate total value instead of rows using php and mysql

2 Answers2