Why Checking mysql_query Results Is a Red Flag
When you see code checking if mysql_query() returned results, it is usually a sign that the application is using the legacy MySQL extension. This API was deprecated in PHP 5.5 and removed entirely in PHP 7.0. While this post addresses the specific logic of your question regarding the old mysql_* functions, relying on them introduces significant security vulnerabilities (specifically SQL injection) and compatibility issues that make modern development impossible.
However, understanding how to check for results in the legacy API helps clarify why we moved away from it entirely. The confusion often stems from mixing up two distinct concepts: whether a query executed successfully versus whether the query returned data rows.
Checking Execution vs. Data Existence
Your first example checks if the result resource is false:
if (!$result) {
// Perform action
}
In the legacy mysql_* API, this condition specifically detects errors. If a SQL syntax error occurs, the connection times out, or a constraint violation happens, mysql_query() returns false. In these cases, no result resource exists to iterate over. This is the correct way to handle execution failures.
Your second example attempts to check for data:
if (mysql_num_rows($result) == 0) {
// Perform action
}
This is technically correct for verifying that a SELECT query returned zero rows. However, there is a critical pitfall here. If the previous mysql_query() call failed (returned false), $result does not exist as a resource. Calling mysql_num_rows() on a non-existent variable will trigger a fatal "undefined variable" error before you even get to the logic inside the if statement. You must always check for execution errors first.
The Logical Flaw in Fetching
Your third approach suggests using list($total) = mysql_fetch_row($result);:
list($total) = mysql_fetch_row($result);
if ($total == 0) {
// Perform action
}
This method is fundamentally flawed and dangerous. The function mysql_fetch_row() returns a false value if there are no rows to fetch or if the query failed. While checking false works for detecting existence, assigning it to $total and checking if ($total == 0) fails in two scenarios:
- Empty Strings: If your database column contains an actual empty string (
''), this is falsy but not equal to zero depending on context, though PHP often treats them similarly. - Zero Values: If a valid row exists where the specific column value is actually
0, the condition$total == 0evaluates to true, causing your logic to incorrectly assume no results were returned.
Furthermore, this approach fetches the first row just to check if it exists. This mixes data retrieval with control flow logic, which violates separation of concerns and makes debugging difficult.
The Modern Solution: PDO and MySQLi
The real answer to "what is the best way" is to stop using mysql_query altogether. These functions are gone. In modern PHP applications, you should use either the MySQLi extension or, preferably, PDO (PHP Data Objects).
With PDO, the error checking logic becomes much cleaner because you separate the execution from the data fetching:
$stmt = $pdo->query("SELECT * FROM users WHERE username = :username", ['username' => $user]);
$count = $stmt->rowCount();
if ($count === 0) {
// No results returned, safe to insert or proceed
}
Here, rowCount() returns an integer (0 if no rows matched), never a boolean false, so you don't have to worry about checking for false first unless you suspect a syntax error. If you need to know if an error occurred during execution, PDO throws a PDOException which is caught by your standard try-catch blocks, providing a much more robust error handling model than the legacy API's simple boolean return value.
If you are maintaining legacy code that forces you to use mysql_query, strictly adhere to this pattern: first check if $result is false to catch execution errors, and only then call mysql_num_rows() to check data existence. But seriously, migrate your stack immediately.
