Page 1 of 1

Case sensitivity problems[SOLVED]

Posted: Tue May 22, 2007 2:49 pm
by toby_c500
Hi,

I thought I had the answer to this one.

On my 'register your details' page I have a login name. When submitting a new users details to mysql, I find that you can use the same username with different caps. ie: Toby or toby.

I have this code to check if the user ids match and it is not throwing an error when different caps are used:

Code: Select all

$query = "SELECT * FROM members WHERE loginid = '".$loginid."'";
$result = mysql_query($query);
if (mysql_num_rows($result) > 0) {
        echo "<h1>Sorry</h1><br>That login name has been taken.";
        exit;
I thought about sending all usernames submitted as lower case using: strtolower(). But I can't seem to get this to work.

Are there any wildcards or other mysql querys I can use?

Thanks for helping.

Posted: Tue May 22, 2007 3:17 pm
by TheMoose

Code: Select all

$query = "SELECT * FROM members WHERE LOWER(loginid)='" . strtolower($loginid) . "'";

Posted: Tue May 22, 2007 5:25 pm
by pickle
You could also use the MySQL BINARY keyword to allow for multiple cases.

For example:

Code: Select all

+------+-----+
| id   | age |
+------+-----+
| toby | 12  |
| Toby | 50  |
+------+-----+

Code: Select all

SELECT
  age
FROM
 myTable
WHERE
 id = 'Toby'
LIMIT
 1
Will return '12'

Code: Select all

SELECT
 age
FROM
 myTable
WHERE
 BINARY id = 'Toby'
LIMIT
 1
Will return '50'

http://dev.mysql.com/doc/refman/5.0/en/ ... ry-op.html

Posted: Tue May 22, 2007 5:28 pm
by volka
pickle wrote:You could also use the MySQL BINARY keyword to allow for multiple cases.
That's the exact opposite of what toby_c500 is asking for ;)
Take a look at http://dev.mysql.com/doc/refman/5.0/en/ ... ivity.html

Posted: Tue May 22, 2007 5:42 pm
by pickle
volka wrote:That's the exact opposite of what toby_c500 is asking for ;)
I don't know if I'd say it's the exact opposite. He was looking for a way to stop improper matches due to case insensitivity. There are two ways to fix that problem: remove the possibility of different cases (such as what ~toby_c500 and ~TheMoose commented on), or becoming case sensitive, like what I suggested.

However, after re-reading my previous comment, I admit I was unclear. When I said to use BINARY to allow for multiple cases, I really meant you could use the keyword to allow for multiple usernames with the same letters but in different cases, ie: to allow for 'toby' and 'Toby' to both be unique, allowed usernames.

I blame English for allowing itself to be interpreted from what I wrote & not what I meant ;)

Re: Case sensitivity problems

Posted: Tue May 22, 2007 5:49 pm
by volka
pickle wrote:When I said to use BINARY to allow for multiple cases, I really meant you could use the keyword to allow for multiple usernames with the same letters but in different cases, ie: to allow for 'toby' and 'Toby' to both be unique, allowed usernames.
As far as I understood the question that is what's happeing right now and is not wanted.

Posted: Tue May 22, 2007 5:55 pm
by pickle
See, again English is being a jerk & allowing itself to be interpreted multiple ways.

I'm sure ~toby_c500 will speak up if he needs more info.

Posted: Wed May 23, 2007 11:26 am
by toby_c500
Thanks so much for the input guys.

What I want is NO usernames in my db to be the same REGARDLESS of there case. So would it be the right thing to do and send all data to mysql lowercase? That will sort the the problem out, but is there another way of querying the db so that it ignores the case and just looks for the spelling.

This also throws up another problem. When my users log in again, will they have to put there name in case sensitive?

I'm grateful of the help guys. I have not yet looked at the link that Volka mentioned so I will head that way now and hope that will sort it out.

Thanks again guys.

Posted: Wed May 23, 2007 11:50 am
by Wade
If you use the

Code: Select all

$query = "SELECT * FROM members WHERE LOWER(loginid)='" . strtolower($loginid) . "'";
as The Moose suggested it won't matter if it's all caps or mixed case. only the spelling will matter

Posted: Wed May 23, 2007 1:30 pm
by toby_c500
Nice one. Thank you peeps. You've been a great help.

Posted: Wed May 23, 2007 5:17 pm
by pickle
So you want there to be no chance of both a 'toby' username AND a 'Toby' username?

The link I posted above about the BINARY keyword shows examples of how simple queries are not case sensitive. So, you don't need to worry about lowercasing everything, as case doesn't matter.

If you've got the username 'toby' in the database already & a user is trying to sign up with 'Toby', just do the query:

Code: Select all

SELECT
 count(*) as `count'
FROM
 members
WHERE
 loginid = 'Toby'
That will return '1' & you don't have to worry about case & you can save yourself 2 function calls.