How to Get Row Count from a MySQL Result Set Using PHP mysqli_num_rows

When using PHP to interact with a MySQL database, you may need to know how many rows are returned by a query. PHP provides the convenient function mysqli_num_rows for this purpose.

The following example demonstrates connecting to a MySQL server, executing a SELECT query, and using mysqli_num_rows to obtain the number of rows in the result set.

<?php
// Assume a connection to the database has been established
$mysqli = new mysqli('localhost', 'username', 'password', 'database_name');

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

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

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

// Use mysqli_num_rows to get the number of rows
$num_rows = mysqli_num_rows($result);

// Output the result
echo "Number of rows in result set: " . $num_rows;

// Close the connection
$mysqli->close();
?>

In the example, the query result is stored in $result , the row count is stored in $num_rows , and the count is echoed to the page.

Note that mysqli_num_rows should only be called after a successful query; if the query fails, the result set is empty and the function returns 0.

Summary

The PHP function mysqli_num_rows makes it easy to retrieve the number of rows in a result set, which is useful for checking whether data was returned and for counting query results.