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
userstable. 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 runningmysqli_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, andINSERT, notSELECT.
🔹 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 ofmysqli_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()afterCOUNT(*).
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.
