Get Row Count from MySQLi Result Set in PHP Using mysqli_num_rows

Summary

When working with MySQL databases in PHP, you often need to know how many rows a SELECT query returned. The mysqli_num_rows function provides a straightforward way to retrieve this count from a result set.

Typical usage involves:

Creating a mysqli instance with host, username, password, and database name.

Checking $mysqli->connect_errno to ensure the connection succeeded.

Running a query, e.g., $result = $mysqli->query("SELECT * FROM users");, and verifying that $result is not false.

Calling $num_rows = mysqli_num_rows($result); to obtain the number of rows.

Outputting the count with echo "Number of rows: " . $num_rows;.

Closing the connection with $mysqli->close();.

If the query fails, the result set is empty and mysqli_num_rows returns 0, so always check for query errors before using the function.

<?php
// Assume a successful database connection
$mysqli = new mysqli('localhost', 'username', 'password', 'database_name');

if ($mysqli->connect_errno) {
    echo "Failed to connect to database: " . $mysqli->connect_error;
    exit();
}

$query = "SELECT * FROM users";
$result = $mysqli->query($query);

if (!$result) {
    echo "Query failed: " . $mysqli->error;
    exit();
}

$num_rows = mysqli_num_rows($result);
echo "Number of rows in result set: " . $num_rows;

$mysqli->close();
?>