CODEWITHSUNDEEP
phpmysql
Dylan Cross
Dylan Cross

Reputation: 5986

MySQL lowercase to compare data

I want to get the contents from a row in the database and compare the lowercase version of it to a lowercase version of a user imput to check if it exists in the database:

"SELECT `id` FROM `user_accounts` WHERE `username` = '".strtolower($username)."'"

How can i get username to be lowercase from mysql?

Upvotes: 11

Views: 38818

Answers (8)

Vi8L
Vi8L

Reputation: 988

Simply use:

SELECT `id`
FROM `user_accounts`
WHERE LOWER(`username`)='".strtolower($username)."'";

Or Use

SELECT `id` 
FROM `user_accounts` 
WHERE LCASE(`username`)='".strtolower($username)."'";

Both will work the same.

Upvotes: 3

PyromonkeyGG
PyromonkeyGG

Reputation: 79

Using one of the above queries should work just fine but CAUTION! If you have your username column indexed, using LOWER on it will make it so your query doesn't use that index.

Upvotes: 5

Jake
Jake

Reputation: 26137

As answered above, Ideally this should work, 

$query = "SELECT `id` 
          FROM `user_accounts` 
          WHERE LOWER(`username`) = '".strtolower($username)."'";

but it won't work if the "username" column in "user_accounts" table is defined as VARBINARY. The reason is VARBINARY requires the data to be case sensitive

Upvotes: 6

devsnd
devsnd

Reputation: 7722

You can use the following query to do so:

$myquery = "SELECT `id` FROM `user_accounts` WHERE
            LOWER(`username`) = '".strtolower($username)."'"

LOWER is the SQL function that converts all characters to lower case, just like PHP's strtolower

On a side note: You should escape the $username using mysql_real_escape_string to avoid possible sql injection at that point.

Upvotes: 1

Tamik Soziev
Tamik Soziev

Reputation: 14818

$query = "SELECT `id` 
          FROM `user_accounts` 
          WHERE LOWER(`username`) = '".strtolower($username)."'"

Upvotes: 2

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799560

If you really want case to not matter then you should set the collation of the column to be case-insensitive.

mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT 'foobar' = 'FoObAr';
+---------------------+
| 'foobar' = 'FoObAr' |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT 'fOoBaR' = 'FoObAr';
+---------------------+
| 'fOoBaR' = 'FoObAr' |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT 'fOoBaR' = 'FoObAz';
+---------------------+
| 'fOoBaR' = 'FoObAz' |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

Upvotes: 8

Selçuk
Selçuk

Reputation: 176

"SELECT `id` FROM `user_accounts` WHERE lower(`username`) = '".strtolower($username)."'"

Upvotes: -1

Andreas Wong
Andreas Wong

Reputation: 60594

You use lower(username) http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lower

"SELECT `id` FROM `user_accounts` WHERE LOWER(`username`) = '".strtolower($username)."'"

Upvotes: 30

Related Questions