PHP output calculated SQL value

Question

I am trying to add all the values in a single column from a table and then echo that value

$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
            }
$sql = "SELECT sum(paid) as total_paid from users";
$result = $conn->query($sql);
echo $result;

But I end up getting this error:

Catchable fatal error: Object of class mysqli_result could not be converted to string in

Why and how can I fix?

Mahmoud Gamal · Answer 1 · 2015-10-11T14:48:09.340

4

This because $result returns an object, not a string. You have to loop through it and echo the results. And since it is only one row (because SUM without a group by always returns only one value, therefore only one row), you can do this:

if ($row = $result->fetch_assoc()) {
    echo $row['total_paid'];
}

Or, use fetch_object() directly:

echo $result->fetch_object()->total_paid;

edited Oct 11 '15 at 14:48

answered Oct 11 '15 at 14:38

Mahmoud Gamal

78,257
17
139
164

its nit one row, its multiple rows. it's one column – rhill45 Oct 11 '15 at 14:44
1

@Rhillz `SUM` without `group by` ***always*** returns one and only one row – Mahmoud Gamal Oct 11 '15 at 14:45
@Rhillz Yes, it will return only one value, hence it will be only one row. – Mahmoud Gamal Oct 11 '15 at 14:49
@Rhillz - Have you tried the code? Does it work fine or not?? – Mahmoud Gamal Oct 11 '15 at 14:50
I'm still getting this undefined variable error: $sql = "SELECT sum(paid) as total_paid from users"; $result = $conn->query($sql); echo $result->fetch_object()->total_paid – rhill45 Oct 11 '15 at 15:12

Funk Forty Niner · Accepted Answer · 2015-10-11T15:30:31.363

4

This is too long for a "comment".

You need to loop over the results and use a while loop, rather than echoing the query which is why you're getting the present error.

You also need to make sure that the paid column's type can be used to perform mathematical problems, such as int for instance (for example). If it's varchar, then you will need to cast it to an integer.

References:

Here is a procedural style method:

$sql = "SELECT sum(paid) as total_paid from users";
$result = $conn->query($sql);

while ($row = mysqli_fetch_array($result)) {
    echo "Sum: " . $row['total_paid'];
}

or as an object-oriented method:

while($row = $result->fetch_array()) {
        echo $sum = $row['total_paid'];
        }

Sidenote:

To calculate a row based on a user/id, add a WHERE clause in your query.

I.e.: WHERE id='x'

edited Oct 11 '15 at 15:30

answered Oct 11 '15 at 15:16

Funk Forty Niner

74,450
15
68
141

"such as `int` for instance" made me double take. It makes sense, but comes across funny. Maybe that's just me :) – Martin Oct 11 '15 at 15:25
@Martin `int` is but one of a few types that can be used to perform math in MySQL. There are a few others. Although I could add a link to the MySQL's website for the different types that can perform math. – Funk Forty Niner Oct 11 '15 at 15:27
I was referring to "`int` for instance", I read that as just that, it does makes sense but it made me double read and then laugh at the coincidence that `int` as a value type in MySQL can also in this sentence imply *instance*. – Martin Oct 11 '15 at 15:29
@Martin ah, I see what you mean now. The "for instance" meant "for example". – Funk Forty Niner Oct 11 '15 at 15:31
hah, yeah I know what it meant it was solely the order and shape of the words in the sentence came across as amusing to me. (And now I feel stupid that this all needed over explaining.) haha, anyhow, your answer's fine, I just had an amusing interpretation. * goes back to looking for money under stones , grunting occasionally * – Martin Oct 11 '15 at 15:33
@Martin Word placement can be interpreted differently for sure ;-) I made a slight edit for it, *cheers* – Funk Forty Niner Oct 11 '15 at 15:49

PHP output calculated SQL value

2 Answers2