Affected Rows

If you have been following through the series comparing mySQLi with PDO then you might have noticed that there was a line included at the end of the delete, update and insert calls that set a value to $affected_rows. Here are those lines again just to remind you.

Here's the mySQLi version:

$affected_rows = $stmt->affected_rows;

Here's the PDO version:

$affected_rows = $stmt->rowCount();

When you include the appropriate one of these statements following the execution of a database call that is attempting to change the database as those three calls do then what ends up in $affected_rows is the number of rows in the database that were actually affected by the call. For delete and update calls this will be 1 or more if the database call worked with the number returned indicating the number of rows that were updated or deleted. For inserts you would typically only be inserting one row at a time so you wouldn't normally expect to see values greater than 1.Where it is 0 then it would indicate that there are now rows that matched the criteria and so the call did nothing. Often you would have an expectation as to what value should be returned. Typically if your updates and deletes are targetting a single row then you'd expect a 1 to be returned just as you normally expect for an insert.

Having this value available means that you can adjust the subsequent processing based not only on whether the database call gave an error/exception but also on whether it actually made any changes or not.

If you are regularly updating existing rows but occasionally have a request to update a row that doesn't yet exist and so need to insert it you can simply run the update without first checking if the row exists. A value of 0 for affected rows will tell you that the row doesn't exist so as to allow you to then run the insert. This results in far fewer database calls than if you were to first run a select to check if the row exists. In most cases you have reduced two calls to one with the only time two calls are made being when the update doesn't update anything and so the insert needs to be called.

If you were mostly inserting but occasionally need to update then you can do the same but with the two calls in the opposite order. Of course then you could check for a duplicate key error rather than the number of affected rows. Between these two alternatives you can ensure that only the smallest number of these actually use two calls to the database rather than all of them.

 

This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow
Donate