CODEWITHSUNDEEP
mysqlselectvarchar
dev21
dev21

Reputation: 580

mysql select not woking for varchar field

I have table with email field of type varchar. 

select * 
from `table_name` 
WHERE email='some email'

working well for all the other email except one email in the table.

Upvotes: 3

Views: 14481

Answers (4)

being_ethereal
being_ethereal

Reputation: 885

Encountered same error for just one email id in mysql 5.7.12. Tried updating/trimming and whatnot, still no luck.

Changed the charset, which resolved the issue:

ALTER TABLE table_name 
CHANGE COLUMN email email VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NULL DEFAULT NULL ;

Upvotes: 0

Alejandro De Castro
Alejandro De Castro

Reputation: 2257

In my case, the problem was that when passing the variable in the code, it also left blank spaces between the double quotes and the variable, when removing this, it was not necessary to put the % character

This is my incorrect code, look at the blanks before and after the variable:

$resul = $mysqli->query("SELECT * FROM `people` WHERE `user` = ' ".$user." '");

This is the correct way:

$resul = $mysqli->query("SELECT * FROM `people` WHERE `user` = '".$user."'");

removing these spaces you won't need to use the wildcards%

I hope this answer helps you.

Upvotes: 0

black
black

Reputation: 745

may be you have blankspace in your email. you can check the length of the email.

SELECT length(email) 
FROM  `table_name`
WHERE  `email` LIKE  '%email%'

use wildcard

select * from table_name 
where email like '%some email%'

or visit here for more combination http://www.w3schools.com/sql/sql_wildcards.asp

Upvotes: 0

juergen d
juergen d

Reputation: 204904

When this works for your problematic record

select * from table_name 
where email like '%some email%'

Then you have leading or trailing spaces in your data.

To revert that update your existing table data like this

update table_name
set email = trim(email)
where email like '%some email%'

Upvotes: 9

Related Questions