auto generate username in php

Question

I want to auto-generate username on the basis of first name and last name. For example, If `firstname = John` and `lastname = Tory` then username should be `johntory`. But, If that username already exist in database the I want to increment by 1 that means with same name next username will be `johntory2`. I have something like this in `PHP`.. ``` foreach($existing_users as $u){ if($u['username'] != $new_generated_username){ // Enter it directly... } else{ // increment count value... } } ``` But, there is a problem, if username is like `johntory5` or something... Is there any way where I can directly update within `INSERT` statement in mysql by checking everything. This is SQLFiddle

Accepted Answer

You can easily do this directly in the INSERT statement like this:

```
 INSERT INTO User (username, first, last) 
 SELECT CONCAT('johntory', COUNT(id)), 'John', 'Tory' 
 FROM User WHERE username LIKE 'johntory%';
```

Or if you don't like the preceding zero, you could use this:

```
 INSERT INTO User (username, first, last) 
 SELECT CONCAT('johntory', CASE WHEN COUNT(id) = 0 THEN '' ELSE COUNT(id) END), 'John', 'Tory' 
 FROM User WHERE username LIKE 'johntory%';
```

This is very fast and efficient. It uses a subquery to prepare the values and then inserts them.

Also, this is going to be many times faster than any of the PHP solutions offered here, especially if you are dealing with billions of rows.

Personally, I love the simplicity and performance of MySql

Enjoy!

~Techdude

auto generate username in php

Solution

Problem