CODEWITHSUNDEEP
phpmysql
Naira 4Dollar
Naira 4Dollar

Reputation: 31

Mysql search not searching with the whole string

My mysql search doesn't search with the whole string. my query

 $query_details= "SELECT * FROM Purchases WHERE sn= '$phone' OR sentto= 
'$phone' OR batch= '$phone' ";
$details= $offices->query($query_details) or die(mysqli_error($xxxx));
$row_details = mysqli_fetch_assoc($details);

when I search with something like '19Pa5vP9MyGoYoaB23kRNsHGjaRvYUHD' it rather brings our result for row with sn = '19'.

Please I need it to search with the whole string not just part of it.

Thanks.

Upvotes: 0

Views: 339

Answers (1)

Nick
Nick

Reputation: 147146

If your column is defined as INT, then MySQL will try to convert the comparison string to an integer too. Since it starts with a number, MySQL will extract all the leading digits from the string (in this case 19), convert that to an INT and then do the comparison. Hence you get rows with sn = 19 returned. For example: 

SELECT 19 = '19c5bx'

Output

1 -- true

To get around this, CAST any integer columns as CHAR before comparison e.g.

SELECT CAST(19 AS CHAR) = '19c5bx'

Output

0 -- false

This will still work fine if you want to compare to an integer instead of a string e.g.

SELECT CAST(19 AS CHAR) = 19

Output

1

Demo on dbfiddle

So something like this should work (cast the other columns as CHAR too if they are also INT in your table):

$query_details= "SELECT * 
                 FROM Purchases 
                 WHERE CAST(sn AS CHAR) = '$phone' 
                    OR sentto = '$phone' 
                    OR batch= '$phone' ";

Upvotes: 1

Related Questions