Introduction to mysqli_affected_rows()
The mysqli_affected_rows() function is used to return the number of rows affected by the most recent INSERT, UPDATE, or DELETE query executed using the MySQLi extension. This function is particularly useful when you need to verify that a query has made the intended changes to the database.Syntax of mysqli_affected_rows()
The syntax for using mysqli_affected_rows() is straightforward:
int mysqli_affected_rows ( mysqli $link )
- $link: The MySQLi connection object returned by mysqli_connect().
- Return Value: The function returns an integer value representing the number of rows affected by the last query. If no rows were affected or if the last query was not a data modification query, it returns 0.
Practical Usage of mysqli_affected_rows()
Let’s explore various use cases for mysqli_affected_rows() through practical examples.Example 1: Checking Rows Affected by an INSERT Query
When inserting new data into a table, you might want to check if the insertion was successful and how many rows were affected.
<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Insert a new record
$sql = "INSERT INTO users (username, email) VALUES ('new_user', 'newuser@example.com')";
if ($conn->query($sql) === TRUE) {
// Check the number of rows affected
$affectedRows = $conn->affected_rows;
echo "New record created successfully. Rows affected: " . $affectedRows;
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
// Close the connection
$conn->close();
?>
In this example, we insert a new user into the users table. After executing the INSERT query, mysqli_affected_rows() is used to determine how many rows were affected. Since this is a single insertion, the expected result is 1.
Example 2: Checking Rows Affected by an UPDATEQuery
When updating existing records, you may need to confirm how many records were updated.
<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Update a record
$sql = "UPDATE users SET email = 'updateduser@example.com' WHERE username = 'existing_user'";
if ($conn->query($sql) === TRUE) {
// Check the number of rows affected
$affectedRows = $conn->affected_rows;
echo "Record updated successfully. Rows affected: " . $affectedRows;
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
// Close the connection
$conn->close();
?>
In this example, we update the email address of a user where the username is ‘existing_user’. mysqli_affected_rows() is used to determine how many rows were affected by the UPDATE query. If the user with the specified username exists and their email is updated, the function will return 1.
Example 3: Checking Rows Affected by a DELETE Query
To check how many rows were deleted by a DELETE query, you can use mysqli_affected_rows().
<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Delete a record
$sql = "DELETE FROM users WHERE username = 'old_user'";
if ($conn->query($sql) === TRUE) {
// Check the number of rows affected
$affectedRows = $conn->affected_rows;
echo "Record deleted successfully. Rows affected: " . $affectedRows;
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
// Close the connection
$conn->close();
?>
Best Practices for Using mysqli_affected_rows()
Here are some best practices to consider when using mysqli_affected_rows():1. Check for Query Success
Always check if the query execution was successful before using mysqli_affected_rows(). If the query fails, using mysqli_affected_rows() might yield incorrect results or cause errors.
if ($conn->query($sql) === FALSE) {
echo "Error: " . $conn->error;
exit();
}
2. Use Transactions for Bulk Operations
When performing multiple data modifications, consider using transactions to ensure data integrity. Transactions allow you to commit or roll back changes based on the success of all operations.
$conn->begin_transaction();
try {
$conn->query($sql1);
$conn->query($sql2);
$conn->commit();
} catch (Exception $e) {
$conn->rollback();
echo "Failed: " . $e->getMessage();
}
3. Validate Inputs and Avoid SQL Injection
Always sanitize user inputs and use prepared statements to prevent SQL injection attacks.
$stmt = $conn->prepare("UPDATE users SET email = ? WHERE username = ?");
$stmt->bind_param("ss", $email, $username);
$stmt->execute();
$affectedRows = $stmt->affected_rows;
4. Close Database Connections
Always close your database connections when they are no longer needed to free up resources.
$conn->close();
Advanced Example: Implementing a Batch Update with mysqli_affected_rows()
Let’s implement a batch update where we update multiple records and check how many rows were affected.
<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Start transaction
$conn->begin_transaction();
try {
// Prepare statements for batch updates
$stmt1 = $conn->prepare("UPDATE users SET status = 'inactive' WHERE last_login < NOW() - INTERVAL 1 YEAR");
$stmt1->execute();
$rowsAffected1 = $stmt1->affected_rows;
$stmt2 = $conn->prepare("UPDATE users SET email_verified = 1 WHERE email_verified = 0");
$stmt2->execute();
$rowsAffected2 = $stmt2->affected_rows;
// Commit transaction
$conn->commit();
echo "Batch update completed. Rows affected by first update: " . $rowsAffected1 . ", Rows affected by second update: " . $rowsAffected2;
} catch (Exception $e) {
$conn->rollback();
echo "Failed: " . $e->getMessage();
}
// Close the connection
$conn->close();
?>
In this advanced example, we perform a batch update using transactions. Two UPDATE queries are executed: one to mark users as inactive if they haven’t logged in for a year, and another to mark email addresses as verified. We use mysqli_affected_rows() to determine how many rows were affected by each update and ensure that the entire batch is either successfully committed or rolled back in case of an error.
Conclusion
The mysqli_affected_rows() function is a powerful tool in PHP for determining the number of rows affected by data modification queries. Whether you are inserting, updating, or deleting records, this function provides valuable information about the impact of your queries. By understanding its usage and following best practices, you can effectively manage and verify changes to your database, ensuring your applications run smoothly and reliably.
Remember to always check for query success, use transactions for bulk operations, validate inputs, and close database connections to maintain security and performance in your PHP applications. With these principles in mind, mysqli_affected_rows() can greatly enhance your ability to manage and interact with your MySQL database.