MySQLi SUM how to handle no results found

Question

I made a question about this before and added a bounty, but I put the wrong code I just realized.

$getgames = $db->prepare("SELECT `id`, `name`, `picture` FROM `games` WHERE `status` = '1' ORDER BY `id` ASC");
$getgames->execute();
    $getgames->bind_result($gid, $name, $picture);
while($getgames->fetch()){


$getgames->free_result();
$gettokens = $db->prepare("SELECT SUM(`amount`) AS `tokenamount` FROM `tokens` WHERE `game` = ? AND `user` = ?");

$gettokens->bind_param('ii', $gid, $sesid);
$gettokens->execute();
$gettokens->bind_result($tokenamount);
$gettokens->fetch();

This code returns the first row correctly, but then for the second it doesn't show it and returns the following error.

Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Attempt to read a row while there is no result set associated with the statement'

The line that it refers to is the one with $getgames->execute();.

Is it an error with the free_result? I'm still learning MySQLi so I have lots of problems with this.

UPDATE:

When I remove $getgames->free_result(); then I get a new error of Commands out of sync; you can't run this command now. On the line $getgames->bind_result($gid, $name, $picture);

And you are 100% that the line this is happening on is `$getgames->execute` ? — Aziz Saleh, Jun 01 '14 at 19:01
After removing that other free_result code, the line of the error changes to `$getgames->bind_result($gid, $name, $picture);`. — , Jun 01 '14 at 19:04
http://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now — Deadooshka, Jun 01 '14 at 19:08
@Deadooshka Thank you so much. That, along with Your Common Sense's answer got it working. — , Jun 01 '14 at 19:11

score 1 · Accepted Answer · edited Jun 15 '14 at 01:34

SELECT query will ALWAYS return a result set. This is how the thing works. It can be empty, but it always present, unless there was error during execution. But if there was any, the error would be different, produced by prepare or execute call. Even select from empty table will return a result set all right.

Which renders the above error message for the present code impossible.

Which means this error is caused by some other code. And here comes the matter of properly asked questions. A properly asked question always contain full error message, including file name, line number and stack trace. As well as a remark from the OP, pointing at the line in the posted code, mentioned in the error message. And if the OP were following this simple rule, they will find that error is caused by some other code.

As of the edited code, change it like this

$getgames->execute();
$getgames->store_result();
$getgames->bind_result($gid, $name, $picture);
while($getgames->fetch()){
    $gettokens = $db->prepare( ...

or better make it in one query

SELECT `id`, `name`, `picture`,  SUM(`amount`) AS `tokenamount` 
FROM `games` g JOIN tokens t ON t.game = g.id
WHERE `status` = '1' AND `user` = ?

Okay so the original version you posted did not work, but I moved the `store_result` line into the `while`, and it worked. Why did it work that way instead of the previous? Either way, thanks for the help. Have some rep. — , Jun 01 '14 at 19:09

MySQLi SUM how to handle no results found

1 Answers1