Obviously, mysqli_stmt::$affected_rows is not available unless prepared statements are being used. But when prepared statements are being used, what's the difference between mysqli::$affected_rows and mysqli_stmt::$affected_rows?
I have the same question in regards to mysqli::$insert_id vs mysqli_stmt::$insert_id.
I'm trying to decide if I should be using one in favour of the other.
I've read the PHP manual entries for these properties. I've done some testing (PHP 5.3.17) using a single execute and using multiple executes. I don't see a difference.
So I am wondering if maybe there is some difference under certain circumstances (or certain versions). If they are exactly the same, why have both?
Returns the total number of rows changed, deleted, or inserted by the last executed statement
Gets the number of affected rows in a previous MySQL operation
So, if the mysqli_stmt object was the last executed statement, both queries should give the same result.
I think the only reason to keep mysqli:$affected_rows is mysqli::query and mysqli::multi_query, because they both don't use prepared statements. And the only reason to keep mysqli_stmt:$affected_rows is OOP: to encapsulate query related information in statement object.
I just discovered a difference between mysqli_stmt::$affected_rows and mysqli::$affected_rows that I didn't expect.
I assumed that mysqli::$affected_rows could be called after closing the statement because I expected it to report based on the last query executed on the connection. I didn't think it would matter if the statement was closed. However, it does seem to make a difference.
This code:
$db_err_msg = 'Database Error: Failed to update profile';
$sql = "UPDATE tblProfiles SET lngPhoneNumber = ? WHERE lngProfileId = ?";
$stmt = $mysqli->prepare($sql) or output_error($db_err_msg);
$phone = 5555555555;
$id = 10773;
$stmt->bind_param('ii', $phone, $id) or output_error($db_err_msg);
$stmt->execute() or output_error($db_err_msg);
if ($mysqli->warning_count) {
$warnings = $mysqli->get_warnings();
do {
trigger_error('Database Warning (' . $warnings->errno . '): '
. $warnings->message, E_USER_WARNING);
} while ( $warnings->next() );
}
else {
echo 'no warnings', "
";
}
echo 'Before $stmt->close()', "
";
echo '$mysqli->affected_rows is ', $mysqli->affected_rows, "
";
echo '$stmt->affected_rows is ', $stmt->affected_rows, "
";
echo '$mysqli->affected_rows is ', $mysqli->affected_rows, "
";
$stmt->close();
echo "
", 'After $stmt->close()', "
";
echo '$mysqli->affected_rows is ', $mysqli->affected_rows, "
";
produces this output:
no warnings
Before $stmt->close()
$mysqli->affected_rows is 1
$stmt->affected_rows is 1
$mysqli->affected_rows is 1After $stmt->close()
$mysqli->affected_rows is -1
Note how the final value is negative 1.
The PHP manual for mysqli::$affected_rows says:
-1 indicates that the query returned an error
The query updated the record as expected and did not return an error or warning. Yet this implies there was an error. I'm not sure if this is a bug or not, but it was certainly not what I expected. Regardless of which one you use, clearly the safest approach is to check it right after the execute statement.