I am trying to query an e-mail address stored in my database for log-in. I am having issues with the query in PHP, when I attempted the query with SQL in PHPMyAdmin it returns an empty set. After doing some testing I determined the following for an email of [email protected]:
Works:
SELECT * FROM `Careers` WHERE `Email` LIKE '%something%' and
SELECT * FROM `Careers` WHERE `Email` LIKE '%gmail.com%' and
SELECT * FROM `Careers` WHERE `Email` LIKE '%@%'.
Doesn't work:
SELECT * FROM `Careers` WHERE `Email` LIKE '[email protected]' and
SELECT * FROM `Careers` WHERE `Email` LIKE '%[email protected]%' and
SELECT * FROM `Careers` WHERE `Email` LIKE '%@gmail.com%'
SELECT * FROM `Careers` WHERE `Email` LIKE '%something%gmail.com'
I'm completely lost as to how to correct this. The only think I can think of is it is an issue with the @ sign as when I add the @ sign the query seems to fail. Any help you could provide would be greatly appreciated!
Are you sure that it's not working. See a proof here that it works http://sqlfiddle.com/#!9/26b00/4. But you should change your queries a bit as shown below
SELECT * FROM table1 WHERE `Email` = '[email protected]' -- No need of LIKE operator
SELECT * FROM table1 WHERE `Email` = '[email protected]' -- No need of LIKE operator
SELECT * FROM table1 WHERE `Email` LIKE '%@gmail.com' -- search before string
SELECT * FROM table1 WHERE `Email` LIKE 'something_gmail.com' -- search a single char
EDIT:
Per your latest comment your collation armscii8_general_ci
is the issue here. For example create the table like
CREATE TABLE Table1
(`email` varchar(19) collate armscii8_general_ci)
;
INSERT INTO Table1
(`email`)
VALUES
('[email protected]')
;
Do a select * ...
returns below; as you can see the .
as turned to ©
kind of copyright symbol and that's why the wildcard with LIKE
operator not working.
something@gmail©com
Change your query to use _
wilcard with LIKE
operator to match any single character and it will work fine. See http://sqlfiddle.com/#!9/ec46f/8
SELECT * FROM Table1 WHERE `Email` LIKE 'something@gmail_com';