The PHP Anthology Volume I, Chapter 3 - PHP and MySQL

Counting Affected Rows

It's also possible to find out how many rows were affected by an UPDATE, INSERT or DELETE query, using the PHP function mysql_affected_rows. Use of mysql_affected_rows is not common in typical PHP applications, but it could be a good way to inform users that, "You've just deleted 1854 records from the Customers table. Have a nice day!"

Unlike mysql_num_rows, which takes a result set resource identifier as its argument, mysql_affected_rows takes the database connection identifier. It returns the number of rows affected by the last query that modified the database, for the specified connection.

Here's how mysql_affected_rows can be used:

Example 3.22. 16.php (excerpt)          
         
// Connect to MySQL          
$dbConn = &connectToDb($host, $dbUser, $dbPass, $dbName);          
         
// A query which updates the database          
$sql = "UPDATE          
         articles          
       SET          
         author='The Artist Formerly Known as...'          
       WHERE          
         author='HarryF'";          
         
// Run the query, identifying the connection          
$queryResource = mysql_query($sql, $dbConn);          
         
// Fetch the number of rows affected          
$changedRows = mysql_affected_rows($dbConn);          
         
echo $changedRows . ' rows changed<br />';

As situations in which mysql_affected_rows is needed are uncommon, I'll omit this from the MySQLResult class in the interests of keeping things simple.

After inserting a row, how do I find out its row number?

When you're dealing with AUTO_INCREMENT columns in database tables, it's often useful to be able to find out the ID of a row you've just inserted, so that other tables can be updated with this information. That, after all, is how relationships between tables are built. PHP provides the function mysql_insert_id, which, when given a link identifier, returns the ID generated by the last INSERT performed with that connection. Here's how mysql_insert_id can be used:

Example 3.23. 17.php (excerpt)          
         
// A query to insert a row          
$sql = "INSERT INTO          
         articles          
       SET          
         title='How to use mysql_insert_id()',          
         body='This is an example',          
         author='HarryF'";          
         
// Run the query, identifying the connection          
$queryResource = mysql_query($sql, $dbConn);          
         
// Fetch the inserted ID          
$insertID = mysql_insert_id($dbConn);          
         
echo 'The new row has ID: ' . $insertID;

Class Insert ID

To use this functionality in our MySQLResult class, add the following method:

Example 3.24. Database/MySQL.php (in SPLIB) (excerpt)          
         
 /**          
  * Returns the ID of the last row inserted          
  * @return int          
  * @access public          
  */          
 function insertID()          
 {          
   return mysql_insert_id($this->mysql->dbConn);          
 }

As you might guess, using this method is quite straightforward:

Example 3.25. 18.php (excerpt)          
         
// Include the MySQL class          
require_once 'Database/MySQL.php';          
         
$host   = 'localhost';   // Hostname of MySQL server          
$dbUser = 'harryf';    // Username for MySQL          
$dbPass = 'secret';    // Password for user          
$dbName = 'sitepoint'; // Database name          
         
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);          
         
// A query to insert a row          
$sql="INSERT INTO          
       articles          
     SET          
       title='How to use mysql_insert_id()',          
       body='This is an example',          
       author='HarryF'";          
         
$result = $db->query($sql);          
         
echo 'The new row as ID: ' . $result->insertID();