CODEWITHSUNDEEP
phpmysql
ZioBit
ZioBit

Reputation: 965

MySQL query to find an exact match in ANY field of a table

I build a MySQL query at runtime with this

$search = "needle";
$sql = "SELECT * FROM myTable WHERE ( (1=0) ";
for($i=0; $i<count($names); ++$i) {
    $sql .= " OR (`{$names[$i]}` = '$search') ";
}
$sql .= ") ";

where names is an array with all the column names. The query seems fine to me:

SELECT * FROM myTable WHERE ( (1=0) OR (`img` = 'needle') OR (`title` = 'needle') OR (`name_en` = 'needle') OR (`caption_en` = 'needle') OR (`bottom_en` = 'needle') )

But the problem is... It finds A LOT of rows EVEN IF NONE OF THEM exactly contain the 'needle' string. Some of them contain it, but in the middle of a text field for example, but even the number of results doesn't make sense. Just four out of 103 contain the needle, but I get 60 rows back! And again, I was expecting ZERO rows, since NONE OF THEM are EQUAL to 'needle'.

Is there something stupid I am missing?

Upvotes: 1

Views: 270

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

Based on the comments above, it was revealed that one column in the table is an integer.

Comparing an integer to a string results in mapping the string to its integer value, i.e. any leading digits are used, and non-digits are ignored. If there are no leading digits, the integer value of the search string is 0.

Therefore it would match any row where the integer column is 0.

Upvotes: 2

Related Questions