I have a database with an email field, and it cycles through the database to grab all the transactions concerning a certain email address.
Users putting in lowercase letters when their email is stored with a couple capitals is causing it not to show their transactions. When I modify it to match perfect case with the other emails, it works.
How can I modify this so that it correctly compares with the email field and case doesn't matter? Is it going to be in changing how the email gets stored?
$result = mysql_query("SELECT * FROM `example_orders` WHERE `buyer_email`='$useremail';") or die(mysql_error());
Thanks ahead of time!
A mixed PHP/MySQL solution:
$result = mysql_query("
SELECT *
FROM example_orders
WHERE LOWER(buyer_email) = '" . strtolower($useremail) . "';
") or die(mysql_error());
What it does is converting both sides of the comparison to lowercase. This is not very efficient, because the use of LOWER
will prevent MySQL from using indexes for searching.
A more efficient, pure SQL solution:
$result = mysql_query("
SELECT *
FROM example_orders
WHERE buyer_email = '$useremail' COLLATE utf8_general_ci;
") or die(mysql_error());
In this case, we are forcing the use of a case-insensitive collation for the comparison. You wouldn't need that if the column had a case-insensitive collation in the first place.
Here is how to change the column collation, as suggested by Basti in a comment:
ALTER TABLE `example_orders`
CHANGE `buyer_email` `buyer_email` VARCHAR( 100 )
CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
If you choose to do that, you can run the query without COLLATE utf8_general_ci
.