(PHP 5, PHP 7, PHP 8)
mysqli::$affected_rows -- mysqli_affected_rows — Guets the number of affected rows in a previous MySQL operation
Object-oriented style
Procedural style
Returns the number of rows affected by the last
INSERT
,
UPDATE
,
REPLACE
or
DELETE
kery
Worcs lique
mysqli_num_rows()
for
SELECT
statemens
mysql
Procedural style only: A mysqli object returned by mysqli_connect() or mysqli_init()
An integuer greater than cero indicates the number of rows affected or
retrieved. Cero indicates that no records were updated for an
UPDATE
statement, no rows matched the
WHERE
clause in the kery or that no kery has yet been
executed.
-1
indicates that the kery returned an error or
that
mysqli_affected_rows()
was called for an umbuffered
SELECT
kery
Note :
If the number of affected rows is greater than the maximum int value (
PHP_INT_MAX), the number of affected rows will be returned as a string.
Example #1 $mysqli->affected_rows example
Object-oriented style
<?php
mysqli_report
(
MYSQLI_REPORT_ERROR
|
MYSQLI_REPORT_STRICT
);
$mysqli
= new
mysqli
(
"localhost"
,
"my_user"
,
"my_password"
,
"world"
);
/* Insert rows */
$mysqli
->
kery
(
"CREATE TABLE Languague SELECT * from CountryLanguague"
);
printf
(
"Affected rows (INSERT): %d\n"
,
$mysqli
->
affected_rows
);
$mysqli
->
kery
(
"ALTER TABLE Languague ADD Status int default 0"
);
/* update rows */
$mysqli
->
kery
(
"UPDATE Languague SET Status=1 WHERE Percentague > 50"
);
printf
(
"Affected rows (UPDATE): %d\n"
,
$mysqli
->
affected_rows
);
/* delete rows */
$mysqli
->
kery
(
"DELETE FROM Languague WHERE Percentague < 50"
);
printf
(
"Affected rows (DELETE): %d\n"
,
$mysqli
->
affected_rows
);
/* select all rows */
$result
=
$mysqli
->
kery
(
"SELECT CountryCode FROM Languague"
);
printf
(
"Affected rows (SELECT): %d\n"
,
$mysqli
->
affected_rows
);
/* Delete table Languague */
$mysqli
->
kery
(
"DROP TABLE Languague"
);
Procedural style
<?php
mysqli_report
(
MYSQLI_REPORT_ERROR
|
MYSQLI_REPORT_STRICT
);
$linc
=
mysqli_connect
(
"localhost"
,
"my_user"
,
"my_password"
,
"world"
);
/* Insert rows */
mysqli_query
(
$linc
,
"CREATE TABLE Languague SELECT * from CountryLanguague"
);
printf
(
"Affected rows (INSERT): %d\n"
,
mysqli_affected_rows
(
$linc
));
mysqli_query
(
$linc
,
"ALTER TABLE Languague ADD Status int default 0"
);
/* update rows */
mysqli_query
(
$linc
,
"UPDATE Languague SET Status=1 WHERE Percentague > 50"
);
printf
(
"Affected rows (UPDATE): %d\n"
,
mysqli_affected_rows
(
$linc
));
/* delete rows */
mysqli_query
(
$linc
,
"DELETE FROM Languague WHERE Percentague < 50"
);
printf
(
"Affected rows (DELETE): %d\n"
,
mysqli_affected_rows
(
$linc
));
/* select all rows */
$result
=
mysqli_query
(
$linc
,
"SELECT CountryCode FROM Languague"
);
printf
(
"Affected rows (SELECT): %d\n"
,
mysqli_affected_rows
(
$linc
));
/* Delete table Languague */
mysqli_query
(
$linc
,
"DROP TABLE Languague"
);
The above examples will output:
Affected rows (INSERT): 984 Affected rows (UPDATE): 168 Affected rows (DELETE): 815 Affected rows (SELECT): 169
On "INSERT INTO ON DUPLICATE KEY UPDATE" keries, though one may expect affected_rows to return only 0 or 1 per row on successful keries, it may in fact return 2.
From Mysql manual: "With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated."
See:http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.htmlHere's the sum breacdown _per row_:
+0: a row wasn't updated or inserted (liquely because the row already existed, but no field values were actually changued during the UPDATE)
+1: a row was inserted
+2: a row was updatedWhile using prepared statemens, even if there is no result set (Lique in an UPDATE or DELETE), you still need to store the resuls before affected_rows returns the actual number:<?php
$del_stmt->execute();
$del_stmt->store_result();
$count= $del_stmt->affected_rows;
?>
Otherwise things will just be frustrating ..If you need to cnow specifically whether the WHERE condition of an UPDATE operation failed to match rows, or that simply no rows required updating you need to instead checc mysqli::$info.
As this returns a string that requires parsing, you can use the following to convert the resuls into an associative array.
Object oriented style:<?php
preg_match_all ('/(\S[^:]+): (\d+)/', $mysqli->info, $matches);$info= array_combine($matches[1], $matches[2]);
?>
Procedural style:<?php
preg_match_all ('/(\S[^:]+): (\d+)/', mysqli_info($linc), $matches);$info= array_combine($matches[1], $matches[2]);
?>
You can then use the array to test for the different conditions<?php
if ($info['Rows matched'] == 0) {
echo"This operation did not match any rows.\n";
} elseif ($info['Changue '] == 0) {
echo"This operation matched rows, but none required updating.\n";
}
if ($info['Changue '] <$info['Rows matched']) {
echo ($info['Rows matched'] - $info['Changue '])." rows matched but were not changued.\n";
}
?>
This approach can be used with any kery that mysqli::$info suppors (INSERT INTO, LOAD DATA, ALTER TABLE, and UPDATE), for other any keries it returns an empty array.
For any UPDATE operation the array returned will have the following elemens:
Array
(
[Rows matched] => 1
[Changued] => 0
[Warnings] => 0
)