MySQLi affected_rows Property

PHP

MySQLi affected_rows - Get Affected Rows

The MySQLi extension in PHP provides a powerful way to interact with MySQL databases. One crucial property is affected_rows, which helps determine how many rows were impacted by the most recent INSERT, UPDATE, or DELETE query. This tutorial will guide you through understanding, using, and applying the affected_rows property effectively in your PHP projects.

Prerequisites

  • Basic knowledge of PHP programming
  • Basic understanding of MySQL databases and SQL queries
  • PHP installed with MySQLi extension enabled
  • Access to a MySQL database and credentials
  • Basic setup of a MySQL database and table (optional but recommended)

Setup Steps

Before diving into the examples, ensure the following:

  1. Connect to MySQL database using MySQLi Object-Oriented style.
  2. Have a sample table with data to run INSERT, UPDATE, or DELETE queries.

// Database credentials
$host = 'localhost';
$user = 'root';
$pass = '';
$dbname = 'test_db';

// Create MySQLi connection
$mysqli = new mysqli($host, $user, $pass, $dbname);

// Check connection
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

Understanding MySQLi affected_rows Property

$mysqli->affected_rows returns the number of rows affected by the last executed INSERT, UPDATE, or DELETE query. This can be useful to verify if your query changed the database as expected.

Important notes:

  • Returns the number of rows changed, deleted, or inserted.
  • Returns -1 if the last query failed or no query was executed.
  • For INSERT queries, it typically returns 1 (one row inserted) or the number of rows for multi-insert statements.
  • For UPDATE, it counts only rows that had their values changed.

Examples

1. Using affected_rows with INSERT query


// Insert a new user
$sql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
if ($mysqli->query($sql)) {
    echo "Rows inserted: " . $mysqli->affected_rows;  // Output: Rows inserted: 1
} else {
    echo "Error: " . $mysqli->error;
}

2. Using affected_rows with UPDATE query


// Update a user's email
$sql = "UPDATE users SET email = 'john_doe_new@example.com' WHERE username = 'john_doe'";
$mysqli->query($sql);
echo "Rows updated: " . $mysqli->affected_rows;  // Output might be 1 or 0 if email is unchanged

Note: If the new value is same as the old value, affected_rows will return 0 since no actual change occurred.

3. Using affected_rows with DELETE query


// Delete a user by username
$sql = "DELETE FROM users WHERE username = 'john_doe'";
$mysqli->query($sql);
echo "Rows deleted: " . $mysqli->affected_rows;  // Output will be number of rows deleted or 0 if none

Best Practices

  • Always check if the query executed successfully before checking affected_rows.
  • Use affected_rows to validate updates and deletes to avoid silent failures.
  • Understand that affected_rows excludes rows matched but unchanged in UPDATE.
  • Use transactions when performing multiple queries that depend on affected_rows results for consistency.
  • Close the database connection when done.

Common Mistakes

  • Not checking if the query executed successfully before reading affected_rows, which can lead to misleading results.
  • Assuming affected_rows counts matched rows, when it actually counts changed rows only for UPDATE.
  • Using affected_rows before executing any query on the connection, which returns -1.
  • Confusing affected_rows with num_rows which is meant for SELECT queries.

Interview Questions

Junior Level Questions

  • Q1: What does $mysqli->affected_rows property represent?
    A1: It returns the number of rows affected by the last INSERT, UPDATE, or DELETE query.
  • Q2: What value does affected_rows return if the last query failed?
    A2: It returns -1 if the query failed or no query was executed.
  • Q3: Does affected_rows work with SELECT queries?
    A3: No, affected_rows only works with data-modifying queries like INSERT, UPDATE, and DELETE.
  • Q4: Which PHP extension provides the affected_rows property?
    A4: The MySQLi extension.
  • Q5: What type of value is returned from affected_rows?
    A5: It returns an integer representing the number of affected rows.

Mid Level Questions

  • Q1: What happens to affected_rows after an UPDATE query that changes no data?
    A1: It returns 0 because although rows may be matched, none were changed.
  • Q2: How can you use affected_rows to verify if a DELETE query worked?
    A2: By checking if affected_rows is greater than 0, indicating rows were deleted.
  • Q3: Why should one check for errors before using affected_rows?
    A3: Because if the query fails, affected_rows returns -1, which may cause misinterpretations.
  • Q4: Can affected_rows be used after multiple queries?
    A4: It only reflects the result of the most recent query executed on the connection.
  • Q5: What's the difference between affected_rows and num_rows?
    A5: affected_rows counts how many rows were affected by data-changing queries, while num_rows counts rows returned by SELECT statements.

Senior Level Questions

  • Q1: How would you handle reporting changes when an UPDATE query sets column values to their current value?
    A1: Since affected_rows returns 0 for unchanged rows, you might run a SELECT to compare before updating or design the app logic accordingly.
  • Q2: How can transactions impact the use of affected_rows?
    A2: Within a transaction, you can batch multiple data-changing queries and use affected_rows after each to verify success before committing.
  • Q3: How does multi-row INSERT affect affected_rows?
    A3: affected_rows returns the number of rows actually inserted, which can be more than one for bulk inserts.
  • Q4: Is affected_rows reliable for stored procedures that perform multiple DML operations?
    A4: No, affected_rows only applies to the last statement executed by the MySQLi query call, so special handling is needed for stored procedures.
  • Q5: How would you debug unexpected -1 from affected_rows?
    A5: Check for query errors via $mysqli->error, validate that a query was executed, and ensure connection is alive before using affected_rows.

Frequently Asked Questions (FAQ)

Q1: Can I use affected_rows with prepared statements?

Yes, affected_rows works with prepared statements after execution using $stmt->execute(). You access it via the MySQLi connection object or the statement object.

Q2: What value does affected_rows return if an UPDATE matches rows but does not change any values?

It returns 0 because no actual row values were changed.

Q3: Does affected_rows include rows matched but not deleted in a DELETE query?

No, it only counts rows actually deleted.

Q4: How can affected_rows help prevent bugs in web applications?

It helps verify if database changes were successful, allowing you to detect no-op updates or failed deletes and handle errors accordingly.

Q5: Is affected_rows available in procedural MySQLi style?

Yes, you can use mysqli_affected_rows($connection) to get the same result procedurally.

Conclusion

The MySQLi affected_rows property is essential to track the number of rows impacted by your INSERT, UPDATE, or DELETE queries in PHP. It provides critical feedback on your database operations and helps ensure data integrity in your applications. By following this tutorial, you now know how to implement, verify, and troubleshoot your code using affected_rows effectively.