使用PHP将MySQL字段强制为小写

Question

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!

Answer 1

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.