user2950370 user2950370 - 1 year ago 63
SQL Question

SQL query returns first row in TABLE when WHERE clause is false

For anyone reading this: The already answered link above has nothing to do with the question.

The following query should return a result when the condition is true or an empty array when the operator fails.

(Bare in mind, I actively want this query to fail and return nothing).

$search = 'a-long-winded-string';
$sql = "SELECT * FROM table WHERE `id` = '$search'";

The problem here is that it returns the correct row on success but on failure always defaults to the first row in the table (with an id of 0). Changing the first rows ID away from 0 remedies this.

My assumption here is that the query fails and the boolean result gets queried.

Is there an explanation as to why the failed query returns a row with an ID of 0?

Answer Source

$search = 'string';
$sql = "SELECT * FROM foo WHERE `id` = '$search'";

// execute query...

Your values should be quoted if they are not numeric.

Basically your search string is evaluating to 0 as id is most likely an integer column.

mysql> select cast('foo' AS UNSIGNED);
| cast('foo' AS UNSIGNED) |
|                       0 |
1 row in set, 1 warning (0.00 sec)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download