Affected_rows

In this article, we will focus on the mysqli_affected_rows() function in PHP, which is used to retrieve the number of rows affected by the previous MySQL operation. We will provide you with an overview of the function, how it works, and examples of its use.

Introduction to the mysqli_affected_rows() function

The mysqli_affected_rows() function is a built-in function in PHP that is used to retrieve the number of rows affected by the previous MySQL operation. It is commonly used after executing an INSERT, UPDATE, or DELETE query.

How to use the mysqli_affected_rows() function

Using the mysqli_affected_rows() function is very simple. You just need to call the function and pass in a valid MySQLi object as an argument. Here is an example:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

$mysqli->query("UPDATE users SET name='John' WHERE id=1");
echo "Rows updated: " . $mysqli->affected_rows;

$mysqli->close();
?>

In this example, we create a new MySQLi object and execute an UPDATE query to update the name of a user with an ID of 1 to "John". We then call the mysqli_affected_rows() function to retrieve the number of rows affected by the query and output the result to the console.

Advanced usage

The mysqli_affected_rows() function can also be used in more advanced scenarios. For example, you can use the function to retrieve the number of rows affected by a SELECT query that uses the FOUND_ROWS() function. Here is an example:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

$mysqli->query("SELECT SQL_CALC_FOUND_ROWS * FROM users WHERE age > 18");
$mysqli->query("SELECT FOUND_ROWS()");
$result = $mysqli->store_result();
$row = $result->fetch_row();
echo "Rows selected: " . $row[0];

$mysqli->close();
?>

In this example, we execute a SELECT query that uses the SQL_CALC_FOUND_ROWS keyword to calculate the number of rows that would be returned without the LIMIT clause. We then execute a second query that uses the FOUND_ROWS() function to retrieve the calculated number of rows. We retrieve the result set using the store_result() method of the MySQLi object and then fetch the first row using the fetch_row() method. We then output the calculated number of rows to the console.

Conclusion

In conclusion, the mysqli_affected_rows() function is a powerful tool for retrieving the number of rows affected by a MySQL operation in PHP. By understanding how to use the function and its advanced usage scenarios, you can take advantage of this feature to create powerful and flexible MySQL queries in your PHP scripts.