Case sensitivity problems[SOLVED]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
toby_c500
Forum Commoner
Posts: 50
Joined: Fri May 11, 2007 11:29 am
Location: Leeds, England

Case sensitivity problems[SOLVED]

Post 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.
Last edited by toby_c500 on Wed May 23, 2007 1:33 pm, edited 1 time in total.
User avatar
TheMoose
Forum Contributor
Posts: 351
Joined: Tue May 23, 2006 10:42 am

Post by TheMoose »

Code: Select all

$query = "SELECT * FROM members WHERE LOWER(loginid)='" . strtolower($loginid) . "'";
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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 ;)
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Re: Case sensitivity problems

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
toby_c500
Forum Commoner
Posts: 50
Joined: Fri May 11, 2007 11:29 am
Location: Leeds, England

Post 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.
Wade
Forum Commoner
Posts: 41
Joined: Mon Dec 18, 2006 10:21 am
Location: Calgary, AB, Canada

Post 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
toby_c500
Forum Commoner
Posts: 50
Joined: Fri May 11, 2007 11:29 am
Location: Leeds, England

Post by toby_c500 »

Nice one. Thank you peeps. You've been a great help.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply