There are no rows affected by an update with identical data.
So here is one very ugly solution for these cases:
<?
function mysql_matched_rows() {
$_kaBoom=explode(' ',mysql_info());
return $_kaBoom[2];
}
?>
mysql_affected_rows
(PHP 4, PHP 5)
mysql_affected_rows — 一番最近の操作で変更された行の数を得る
警告
この拡張モジュールは PHP 5.5.0 で非推奨になり、PHP 7.0.0 で削除されました。 MySQLi あるいは PDO_MySQL を使うべきです。詳細な情報は MySQL: API の選択 を参照ください。 この関数の代替として、これらが使えます。
説明
link_identifier と関連付けられた直近の
INSERT、UPDATE、REPLACE、DELETE クエリによって変更された行の数を取得します。
パラメータ
-
link_identifier -
MySQL 接続。指定されない場合、mysql_connect() により直近にオープンされたリンクが指定されたと仮定されます。そのようなリンクがない場合、引数を指定せずに mysql_connect() がコールした時と同様にリンクを確立します。リンクが見付からない、または、確立できない場合、
E_WARNINGレベルのエラーが生成されます。
戻り値
成功した場合に変更された行の数を、直近のクエリが失敗した場合に -1 を返します。
直近のクエリが WHERE 句を含まない DELETE だった場合、テーブルから すべてのレコードが削除されますが、MySQL 4.1.2 以前のバージョンでは この関数はゼロを返します。
UPDATE を使用する場合、MySQL では新旧の値が同じときには更新処理を行いません。 このことから、必ずしも mysql_affected_rows() の返す値が マッチする行の数と一致するとは限りません。返す値は実際に更新処理が行われた 行の数です。
REPLACE ステートメントは、まず最初に同じ主キーのレコードを削除した後に 新しいレコードを挿入します。この関数は、削除された行の数と 挿入された行の数を足したものを返します。
"INSERT ... ON DUPLICATE KEY UPDATE" クエリの場合の戻り値は、
新しい行を追加した場合が 1
で既存の行を更新した場合が 2 となります。
例
例1 mysql_affected_rows() の例
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');
/* これは、削除されたレコードの正しい数をかえずはず */
mysql_query('DELETE FROM mytable WHERE id < 10');
printf("Records deleted: %d\n", mysql_affected_rows());
/* 決して真にはならない where 条件なので、結果は 0 となるはず */
mysql_query('DELETE FROM mytable WHERE 0');
printf("Records deleted: %d\n", mysql_affected_rows());
?>上の例の出力は、 たとえば以下のようになります。
Records deleted: 10 Records deleted: 0
例2 トランザクションを利用した mysql_affected_rows() の例
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');
/* レコードの更新 */
mysql_query("UPDATE mytable SET used=1 WHERE id < 10");
printf ("Updated records: %d\n", mysql_affected_rows());
mysql_query("COMMIT");
?>上の例の出力は、 たとえば以下のようになります。
Updated Records: 10
注意
注意: トランザクション
トランザクションを使用する場合には、コミット後ではなく INSERT, UPDATE, DELETE クエリの後に mysql_affected_rows() をコールする必要があります。
注意: SELECT ステートメント
SELECT から返される行の数を得る際には、 mysql_num_rows() が利用できます。
注意: 外部キーによる連鎖
mysql_affected_rows() は、外部キー制約の ON DELETE CASCADE や ON UPDATE CASCADE で暗黙のうちに変更された行については数えません。
add a note
User Contributed Notes 11 notes
Ome Ko ¶
13 years ago
temp02 at flexis dot com dot br ¶
19 years ago
SCENARIO
1. You're using MySQL 4.1x with foreign keys.
2. You have table t2 linked to table t1 by a CASCADE ON DELETE foreign key.
3. t2 has a UNIQUE key so that duplicate records are unacceptable.
3. You have a REPLACE query on t1 followed by an INSERT query on t2 and expect the second query to fail if there's an attempted insert of a duplicate record.
PROBLEM
You notice that the second query is not failing as you had expected even though the record being inserted is an exact duplicate of a record previously inserted.
CAUSE
When the first query (the REPLACE query) deletes a record from t1 in the first stage of the REPLACE operation, it cascades the delete to the record that would be duplicated in t2. The second query then does not fail because the "duplicate" record is no longer a duplicate, as the original one has just been deleted.
ahmd ¶
8 years ago
I was just testing "INSERT INTO ... ON DUPLICATE KEY UPDATE" syntax, on PHP 5.3.29 and mysql_affected_rows() was returning either 2 for updated row, 1 for inserted new row, and also 0, which was not documented, evidently when nothing was inserted. I was inserting a single row.
HMax ¶
17 years ago
If you use "INSERT INTO ... ON DUPLICATE KEY UPDATE" syntax, mysql_affected_rows() will return you 2 if the UPDATE was made (just as it does with the "REPLACE INTO" syntax) and 1 if the INSERT was.
So if you use one SQL request to insert several rows at a time, and some are inserted, some are just updated, you won't get the real count.
dobrys at abv dot bg ¶
17 years ago
I see that when try to use mysql_affected_rows() with "mysql_pconnect(...)" without link indetifier as param in "mysql_affected_rows()" the result is allways -1.
When use link identifier "mysql_affected_rows($this_sql_connection)" - everything is Fine. This is is on PHP Version 5.2.0
Hope that this was helpfull for somebody
deponti A_T tiscalinet D0T it ¶
21 years ago
It works also for REPLACE query,returning:
0 if the record it's already updated (0 record modified),
1 if the record it's new (1 record inserted),
2 if the record it's updated (2 operations: 1 deletion+ 1 insertion)
gtisza at gmail dot com ¶
5 years ago
Note that when the CLIENT_FOUND_ROWS connection flag was used, affected_rows returns the number of rows matched by the WHERE condition of an UPDATE query, even if the query doesn't actually change those rows. I.e. for
INSERT INTO t(id, val) VALUES (1, 'x');
UPDATE t SET val = 'x' WHERE id = 1;
the number of affected rows will be 0 normally but 1 with CLIENT_FOUND_ROWS.
vitospericolato at gmail dot com ¶
7 years ago
calling mysql_affected_rows(null)
is not the same that calling mysql_affected_rows()
So, if you have a $link variable that could be null, you must write
if($link)
$n=mysql_affected_rows($link);
else
$n=mysql_affected_rows();
brian at smitherconsulting dot com ¶
8 years ago
In the case of INSERT where a row/slot had been previously deleted, making an uncollapsed hole in the table, and the record being inserted fills that empty row/slot, that is to say, the inserted data did not create a new row/slot/space, then this may explain why a zero result is returned by this function.
steffen at showsource dot dk ¶
20 years ago
Using OPTIMIZE TABLE will also return true.
So, if you want to check the numbers of deleted records, use mysql_affected_rows() before OPTIMIZE TABLE
sean at adtools dot co dot uk ¶
16 years ago
Here's a little function I've been using for a while now, pass it two parameters (action command (1 or 0 see notes)) and a sql statement.
It returns a simple line which shows the length of time taken to action the query, the status of the query (0= query not actioned, you can set this value for testing, 1=success qry executed successfully, -1= failed, there was a problem with the sql statement) the number of lines affected by that query and the sql statement itself.
I've found this invaluable when trying to tie down large amounts of updates to a table, using this you can easily see where a query was successfully executed and the number of rows are affected, or where there are problems and a statement has failed for example.
<?php
function dosql($action,$sql){
# assuming you have setup a link to your database entitled $link
# action = 1 run this query
# action = 0 don't run, just return sql statement
$start = getmtime();
if($action==1){
$result = mysql_query($sql);
$affectedrows = "[".mysql_affected_rows($link)."]";
}
return "[".number_format((getmtime()-$start),3)."][$action]: $sql\n";
mysql_free_result($result);
}
?>
Example output:
[0.072][1][80]: UPDATE MYTABLE SET FIELD = 1;
[0.106][1][758]: UPDATE ANOTHERTABLE SET FIELD = 2;
[0.006][-1][0]: UPDATER ANOTHERTABLE SET FIELD = 2;
The output shows:
[Timetaken][result]][lines affected]
The result will be either -1, 0 or 1, -1 means there's a problem with the sql statement, 1 means it executed correctly, 0 means it wasn't executed.
