User Contributed Notes 10 notes

SCENARIO1. 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.

PROBLEMYou 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.

CAUSEWhen 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.

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.

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.0Hope that this was helpfull for somebody

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];}?>

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)

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.

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.