PHP Get Number of Rows in MySQL — Complete Guide

When working with databases in PHP, one of the most common tasks developers perform is counting the number of rows returned by a MySQL query. Whether you’re building an admin dashboard, displaying search results, or validating user data, knowing how many rows match a query is essential.

In this complete guide, we’ll cover all major methods to get the number of rows in MySQL using PHP — from classic mysqli_num_rows() to more advanced approaches like using COUNT(*) and PDO.

⚙️ What Does “Get Number of Rows” Mean in PHP MySQL?

When you execute a SQL query in PHP, MySQL sends back a result set (rows and columns). Sometimes, you don’t need all the data — you just need to know how many rows match the query condition.

For example:

• You might want to know how many users exist in your table.
• Or check if a record exists before inserting a new one.
• Or limit your pagination based on total results.

PHP provides multiple ways to find the total number of rows returned by a query.

🔹 Method 1: Using mysqli_num_rows() Function

The most straightforward way to get the number of rows from a SELECT query is using the built-in mysqli_num_rows() function.

You may need to read in detail about SQL and how to use it with MySQL

✅ Example Code:

<?php
$conn = mysqli_connect("localhost", "root", "", "test_db");

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

$row_count = mysqli_num_rows($result);

echo "Total Rows: " . $row_count;

mysqli_close($conn);

💡 Explanation:

• The query fetches all rows from the users table.
• mysqli_num_rows() counts how many rows are in the result set.
• It only works with SELECT queries.

⚠️ Note: You must call mysqli_num_rows() after running mysqli_query() and before closing the result.

🔹 Method 2: Using mysqli_affected_rows() for UPDATE, DELETE, or INSERT

When you perform data manipulation queries (like UPDATE, DELETE, or INSERT), you can use mysqli_affected_rows() to know how many rows were affected.

✅ Example:

<?php
$conn = mysqli_connect("localhost", "root", "", "test_db");

mysqli_query($conn, "DELETE FROM users WHERE status = 'inactive'");
echo "Rows Deleted: " . mysqli_affected_rows($conn);

mysqli_close($conn);

💡 Explanation:

• mysqli_affected_rows() gives the number of rows that were modified by a query.
• Works with UPDATE, DELETE, and INSERT, not SELECT.

🔹 Method 3: Using SELECT COUNT(*) Query

This is the most efficient and recommended way to count rows — especially when your table has many records.

✅ Example:

<?php
$conn = mysqli_connect("localhost", "root", "", "test_db");

$sql = "SELECT COUNT(*) AS total_users FROM users";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);

echo "Total Users: " . $row['total_users'];

mysqli_close($conn);

💡 Why It’s Better:

• MySQL counts the rows on the server side.
• Only one numeric value is returned — reducing memory usage.
• Much faster than fetching all rows.

🔥 Pro Tip: Always use COUNT(*) for large datasets instead of mysqli_num_rows().

🔹 Method 4: Using PDO and rowCount()

If you prefer PDO (PHP Data Objects), you can use the rowCount() method.

✅ Example:

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=test_db", "root", "");

    $stmt = $pdo->query("SELECT * FROM users");
    $count = $stmt->rowCount();

    echo "Total Rows: " . $count;

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

⚠️ Important:

For SELECT queries, rowCount() might not work consistently across all drivers (especially MySQL).
For reliable results, use:

$stmt = $pdo->query("SELECT COUNT(*) FROM users");
$total = $stmt->fetchColumn();
echo "Total Rows: " . $total;

🔹 Method 5: Getting Number of Rows Using mysqli_store_result()

For advanced developers handling large result sets, you can use mysqli_store_result() to manage memory efficiently.

✅ Example:

<?php
$conn = mysqli_connect("localhost", "root", "", "test_db");

mysqli_query($conn, "SELECT * FROM users");
$result = mysqli_store_result($conn);

echo "Rows Found: " . mysqli_num_rows($result);

mysqli_free_result($result);
mysqli_close($conn);

This method stores the result set in memory and allows multiple result-handling operations.

🧩 Comparison of PHP Row Count Methods

Method	Works For	Performance	Notes
mysqli_num_rows()	SELECT	Medium	Loads all rows into memory
mysqli_affected_rows()	UPDATE/DELETE/INSERT	High	Returns modified rows count
COUNT(*)	SELECT	Very High	Server-side efficient
PDO::rowCount()	SELECT (limited)	Medium	Use fetchColumn() instead for reliability
mysqli_store_result()	SELECT	High	For large data sets

💬 Best Practice for Counting Rows in PHP MySQL

• Use COUNT(*) for accurate and fast counts.
• Avoid using mysqli_num_rows() on large tables — it consumes more memory.
• For INSERT/UPDATE/DELETE — always use mysqli_affected_rows().
• When using PDO, rely on fetchColumn() for consistent results.

❓ FAQs About Getting Number of Rows in PHP MySQL

🔸 1. Can I use mysqli_num_rows() after INSERT or UPDATE?

No. It only works with SELECT queries. Use mysqli_affected_rows() instead.

🔸 2. Which method is fastest for counting rows?

SELECT COUNT(*) is the fastest and most resource-efficient method.

🔸 3. Does rowCount() work with PDO SELECT queries?

It might not always return accurate results for MySQL. Use fetchColumn() on a COUNT(*) query instead.

🔸 4. What’s the difference between mysqli_num_rows() and COUNT(*)?

mysqli_num_rows() counts rows after fetching, while COUNT(*) counts them in the database.

🔸 5. Can I get the number of rows without fetching data?

Yes, using SELECT COUNT(*) gives you the row count without loading all data into PHP memory.

🔸 6. Why is COUNT(*) better than mysqli_num_rows() for large datasets?

Because MySQL handles counting internally without sending all row data to PHP — saving bandwidth and processing time.

🚀 Conclusion

Knowing how to get the number of rows in PHP MySQL is a fundamental skill that can improve performance and make your database operations more efficient.

• For small datasets or debugging → mysqli_num_rows() works fine.
• For accurate, scalable counting → use SELECT COUNT(*).
• For modification queries → mysqli_affected_rows().
• For PDO users → prefer fetchColumn() after COUNT(*).

By choosing the right approach, you can ensure that your web applications remain fast, efficient, and optimized — especially as your database grows.

✅ Pro Tip:
If your website or app starts growing and your queries slow down — consider upgrading to a faster host that supports advanced PHP performance configurations.
👉 Move to WebfulHost.com for reliable PHP hosting optimized for MySQL performance.