Member Avatar for magicmarkuk

Hi

I am running the following SQL (extract shown below)

INSERT INTO users
    (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

Mark

  • 2 Contributors
  • 1 Reply
  • 227 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.