I am running the following SQL (extract shown below)

    (user_id, username, username_clean)
    SELECT member_id, username, lower(REPLACE(username, '.', '_'))
FROM members

The problem arises when lower(REPLACE(username, '.', '_')) produces a duplicate that already exists in username_clean as the users table does not permit duplicates.

Is there a SQL or PHP solution to append a suffix such as 1,2,3 etc after such a duplicate so that they do not exist in the users table?

I could do this manually but as I want to run this routine many times this is not a solution.

Many thanks in advance


Shouldn't you do such a check before inserting? I know the problem could still arise then, but it would be much cleaner to let the user select a different name. I know most would prefer that over having a number attached without knowing.

If you are stuck with this logic, then you could select all matching username_clean, but you'd have to create a check loop somewhere to determine the highest used number (either in PHP or SQL), because ORDER BY would not get the right result.