Execute Success but num_rows return 0 [PHP-MySQL]

Question

The problem I just got is,

the $update_stmt->execute() is ok, and data in database already update

but, $update_resultrow = $update_stmt->num_rows; return 0 ?

I tried to copy MySQL command to run in query and it also worked well, like this:

UPDATE ACCOUNT_EMPLOYEE SET NAME = 'cccccc' WHERE ID = 1

Problem's Code here:

$update_sql = "UPDATE ACCOUNT_EMPLOYEE SET NAME = ? WHERE ID = ?";
if ($update_stmt = $conn -> prepare($update_sql)) {
    if ($update_stmt->bind_param("si",
        $newname,
        $acc_id
    )
    ) {
        if ($update_stmt->execute()) {
            // must declare here to be able to get num_rows
            $update_stmt->store_result();
            $update_resultrow = $update_stmt->num_rows;
            if ($update_resultrow == 0) {
                echo $error_forgot_noresult . '????' . $acc_id ;
                $update_stmt->close();
                $conn->close();
                exit();
            }
        }
    }
}

replace `$update_stmt->num_rows;` with `$update_stmt->rowCount()` — Umair Ayub, Jun 24 '15 at 04:50
`$update_resultrow = $update_stmt->num_rows;` what are you trying to achieve here, if the execute was successful? if so, you need to use `affected_rows`, or read the manual http://php.net/manual/en/mysqli-result.num-rows.php i.e.: from the manual `$rows = $query->num_rows;` - and for affected_rows http://php.net/manual/en/mysqli.affected-rows.php — Funk Forty Niner, Jun 24 '15 at 05:01
Yes, Fred -ii-, I never noticed that it has ->affected_rows. please post as answer, and I will marked it here — Sruit A.Suk, Jun 24 '15 at 05:04

Funk Forty Niner · Accepted Answer · 2015-06-24T05:11:38.917

Yes, Fred -ii-, I never noticed that it has ->affected_rows. please post as answer, and I will marked it here

As per OP's request.

Seeing that the goal here is to test if the query was indeed successful, you need to use affected_rows.

As per the manual http://php.net/manual/en/mysqli.affected-rows.php

printf("Affected rows (UPDATE): %d\n", $mysqli->affected_rows);

Object oriented style

int $mysqli->affected_rows;

Sidenote:

Using

$update_resultrow = $update_stmt->num_rows;

and checking for errors, would have thrown an error, rather than "return 0".

http://php.net/manual/en/mysqli.error.php

score 0 · Answer 2 · answered Jun 24 '15 at 05:51

Try to find the number of rows affected by your query instead of finding number of rows as below:

$update_sql = "UPDATE ACCOUNT_EMPLOYEE SET NAME = ? WHERE ID = ?";
if ($update_stmt = $conn -> prepare($update_sql)) {
    if ($update_stmt->bind_param("si",
        $newname,
        $acc_id
    )
    ) {
        if ($update_stmt->execute()) {
            // must declare here to be able to get num_rows
            $update_stmt->store_result();
            $update_resultrow = $update_stmt->affected_rows;
            if ($update_resultrow == 0) {
                echo $error_forgot_noresult . '????' . $acc_id ;
                $update_stmt->close();
                $conn->close();
                exit();
            }
        }
    }
}

Execute Success but num_rows return 0 [PHP-MySQL]

2 Answers2

Linked