0

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?

user2950370
  • 117
  • 3
  • 13
  • That's not valid PHP, or is not valid SQL. Please show your actual code. – Jonnix Jun 15 '16 at 16:38
  • 1
    It's not returning `0`. it's returning boolean false because you have syntax errors in the sql, and obviously have no error handling at all to detect when your queries fail. php `false` becomes integer `0` in numeric contexts. – Marc B Jun 15 '16 at 16:50
  • Marc B, I think you've incorrectly marked this a duplicate. Bare with my whilst i try to clarify things – user2950370 Jun 15 '16 at 16:57
  • For clarification, this has nothing to do with back ticks, single or double quotes. I am fully aware of these – user2950370 Jun 15 '16 at 16:58
  • I've updated the original post. Hope it makes sense – user2950370 Jun 15 '16 at 17:18

1 Answers1

0
<?php

$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)
Progrock
  • 7,373
  • 1
  • 19
  • 25
  • Hi, appreciate your comment. I've tried the answer that you have given but it is still returning the first row when $search is a string. – user2950370 Jun 15 '16 at 16:52
  • My bad, mysql is most likely still trying to cast the string to an integer. Edited answer. See also: http://stackoverflow.com/a/6782019/3392762 – Progrock Jun 15 '16 at 16:54
  • @user2950370 is your string variable single quoted? Is this mysql or sql? 'id' in sql would literally mean the string id not a column – Matt Jun 15 '16 at 17:06
  • Hi guys, I've updated my original post. Hope it makes sense. Progrock, I'd understand if mysql is switching the variable type of a number.. but this isn't the case - i am passing a string of letters. Matt, I've cleared up the query – user2950370 Jun 15 '16 at 17:18
  • I have shown above that a string of letters can be cast to the value of 0 as above. Mysql ref: Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa. – Progrock Jun 15 '16 at 17:20
  • Prog, I've just realised that your edit is probably the correct answer. Bare with me while i take a look into it ! – user2950370 Jun 15 '16 at 17:21
  • More than likley $sql = "SELECT * FROM table WHERE `id` = '$search'"; should actually be $sql = "SELECT * FROM table WHERE id = '$search'"; because I am assuming you are not trying to compare the string "id" to "a-long-winded-string" but rather wanting to compare the value of the id column to the string. – Matt Jun 15 '16 at 17:53
  • I don't think the backticks surrounding field names in mysql will make any difference here. (Consistency might be good though.) – Progrock Jun 15 '16 at 18:11