PhpHow-ToBeginner · 4 min read

How to Count Rows in PHP: Simple Methods Explained

To count rows in PHP when working with databases, use mysqli_num_rows() for MySQLi or rowCount() for PDO after running a SELECT query. These functions return the number of rows fetched by your query.

📐

Syntax

mysqli_num_rows() counts rows in a MySQLi result set.

mysqli_num_rows($result): Returns the number of rows in the result.

PDOStatement::rowCount() counts rows affected or fetched.

$stmt->rowCount(): Returns the number of rows affected by the last DELETE, INSERT, or UPDATE, or number of rows fetched for some databases.

php

<?php
// MySQLi syntax
$num_rows = mysqli_num_rows($result);

// PDO syntax
$count = $stmt->rowCount();
?>

💻

Example

This example shows how to count rows from a MySQL database using MySQLi and PDO.

php

<?php
// MySQLi example
$mysqli = new mysqli('localhost', 'user', 'password', 'database');
if ($mysqli->connect_error) {
    die('Connect Error: ' . $mysqli->connect_error);
}

$result = $mysqli->query('SELECT * FROM users');
if ($result) {
    echo 'MySQLi row count: ' . mysqli_num_rows($result) . "\n";
}

// PDO example
try {
    $pdo = new PDO('mysql:host=localhost;dbname=database', 'user', 'password');
    $stmt = $pdo->query('SELECT * FROM users');
    echo 'PDO row count: ' . $stmt->rowCount() . "\n";
} catch (PDOException $e) {
    echo 'PDO Error: ' . $e->getMessage();
}
?>

Output

MySQLi row count: 5 PDO row count: 5

⚠️

Common Pitfalls

Many beginners expect rowCount() to always return the number of rows fetched in PDO, but it only reliably works for DELETE, INSERT, and UPDATE queries. For SELECT queries, rowCount() may return 0 depending on the database driver.

Using mysqli_num_rows() requires a successful SELECT query result; otherwise, it will cause errors.

php

<?php
// Wrong: expecting rowCount() to always work for SELECT
$stmt = $pdo->query('SELECT * FROM users');
echo $stmt->rowCount(); // May output 0

// Right: fetch all rows and count
$rows = $stmt->fetchAll();
echo count($rows); // Correct row count
?>

📊

Quick Reference

Function/Method	Use Case	Returns
mysqli_num_rows($result)	Count rows in MySQLi SELECT result	Number of rows in result set
PDOStatement::rowCount()	Count affected rows for INSERT/UPDATE/DELETE	Number of affected rows
count($stmt->fetchAll())	Count rows fetched in PDO SELECT	Number of rows fetched

✅

Key Takeaways

Use mysqli_num_rows() to count rows from MySQLi SELECT queries.

PDOStatement::rowCount() reliably counts affected rows for INSERT, UPDATE, DELETE, but not always for SELECT.

For PDO SELECT queries, fetch all rows and use count() to get the row count.

Always check your query result before counting rows to avoid errors.

Different database extensions have different ways to count rows; choose the right one.