Page 1 of 2
Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 6:36 pm
by JAB Creations
I'm interested in preventing
like user names during the registration process of the PHP/MySQL script that I've been working on for some time now.
In example "JAB Creations" and "jabcreations" are essentially the same thing though I want to prevent this to preserve the uniqueness of each user name.
I've considered creating a new case sensitive column to store the letter casing and spaces in a user name separate of the base user name.
So in example if I registered as "JAB Creations" that user name would be stored in the case sensitive/space allowed column. Additionally I'd use PHP to set all the letters to lower case and strip the spaces storing it in a sort of "
base" user name column. The case sensitive user names are restricted to alphanumeric character and spaces.
I'm interested in thoughts of improvements or different approaches to this goal.

Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 6:42 pm
by VladSun
Levenshtein distance ...
PS:
I have modified it like this:
Code: Select all
$levenshtein_distance = Levenshtein_distance($word1, $word2);
$similarity = (200*$levenshtein_distance)/(strlen($word1) + strlen($word2));
$similarity = $similarity > 100 ? 100 : $similarity; // similarity in percentage
Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 7:34 pm
by JAB Creations
Hahaha that's awesomely way deeper a response than I anticipated!
Let's say I did something like that...what would I be comparing it to? I think this might be taking it to a deeper end of the pool than what I really want to do...not entirely sure though. I don't mind if there are two users such as "Ltron123" and "MTron123" in example.
Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 8:46 pm
by allspiritseve
Your requirements sound simple enough that you could just strip out spaces, dashes, underscores, etc. and do a case-insensitive comparison between existing usernames and a potential username when someone signs up. You wouldn't even have to store a separate column in that case, just make a function that gives you the base name.
Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 8:57 pm
by VladSun
++allspiritseve
@Jab: I didn't expect your question was so easy

Follow
allspiritseve advice and ... I think you should post questions that are really hard to answer by yourslef. That one was not one of those ... I know (so so) how much experienced you are and I think you should had started using your experience to solve your problems by yourself

You know enough of PHP/SQL base functionality to do that

Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 9:02 pm
by JAB Creations
I think what I'm stuck on is if you sign up as "jabcreations" then how would I determine it's used if the user name "JAB Creations" exists in the database.
My point is simple: removing the white space from "JAB Creations" and doing a query against "jabcreations" in the database is easy however I'm not sure how to do the reverse. Maybe I missed something though...

Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 9:05 pm
by JAB Creations
To add to that then...is there a way to query MySQL records stripping white space and then seeing if a row matches?
So you register as "jabcreations"...and then do a query...a record "JAB Creations" exists...but can we have MySQL first make all the letters lower-case and remove white space....then determine if it's a match?
Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 9:07 pm
by allspiritseve
Code: Select all
$stmt = $db->exec ('SELECT username FROM users');
$users = $stmt->fetchAll (PDO::FETCH_ASSOC);
foreach ($users as $user):
if (your_strip_function ($_POST['username']) == your_strip_function ($user)):
$match = true; break;
endif;
endforeach;
...for example.
Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 9:07 pm
by VladSun
Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 9:10 pm
by allspiritseve
JAB Creations wrote:To add to that then...is there a way to query MySQL records stripping white space and then seeing if a row matches?
Not sure... maybe you could check here:
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
And see if something might work. I noticed there was a regex function... maybe that would work for you.
Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 9:11 pm
by allspiritseve
Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 9:53 pm
by JAB Creations
Thanks to both of you, I'm half way in theory then!
Looks like
LCASE() is one of the two string functions I'm looking for then.
However I only see
trim() and it only removes starting and ending whitespace. Are there any other functions not listed here that would remove all white spaces? Google isn't being very helpful with this particular question.

Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 9:54 pm
by allspiritseve
I think that REGEX function is your best bet. I know almost nothing about regular expressions though, so you'd have to google it.
Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 10:06 pm
by JAB Creations
Regex on a MySQL query...can we add the presumption that the table of users could grow in to the six or seven figure at some point...I don't think this would scale nicely as far as load is concerned!

Re: Preventing like user names: your suggestions please.
Posted: Sun Sep 14, 2008 10:10 pm
by allspiritseve
If that's the case, maybe you are better off with another column in your table.