php - Sql where clause not working

Question

SQL where clause is not working in my database.

I have a table called "sites" and structure like that

id     site
1      xyz.com
2      google.com
3      example.com

I am running this SQL query

SELECT * FROM `sites` WHERE `site` = "google.com";

But I am getting this output

 MySQL returned an empty result set (i.e. zero rows). (Query took 0.0009 sec)

I never see before like that in my life.

Update: Screenshot

I do not want to apply this query in project.

SELECT * FROM `sites` WHERE `site` LIKE "%google.com%";

#

---

The real problem was in insert commands on creation of DB. Try

INSERT INTO sites (id, site) VALUES (1, '
xyz.com
'), (2, '
google.com
'), (3, '
example.com
')

and manually check records in the table. You would not see line breaks. This is an issue in SQL I've noticed.

Answer 1

UPDATE: OP had invisible newline characters ( ) in his dataset. @EternalPoster (and I) supposed that Trim would remove all whitespace, but MySql Trim Documentation specifies leading & trailing spaces only.

---

This is what I did:

-- for http://stackoverflow.com/questions/27203169/sql-query-not-work-for-google-com
-- and http://stackoverflow.com/questions/27202157/sql-where-clause-not-working

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- --------------------------------------------------------

DROP TABLE IF EXISTS `sites`;

--
--  structure for table `sites`
--
CREATE TABLE IF NOT EXISTS `sites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- data for table `sites`
--
INSERT INTO `sites` (`id`, `site`) VALUES
(1, 'xyz.com'),
(2, 'google.com'),
(3, 'example.com');

--
-- select google
--
SELECT * 
FROM sites 
WHERE site = 'google.com'
;

--
-- select google
--
SELECT * 
FROM sites 
WHERE site = 'google.com'
;

and this is what I got:

So in my case, I see the script functioning as expected.

What's different about your case? My installation is a fairly default setup. The fact that Like '%google.com%' works on your dataset suggests a couple things. Folks have already suggested TRIM, because the Like expression would match invisible characters (spaces, tabs, backspaces, nulls). MySQL has a separate operator REGEXP for regular expressions, so it wouldn't seem to be that the . character is being used as a wildcard, but that might be worth a look.

Create an empty database and try running my script above. Do you get the same result I do?