Page 1 of 1
upper/lower case[solved]
Posted: Sun Aug 15, 2004 2:24 pm
by tim
Well I am thinking of the best/most efficient method to accomplish this.
the task: I have a script that you can sign-up for, lets say u sign-up with the username tim, all lowercase, you can sign-up with Tim and it would be considered a valid username. I dont want this, no duplicate names of anykind.
so tim, Tim, TiM, etc are all no good. Tim, Timmy, would be allowed tho
I use MySQL to store the names
I have some ideas but I would like others input on what they feel is the best method to get this done.
Posted: Sun Aug 15, 2004 4:12 pm
by markl999
If your column is, for example, a varchar(20) then just make it unique and mysql will handle it, i.e if you already have tim in there then it will class Tim, tIm etc as duplicates as mysql is case insensitive by default.
Posted: Sun Aug 15, 2004 4:32 pm
by qads
keep all usernames in lowercase, when a new user wants to signup, convert the username to lowercase and look for a match

.
Posted: Sun Aug 15, 2004 5:39 pm
by tim
qads that method is effective but I do want to allow users to have the choice of a upper n lowercase name.
Mark I never ever thought about going that route. So simple yet so far away for me to think of, lol.
Okay, how would you let users log-in? if they sign up with tim, the way I have it now if they type Tim and the correct password, they wont log-in, they must have every letter upper/lowercase as in the order they registered the name with.
I wish for a case-insenstive log-in, but when they do their name is in the format they signed up with, so I dont see converting them to lowercase n storing them as all the letters would have to be the same.
Sorry if I sound like a newbie, i never thought about using the field attributes as you suggested and hopefully you have another piece of good advice to save me a harsh hassel in the long run.
thank you
Posted: Sun Aug 15, 2004 5:46 pm
by markl999
if they sign up with tim, the way I have it now if they type Tim and the correct password, they wont log-in, they must have every letter upper/lowercase as in the order they registered the name with.
That's weird as that's not default behaviour. Eg. i just created a test table with id, name columns and put 'tim' in. Then a SELECT * FROM foo WHERE name='tIm' works fine (selects the row), as does TIM, TIm etc.. You have to use the mysql BINARY keyword to make it case sensitive.
Posted: Sun Aug 15, 2004 7:14 pm
by tim
heres my table
Code: Select all
CREATE TABLE `users` (
`username` varchar(15) default NULL,
`admin` tinyint(1) default '0',
`password` varchar(10) default NULL,
`email` varchar(35) default NULL,
`semail` tinyint(1) default '0',
`aim` varchar(35) default NULL,
`page` varchar(30) default NULL,
`about` varchar(110) default NULL,
UNIQUE KEY `username` (`username`)
)
this code is a snipplet of the verification:
Code: Select all
<?php
// SQL:
$sql = "SELECT * FROM users WHERE username='$username'";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
// Snipplet
elseif ($password == $row["password"] && $username == $row["username"]) {
$_SESSION['username'] = $_POST['username'];
?>
am I being dumb and missing something here?
I made another account, used TED as the username, when i try to login with ted w/ correct password, it dont recognize the name without it being all uppercase.
Posted: Mon Aug 16, 2004 3:44 am
by markl999
Ah. The mysql is probably pulling the row fine regardless of the case, but the PHP bit, $username == $row["username"] will be case sensitive.
Posted: Mon Aug 16, 2004 12:58 pm
by d3ad1ysp0rk
Have two columns. One holds the user-formatted name, one holds the lowercase one. When they register, you make another variable with the username in lowercase, make sure it doesn't exist already, then add all the info to the db. When they login, you check against the lowercase one, just incase someones name is IAmTheNeWmeMBeR and they forget where the uppercase letters go
BUt when you call on it to display it, use the formatted one. ie. logged in users, etc.
Posted: Mon Aug 16, 2004 4:04 pm
by feyd
[php_man]strcasecmp[/php_man]() for in php case insensitive matching.. and as for case insensitive matching in sql, I often use the LIKE directive, with making sure to escape the username for the wildcards, of course.
Posted: Mon Aug 16, 2004 4:59 pm
by pickle
ya, go with ~feyd - he beat me to it, so I agree with him

Posted: Mon Aug 16, 2004 5:34 pm
by tim
excellent suggestion feyd. I knew there was something better
Punk, that was my method to handle it. I'm glad I wasnt the only one to think of that, but theres always something better that the big gurus know.
thanks alot for the tips people
Update- i used feyd/pickles theory, it works great. That with combo of marks tweak in my SQL table, its 100%, thanks so much