It appears that an UPDATE prepared statement which contains the same data as that already in the database returns 0 for affected_rows. I was expecting it to return 1, but it must be comparing the input values with the existing values and determining that no UPDATE has occurred.
mysqli_stmt::$affected_rows
mysqli_stmt_affected_rows
(PHP 5, PHP 7)
mysqli_stmt::$affected_rows -- mysqli_stmt_affected_rows — Returns the total number of rows changed, deleted, inserted, or matched by the last statement executed
Descrição
Estilo orientado à objeto
int|string $mysqli_stmt->affected_rows;
Estilo procedural
Returns the number of rows affected by INSERT,
UPDATE, or DELETE query.
Works like mysqli_stmt_num_rows() for
SELECT statements.
Parâmetros
-
stmt -
Somente no estilo procedural: Um recurso statement retornado por mysqli_stmt_init().
Valor Retornado
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 or
that, for a SELECT query,
mysqli_stmt_affected_rows() was called prior to calling
mysqli_stmt_store_result().
Nota:
If the number of affected rows is greater than maximum PHP int value, the number of affected rows will be returned as a string value.
Exemplos
Exemplo #1 mysqli_stmt_affected_rows() example
Estilo orientado à objeto
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* create temp table */
$mysqli->query("CREATE TEMPORARY TABLE myCountry LIKE Country");
$query = "INSERT INTO myCountry SELECT * FROM Country WHERE Code LIKE ?";
/* prepare statement */
$stmt = $mysqli->prepare($query);
/* Bind variable for placeholder */
$code = 'A%';
$stmt->bind_param("s", $code);
/* execute statement */
$stmt->execute();
printf("Rows inserted: %d\n", $stmt->affected_rows);
Estilo procedural
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* create temp table */
mysqli_query($link, "CREATE TEMPORARY TABLE myCountry LIKE Country");
$query = "INSERT INTO myCountry SELECT * FROM Country WHERE Code LIKE ?";
/* prepare statement */
$stmt = mysqli_prepare($link, $query);
/* Bind variable for placeholder */
$code = 'A%';
mysqli_stmt_bind_param($stmt, "s", $code);
/* execute statement */
mysqli_stmt_execute($stmt);
printf("Rows inserted: %d\n", mysqli_stmt_affected_rows($stmt));
Os exemplos acima irão imprimir:
Rows inserted: 17
Veja Também
- mysqli_stmt_num_rows() - Returns the number of rows fetched from the server
- mysqli_stmt_store_result() - Stores a result set in an internal buffer
add a note
User Contributed Notes 3 notes
Carl Olsen ¶
18 years ago
gerbawn at 163 dot com ¶
9 years ago
I find should add $stmt->store_result() after $stmt->execute(), otherwise you can't get right results when use $stmt->affected_rows
Chuck ¶
17 years ago
I'm not sure whether or not this is the intended behavior, but I noticed through testing that if you were to use transactions and prepared statements together and you added a single record to a database using a prepared statement, but later rolled it back, mysqli_stmt_affected_rows will still return 1.
