14

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.

Swiftheart
  • 443
  • 1
  • 5
  • 17
  • Okay...a comment seems to have disappeared telling me to check the documentation, which does indeed state that get_result "returns a resultset or FALSE on failure." I'd already read that before asking my question, however, and I guess I'm just curious if the documentation is accurate. After $stmt->get_result() returned false, I checked $stmt->errno (which was 0) and $stmt->error (which was blank) and the query seemed to execute successfully (since the database was updated). So where is this error occurring? – Swiftheart Feb 16 '13 at 07:42

2 Answers2

14

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
}
peterm
  • 91,357
  • 15
  • 148
  • 157
  • Yes...I have the line $stmt->execute() in my sample code up there, and in my actual code, I check the return value. It returns true, so that's fine. I'm just curious about whether _$stmt->get_result()_ should return true. – Swiftheart Feb 16 '13 at 07:45
  • See updated answer. You don't need to use `get_result()` if you execute `UPDATE`, `DELETE`, `INSERT` queries because they don't produce any resultset. – peterm Feb 16 '13 at 07:46
  • Great, thanks. I suspected as much, but I wanted some outside confirmation. – Swiftheart Feb 16 '13 at 07:52
6

Non-SELECT queries don't have a "result set", so get_result is meaningless for them. If you want to know whether a modification query (UPDATE, INSERT, or DELETE), use $stmt->affected_rows. This will be 0 or non-zero depending on whether the query did anything.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Sometimes I forget that an UPDATE query will return 0 if it only matches rows that wouldn't actually be modified. In other words if the WHERE clause matches multiple rows, but the UPDATE wouldn't change any existing values, then affected_rows will return 0. > An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records were updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error. – shawncampbell Aug 19 '20 at 23:28