In PHP with MySQL, how can you find the number of rows in a result set returned by a SELECT query?

Introduction / Context:
When working with relational databases from PHP, it is common to execute a SELECT query and then need to know how many rows were returned, for example to display totals, implement pagination, or perform conditional logic. PHP database extensions such as the legacy mysql extension and the newer mysqli extension provide specific functions to get the row count, and interview questions often check whether a candidate knows which functions are used and how to call them correctly.

Given Data / Assumptions:

• A SELECT query has been executed against a MySQL database from PHP.
• The result of the query has been stored in a resource or result object, depending on the extension used.
• The code uses either the older mysql_* functions or the mysqli extension.
• The task is to count rows in the result set, not in the entire table.

Concept / Approach:
The key idea is that the database server returns a result set object or resource to PHP, and the PHP extension exposes a function that knows how many rows it contains. The legacy mysql extension provides mysql_num_rows($result), while the mysqli extension provides mysqli_num_rows($result). These functions return an integer count of rows in the result set returned by the last query associated with that resource or object. Using these functions is efficient because they rely on metadata that the database engine already knows after executing the query.

Step-by-Step Solution:
Step 1: Execute your SELECT query using functions such as mysql_query() or mysqli_query(), capturing the result into a variable, for example $result. Step 2: If you are using the mysql extension, call mysql_num_rows($result). If you are using the mysqli extension, call mysqli_num_rows($result). Both functions return the number of rows in the result set. Step 3: Use the returned integer to control program logic, such as displaying a message when zero rows are found or calculating the number of pages for pagination. Step 4: Remember that mysql_* functions are deprecated and removed in newer PHP versions; modern code should use mysqli or PDO, where row counts are obtained with extension specific methods. Step 5: Do not confuse the number of rows in a result set with the number of affected rows in insert or update operations, which are obtained with different functions such as mysqli_affected_rows(). Step 6: This sequence shows that using mysql_num_rows() or mysqli_num_rows() is the correct approach to find the row count of a SELECT result.

Verification / Alternative check:
You can verify this by running a simple script that queries a table with a known number of matching rows and then prints the value returned by mysql_num_rows() or mysqli_num_rows(). The printed number should match the expected count. Changing the query condition to narrow or broaden the result set will change the value accordingly, confirming that these functions operate on the result set, not on the raw SQL string or the physical table size.

Why Other Options Are Wrong:
Option b is wrong because count() on the SQL string measures the length of the string or the number of elements in an array, not the number of rows returned by the database. Option c is incorrect because inspecting table files on disk is not how application level code determines row counts and would be highly unreliable. Option d is wrong because there is no global ROW_COUNT constant set by PHP; row counts are associated with specific result sets or connections.

Common Pitfalls:
A common pitfall is to call mysql_num_rows() or mysqli_num_rows() on result sets of non SELECT queries, which can fail or return zero. Another is to rely on row count for large result sets when you really need a separate COUNT(*) query for performance reasons. With modern extensions such as PDO, developers must also remember that rowCount() may behave differently with SELECT statements depending on the driver. However, for basic MySQL usage with mysql or mysqli, mysql_num_rows() and mysqli_num_rows() remain the standard answer for counting rows in a SELECT result.

Final Answer:
You find the number of rows in a MySQL result set in PHP by calling mysql_num_rows() or mysqli_num_rows() on the result resource or result object returned by your SELECT query.