How do I count query result rows?
When validating authentication credentials or checking for specific data existence, a common stumbling block is determining whether your SQL query returned any results without iterating through them unnecessarily. In PHP using the MySQLi extension, you might find yourself needing to know immediately if mysqli_query produced exactly one row, multiple rows, or zero rows. The most direct way to access this information is via the mysqli_num_rows() function.
However, applying this function correctly requires understanding how it interacts with the query result object and the internal state of the database connection. If you are seeing unexpected behavior where the check fails despite a row existing, it often stems from misunderstanding when the function should be called relative to fetching data or mixing up different API behaviors.
Checking the Row Count Immediately
The mysqli_num_rows() function returns an integer representing the number of rows in a result set. Crucially, this count is available immediately after executing the query and assigning the result to the $query variable. You do not need to fetch every single row first to know how many exist; the database driver maintains this metadata internally.
In your specific scenario, where you are checking if a user exists in the login table, you can wrap the logic inside an if statement right after the query execution:
$query = mysqli_query($con, "SELECT * FROM login WHERE usuario = '$usuario' AND senha='$senha'");
if (mysqli_num_rows($query) > 0) {
// There is at least one matching row.
// Proceed to fetch and validate the data.
while ($row = mysqli_fetch_assoc($query)) {
$db_username = $row['usuario'];
$db_password = $row['senha'];
}
if ($usuario == $db_username && $senha == $db_password) {
return true;
}
} else {
// No matching rows found.
return false;
}
By placing mysqli_num_rows($query) in the condition, you avoid entering the while loop if no records match the criteria. This prevents unnecessary database overhead and simplifies the logic flow. If num_rows returns 0, the code skips directly to returning false.
Trade-offs with Memory and Performance
While checking num_rows is efficient for determining existence, it does not inherently limit memory usage if you proceed to iterate through the entire result set regardless of size. For simple queries like authentication checks involving small tables, this distinction is negligible. However, in high-traffic applications where a query might theoretically return thousands of rows (e.g., fetching all logs or large datasets), iterating unnecessarily can become a bottleneck.
A more robust approach for scenarios where you only need to know "does this exist?" rather than "what are the values?" is to modify your SQL query to explicitly count the results using COUNT(*) and return that value directly:
$countQuery = mysqli_query($con, "SELECT COUNT(*) as total FROM login WHERE usuario = '$usuario' AND senha='$senha'");
$row = mysqli_fetch_assoc($countQuery);
if ($row['total'] > 0) {
return true;
}
return false;
This method ensures that the database engine handles the aggregation, and your PHP script receives a single row representing the count. This is often safer when dealing with large datasets because you avoid fetching unnecessary columns or rows into memory if you don't need them.
Security Considerations
It is vital to note that the provided source code snippet uses string interpolation for user input ('$usuario' and '$senha') directly within the SQL query string. This approach is vulnerable to SQL injection attacks. An attacker could manipulate these variables to alter the query logic or access unauthorized data.
To ensure safety, you must utilize prepared statements. The mysqli_stmt_prepare() function allows you to separate the SQL structure from the data, ensuring that user input is treated strictly as data and never as executable code. This practice is mandatory for any production environment handling authentication logic.