Should mysqli update queries return a result?

Question

I'm updating my PHP code from mysql to mysqli but I can't seem to find the answer to this question: Do mysqli update queries return a result?

With mysql, I could do

$result = mysql_query("UPDATE `data` SET `field1` = 1 WHERE `key` = '$mykey');

and $result would be true even though the query doesn't return any rows.

Now, though, in the mysqli code, I have something like this (error-handling removed for clarity):

$stmt = $mysqli->prepare("UPDATE `data` SET `field1` = 1 WHERE `key` = (?)")
$stmt->bind_param("s", $mykey);
$stmt->execute();
$result = $stmt->get_result();

and $result is false.

For the record, the query is valid (ignore any typos I may have made transcribing it into stackoverflow) and field1 is correctly updated in the database as expected. Also, get_result() works fine for select queries, so it's not a matter of get_result() not being available.

Basically, I just want to know if this changed behaviour is expected or if I should keep trying to find some bug somewhere.

Answer 1

Prepared statement is executed with

 $stmt->execute();

And execute() returns TRUE on success or FALSE on failure.

Because UPDATE, DELETE, INSERT don't yield any resultset, there is no need to use get_result(). If you need to know the total number of affected rows, you can do that by using the mysqli_stmt_affected_rows() function.

Therefore your code might look like this

$stmt = $mysqli->prepare("UPDATE `data` SET `field1` = 1 WHERE `key` = (?)")
$stmt->bind_param("s", $mykey);
$stmt->execute();
//You can get the number of rows affected by your query
$nrows = $stmt->affected_rows;
if (!$nrows) {
    //Nothing has been updated
}