Duplicate entries

[jaymc]

I have a username field in my database that is set to binary, so basically, Jamie is not the same as jamie

So in their their may be the following usernames

Jamie
jamie
JaMiE

What I want to do is query the database and have it return all the duplicate entries ignoring the rule of character binary

Any ideas

[btherl]

You might be able to use [code=php:0]SELECT * FROM users WHERE username ILIKE 'jamie'[/code]

ILIKE is case-insensitive.

Another option is to compare [code=php:0]WHERE LOWER(username) = 'jamie'[/code]

[jaymc]

Hmmm

Basically, I'll explain in a bit more detail what I want to achieve

I have a 100,000 row member database, so basically 100,000 usernames

I want to change the username field to ASCI so usernames are not case sensitive

I cant do that until I remove/rename duplicate usernames

I tried dumping every username from a loop into an array then for each username see if it already exists in the array, if it did then I know its a duplicate entry

But because their is 100,000 rows the script takes a while and dies at 60 seconds for max execution time

[fenway]

In general, MySQL is not case-sensitive -- do you simply want to toss out the duplicates?

[jaymc]

It is case sensitive because ive set the username field to BINARY

I dont want to toss out the dupilicates, I basically want to turn the following

jamie
JAMIE
Jamie

into this

jamie
JAMIE01
Jamie02

[fenway]

Interesting... I'm going to see what I can come up with.  As a quick hack, just populate a temporary table with the lowercased usernames & counts, and then simply issue a multi-table update, and "skip" the first one of each username with an appropriate limit clause, and concatenate in the lpadded-number.