Search code examples
phpmysqlsqldatabasemysqli

How to get the sum in a column using MySQL and PHP

I've been searching for the better part of five days (no joke, don't want to bother you guys for nothing), but I can't seem to be getting this right. I want to display the total amount of a column (tech) on the screen using a function in PHP. Don't worry, the $day part works in other functions. I don't think it is part of the problem. Thank you in advance.

function calc_tech($day){

    include("connect.php"); // include database connection

    $res = $mysqli -> query(" SELECT * FROM sales WHERE day = $day ") or die($mysqli->error); // Fetch data from the table

    while($val = $res -> fetch_array()){
        $tech_total = $val[tech] += $val[tech];
    }

    echo $tech_total; // Echo the result
    //echo "42";

    $res -> free(); // Free the query results
    $mysqli -> close(); // Close mysqli object

}

My database table looks like this:

enter image description here

Solution

  • You need to make the SUM() the column in the query :

    function calc_tech($mysqli,$day){
    //Prepare your query first
    $stmt = $mysqli->prepare("SELECT SUM(tech) FROM sales WHERE day=?"); 
    // then bind the parameter
    $stmt->bind_param("s", $day);
    // then execute the query
    $stmt->execute();
    $result = $stmt->get_result();
    // fetch and return the result
    return $result->fetch_row()[0];
}

include("connect.php"); // include database connection
echo calc_tech($mysqli,$day); // get the result