Can anyone please help me to understand the MySQL select query behavior and help me to solve this,
How to show no results found message based on user search?
CREATE TABLE `dummytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mobile` int(11) NOT NULL,
`welcome` varchar(150) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
SELECT * FROM `dummytable` WHERE `mobile` = '\'\'' LIMIT 50
mobile is declared as an integer. When MySQL compares an integer to a string, then it converts the string to a number -- not the other way around.
How does MySQL do this? It converts the leading numeric characters to a number. Your condition is:
WHERE mobile = ''''
(The traditional SQL way to put a single quote in a string is to double it up.)
The single quote is not a numeric character, so the conversion results in
0. And hence, that is the result that you get.