CODEWITHSUNDEEP
phpmysqluppercaselowercase
Brenden Clerget
Brenden Clerget

Reputation: 137

Force MySQL field to lowercase with PHP

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!

Upvotes: 1

Views: 3148

Answers (3)

bfavaretto
bfavaretto

Reputation: 71939

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.

Upvotes: 3

Conrad Shultz
Conrad Shultz

Reputation: 8808

Uh... you realize that email addresses are case sensitive, right? From RFC 2821:

Verbs and argument values (e.g., "TO:" or "to:" in the RCPT command
and extension name keywords) are not case sensitive, with the sole
exception in this specification of a mailbox local-part (SMTP
Extensions may explicitly specify case-sensitive elements). That is, a command verb, an argument value other than a mailbox local-part,
and free form text MAY be encoded in upper case, lower case, or any
mixture of upper and lower case with no impact on its meaning. This
is NOT true of a mailbox local-part. The local-part of a mailbox
MUST BE treated as case sensitive. Therefore, SMTP implementations
MUST take care to preserve the case of mailbox local-parts. Mailbox
domains are not case sensitive. In particular, for some hosts the
user "smith" is different from the user "Smith". However, exploiting the case sensitivity of mailbox local-parts impedes interoperability
and is discouraged.

(emphasis added)

Upvotes: 4

ceejayoz
ceejayoz

Reputation: 180176

If you do WHERE buyer_email LIKE '...' it'll by default do a case-insensitive match.

With e-mail fields, though, I prefer to lowercase the e-mail address when I insert it into the DB.

Upvotes: -1

Related Questions