0

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 something@gmail.com:

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 'something@gmail.com' and

SELECT * FROM `Careers` WHERE `Email` LIKE '%something@gmail.com%' 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!

Dharman
  • 30,962
  • 25
  • 85
  • 135
NMaduro
  • 21
  • 1
  • 1
  • 12
  • Do you confirm the last line of 'Works' and of 'Doesn't work' ? Because the last line of Works contains an '@' when the last line of Doesn't Work does not contain it. – Laurent Crivello Jun 24 '15 at 15:32
  • @LaurentCrivello Yes, I have executed each of these queries on my database. I'm not sure why the last 'Doesn't Work' fails and the last line of 'works' does. – NMaduro Jun 24 '15 at 15:37
  • 1
    @Nmaduro i am not able to recreate your issue [link]http://sqlfiddle.com/#!9/e9e21/3 – Sachu Jun 24 '15 at 15:39
  • have you tried escaping the @ sign e.g. \@ I'm more familiar with Oracle or SQLServer syntax so not 100% sure – kayakpim Jun 24 '15 at 15:41
  • Perhaps: http://stackoverflow.com/questions/7230068/at-sign-in-sql-statement-before-column-name in oracle set define off will turn off the user defined nature of this. Perhaps MySQL has a default setting which was changed to no longer ignore @ as user variables. – xQbert Jun 24 '15 at 15:51

2 Answers2

5

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` = 'something@gmail.com' -- No need of LIKE operator

SELECT * FROM table1 WHERE `Email` = 'something@gmail.com' -- 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
    ('something@gmail.com')
;

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';
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • @Sachu, Yes and even I have made the same statement with a fiddle in answer but when searching for entire string literal there is no need of LIKE operator and that's what a, trying to point here. – Rahul Jun 24 '15 at 15:51
  • oh ok..sorry for not checking the fiddle :) – Sachu Jun 24 '15 at 15:55
  • @Sachu maybe it is something with how the database is collated? I'm using armscii8_general_ci. Fiddle doesn't state what collation is used. Any idea? – NMaduro Jun 24 '15 at 16:39
  • @NMaduro, Yes that's the issue here; see edit in answer if that helps. – Rahul Jun 24 '15 at 17:45
  • @Sachu What would you recommend as the collation I use? Thank you so much, by the way! This has been driving me crazy for weeks! Lol – NMaduro Jun 24 '15 at 18:13
  • @NMaduro above answer solved the issue?in your question u said `SELECT * FROM Careers WHERE Email LIKE '%gmail.com%' and` is working if this is the case it wont select any data – Sachu Jun 24 '15 at 23:40
  • Sorry @Sachu just now had the time to get home and test before selecting your answer as the correct one. Thanks for helping me out with this. I ended up using UTF8_general_ci as the collation. – NMaduro Jun 25 '15 at 15:20
  • @NMaduro, from yesterday you are bit confused, it's not Sachu; Sachu made his comment in my answer ... LOL ... funny :) – Rahul Jun 25 '15 at 15:29
1

Use the Regex force! Just a where statement to solve this:

select * from site_form
where email REGEXP '^[A-Za-z0-9._%\-+!#$&/=?^|~]+@[A-Za-z0-9.-]+[.][A-Za-z]+$';
Renato Fagalde
  • 36
  • 2