2012-12-15 19:26 阅读 63

mysqli :: $ affected_rows和mysqli_stmt :: $ affected_rows之间有什么区别?

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • 已采纳
    dougu1952 dougu1952 2012-12-15 19:35


    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.

    点赞 5 评论 复制链接分享
  • doushenxu7294 doushenxu7294 2012-12-15 19:40

    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.

    点赞 7 评论 复制链接分享
  • douji3623 douji3623 2012-12-16 11:30

    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, "
    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 1

    After $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.

    点赞 4 评论 复制链接分享