Donjwan Donjwan - 2 months ago 7
MySQL Question

How to show value in php prepared statements

I have the following query and I want to print the "total"
I want to print the total like I print "num_rows"
How can I do that ?

$stmt = $conn->prepare("SELECT SUM( total) AS total FROM money");
$stmt->execute();
$stmt->store_result();
print $stmt->total;


my table structure are like this

id user monthly_pay total
1 Adam 1500 1500
2 Erik 1500 1500
3 Mark 1500 1500
4 Gusta 1500 1500
5 Mike 1500 1500


I want to view the total "75000"

Answer

It's strictly not needed to use prepared statements when not dealing with user-input, but there's no disadvantage using it either (should you expand the query to deal with variables/user-input, the ground-work is already done!). This means for a static query like this, you can just use normal query() functions.

However, using prepared statements like this, you need to bind the result and fetch it. Using $stmt->num_rows is going to trick you, as this will return the amount of rows returned by the query - which will only be 1. It will not return the result.

The snippet below has been modified to use bind_result() and fetch(). This will prepare the query, bind the results to $sum and fetch it.

$stmt = $conn->prepare("SELECT SUM( total) AS total FROM money");
$stmt->execute();
$stmt->bind_result($sum);
$stmt->fetch();
print "The sum is ".$sum;

References

Comments