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

Question

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:

select sum(tech) from sales where day='$day'; – JYoThI May 09 '17 at 04:02 — JYoThI, May 09 '17 at 04:02
What output do you want here? – Tim Biegeleisen May 09 '17 at 04:03 — Tim Biegeleisen, May 09 '17 at 04:03
So, what isn't working? – Sloan Thrasher May 09 '17 at 04:04 — Sloan Thrasher, May 09 '17 at 04:04
42+0+0+42 = 42 how is it 42 ? @adesgangne ?? – JYoThI May 09 '17 at 04:09 — JYoThI, May 09 '17 at 04:09

score 1 · Accepted Answer · edited Mar 09 '23 at 06:39

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

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

1 Answers1