How to Retrieve Row Count from MySQLi Queries in PHP

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

Complete Example

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

// Verify the connection
if ($mysqli->connect_errno) {
    echo "Connection failed: " . $mysqli->connect_error;
    exit();
}

// Define and execute a SELECT query
$query  = "SELECT * FROM users";
$result = $mysqli->query($query);

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

// Get the number of rows in the result set
$num_rows = mysqli_num_rows($result);

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

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

The script performs the following steps:

Creates a new mysqli instance to connect to the database.

Checks connect_errno to ensure the connection succeeded.

Runs a SELECT statement and stores the result in $result.

Validates that the query did not fail before proceeding.

Calls mysqli_num_rows($result) to retrieve the row count and stores it in $num_rows.

Outputs the count with echo and finally closes the connection.

Important note: mysqli_num_rows only works after a successful query. If the query fails, the result set is empty and the function will return 0, so always check the query result before calling it.

Using mysqli_num_rows simplifies determining whether a SELECT query returned any data and allows you to easily report or process the number of rows returned.