PHP update query in MySQL not updating but SELECT and INSERT works

Question

If I try to update an entry in my database, it doesn't work, as in not update. But when I try to select a value from the same database, that works just fine. I'm not sure why this is so.

Update query:

$id = 1;

try {
    $conn = db();
    $sql = "UPDATE instagram SET token=?, expires=? WHERE id=$id";
    $stmt = mysqli_stmt_init($conn);

    if (!mysqli_stmt_prepare($stmt, $sql)) {
        throw new Exception($conn->error);
    }

    mysqli_stmt_bind_param($stmt, "si", $tokenAccess, $tokenExpires);
    mysqli_stmt_execute($stmt);

    // on error
    if (!mysqli_stmt_execute($stmt)) {
        throw new Exception($conn->error);
    }

    var_dump($stmt);

    $stmt->close();
    $conn->close();
}
catch (Exception $e) {
    print_r($e);
}

var_dump for $stmt returns:

object(mysqli_stmt)#4 (10) { 
        ["affected_rows"]=> int(1) 
        ["insert_id"]=> int(0) 
        ["num_rows"]=> int(0)
        ["param_count"]=> int(2) 
        ["field_count"]=> int(0) 
        ["errno"]=> int(0) 
        ["error"]=> string(0) "" 
        ["error_list"]=> array(0) { } 
        ["sqlstate"]=> string(5) "00000" 
        ["id"]=> int(1) 
}

It's probably in plain sight and I'm missing the obvious. A pointer in the right direction is much appreciated.

UPDATE:

My table setup is as follows:

It has one entry where:

token = ABCdefg123adc........ <-- example string, contains numbers and letters
expires = 60
created_at = 2019-12-01 08:57:26
id = 1.

I tried to access the table via:

$sql = "INSERT INTO instagram (token, expires) VALUES (?, ?)";

This, like SELECT, works.

I also found that if (!mysqli_stmt_execute($stmt)) was sending the entry twice, so I took that out. Still, updating the entry doesn't work.

I have also echoed the values for $tokenAccess and $tokenExpires. The result contains 60 for $tokenExpires and the long-lived access token for $tokenAccess, as expected.

Is there any error? If there's no error, it means the update works.. maybe there are no value to update? — FanoFN, Mar 03 '20 at 08:41
try to debug your `id` maybe your database table doesn't have row with that `id` value — ROOT, Mar 03 '20 at 08:41
Try my answer and check this for update object and procedural https://stackoverflow.com/a/59929664/12232340 And check here for debug https://stackoverflow.com/a/22662582/12232340 — , Mar 03 '20 at 09:16
Are `$tokenAccess` and `$tokenExpires` set? Your code doesn't include those variables. — Havenard, Mar 03 '20 at 17:10
er, how do you *know* it is not updating? the `var_dump` indicates it is updating. If you are updating a table with the same values as already exists, MySQL will detect this and will not carry it out (as it's a waste of time). — Martin, Mar 03 '20 at 17:11
MySQL won’t check if you send same values or not each time, it will just update database again and again until you ask MySQL to check for values And return error on Matches. Which will need a select statement and I don’t see it in question. I commented affected rows in answer, just uncomment it and echo out to see if your rows affected! (It will show you how many rows are updated). You are executing statements twice I dropped it to 1 in answer. — , Mar 04 '20 at 07:26

score 1 · Accepted Answer · answered Mar 23 '20 at 21:09

I was having similar problems when I was learning mysqli years ago. I am guessing that no one told you can enable error reporting for mysqli functions. You can do it by adding this line before you open a connection:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

I can see you are adding a lot of unnecessary code. You don't need all these if statements and you don't need to throw exceptions manually. PHP can do it for you.

You don't need to close the statement and you don't need to close the connection. Never catch exceptions just to print out the error message!

Using OOP is also going to make your code cleaner and easier to spot mistakes.

I can't see what your db() function looks like, but it should hopefully look something like this. Take a look how much simpler this code is:

function db(): \mysqli {
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $mysqli = new \mysqli('localhost', 'username', 'password', 'testdb');
    $mysqli->set_charset('utf8mb4'); // always set the charset
    return $mysqli;
}

$conn = db();

$id = 1;
$sql = "UPDATE instagram SET token=?, expires=? WHERE id=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('sss', $tokenAccess, $tokenExpires, $id);
$stmt->execute();

About your real problem:
There probably was no problem at all. affected_rows indicates that 1 row has been updated.

PHP update query in MySQL not updating but SELECT and INSERT works

1 Answers1