Preventing like user names: your suggestions please.

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Preventing like user names: your suggestions please.

Post 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. :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Preventing like user names: your suggestions please.

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Preventing like user names: your suggestions please.

Post 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.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Preventing like user names: your suggestions please.

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Preventing like user names: your suggestions please.

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Preventing like user names: your suggestions please.

Post 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... :|
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Preventing like user names: your suggestions please.

Post 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?
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Preventing like user names: your suggestions please.

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Preventing like user names: your suggestions please.

Post by VladSun »

As always ... Google is your firend ;)

http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Preventing like user names: your suggestions please.

Post 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.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Preventing like user names: your suggestions please.

Post by allspiritseve »

VladSun wrote:As always ... Google is your firend ;)

http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
You beat me to it!
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Preventing like user names: your suggestions please.

Post 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. :|
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Preventing like user names: your suggestions please.

Post 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.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Preventing like user names: your suggestions please.

Post 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! 8O
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Preventing like user names: your suggestions please.

Post by allspiritseve »

If that's the case, maybe you are better off with another column in your table.
Post Reply