mySQLi_affected_rows check not working, or is it...?

Question

(Sorry, I don't really know what I am doing.)

I have this Unity game in an iframe on Facebook calling a php file in the same directory, and that much is working. What I want it to do is update the player record if it is there and make one if it isn't.

This script runs but it always returns a "not here" and when I check the database, it is in fact creating the records each time, identical but for the datetime field. So I don't understand why affected_rows is never coming back as "1".

<?php
$db = @new mysqli('••.•••.•••.••', '•••••••••••', '••••••••','•••••••••••');
if ($db->connect_errno) 
{
echo("Connect failed "+mysqli_connect_error());
exit();
}
$inIP = $_POST["ip"];
$playerIP = mysqli_real_escape_string($db, $inIP);

$inUN = $_POST["un"];
$playerUN = mysqli_real_escape_string($db, $inUN);

$query = "UPDATE lobby SET whens=NOW(), wherefores='$playerIP', whys=0 WHERE whos='$playerUN'";
mysqli_query($db, $query);
if (mysqli_affected_rows($db) > 0)
    {
    echo "here";
    }
else
    {
    $query2 = "INSERT INTO lobby (whens,whos,wherefores,whys) values (NOW(),'$playerUN','$playerIP',0)";
    mysqli_query($db, $query2);
    echo "not here";
    }   

if ($db)
    {       
    $db->close();
    }
?>

why not an `INSERT INTO ... ON DUPLICATE KEY UPDATE ...` instead? One single query instead of (potentially) two? And note: PHP's concatenation operator is `.`. This is outright wrong: `echo ('connect failed' + mysqli_connect_error())`. You are also assuming your queries worked, and are suppressing erors. **NEVER** assume success. — Marc B, Mar 31 '13 at 14:54
Variables are case-sensitive. Please enable error_reporting for once. And when using mysqli already, consider prepared statements. — mario, Mar 31 '13 at 14:55
Your script is vulnerable to SQL injection, if `$playerIP` and `$playerip` is just a typo. — Gumbo, Mar 31 '13 at 15:23
The answer to any "why" question is in my first line; I don't know what I'm doing. It's for my first game with multiplayer features, I've only written 4 php scripts ever, and there seems to be no consensus on the right way to do even the simplest little thing in SQL, though lots of opinions on which wrong way is wronger. — blerg, Mar 31 '13 at 15:28

n1te · Answer 1 · 2013-03-31T15:17:38.593

0

You have a typo:

wherefores=$playerip

it should be

wherefores=$playerIP

because of that

mysqli_affected_rows($db)

returns

-1

edited Mar 31 '13 at 15:17

answered Mar 31 '13 at 15:05

n1te

945
3
11
20

Thanks, but I saw that after I posted the question and fixed it; apparently that wasn't the problem. – blerg Mar 31 '13 at 15:21
change `mysqli_query($db, $query);` to `mysqli_query($db, $query) OR triger_error(mysqli_error($db));` and see what the problem is – n1te Mar 31 '13 at 15:27
It was a duplicate record error. I took Marc B's suggestion to use INSERT INTO ... ON DUPLICATE KEY UPDATE ... (don't know why he didn't post that as an answer) and the error now is a syntax error. – blerg Mar 31 '13 at 16:08

score 0 · Answer 2 · answered Jun 02 '14 at 20:31

Sounds like you're experiencing the same problem as me, especially if you are running your code through a debugger. I've investigated the issue with Netbeans and Xdebug and it seems this is a bug in the MySQLi extension itself. An according bug report has been made. In the meantime you can instead use another expression, e.g.:

if (mysqli_sqlstate($dbc) == 00000) {
   //your code
}

to continue debugging your remaining code.

mySQLi_affected_rows check not working, or is it...?

2 Answers2