Allan Vester Allan Vester - 7 months ago 11
SQL Question

How do I get first name, middle name and last name as username with a dot in between the names and a number at the end for a unique username?

I'm working on a project, but I would like to know how I get the first name, middle name and last name as the username with a dot in between their full names and a number at the end for a unique username? In MySQL. Just like Facebook's system. I also want so if the middle name is NULL, then it doesn't like a space in the middle.

I have tried a lot of different things, but nothing worked yet.

SELECT COALESCE(CONCAT(first_name, '.', COALESCE(CONCAT(middle_name, '.', last_name),
first_name,middle_name,last_name)), middle_name,'.', COALESCE(CONCAT(first_name, '.',
last_name),first_name,last_name)) AS username FROM users


That is how close I have come, but it has a problem if they don't have a middle name, it will display two dots instead of one. But I still need something to get the first name, middle name and last name as the username with a dot in between their full names and a number at the end for a unique username.

Answer

Okay I got it to work with my setup and with Laravel.

if (Auth::user()->original_middle_name === NULL) {

        $affected = DB::update('UPDATE users SET original_first_name = CAP_FIRST(original_first_name) WHERE id = ?', [Auth::user()->id]);
        $affected = DB::update('UPDATE users SET original_middle_name = CAP_FIRST(original_middle_name) WHERE id = ?', [Auth::user()->id]);
        $affected = DB::update('UPDATE users SET original_last_name = CAP_FIRST(original_last_name) WHERE id = ?', [Auth::user()->id]);

        $affected = DB::update("UPDATE users SET updated_first_name = original_first_name WHERE id = ?", [Auth::user()->id]);
        $affected = DB::update("UPDATE users SET updated_middle_name = original_middle_name WHERE id = ?", [Auth::user()->id]);
        $affected = DB::update("UPDATE users SET updated_last_name = original_last_name WHERE id = ?", [Auth::user()->id]);

        $affected = DB::update("UPDATE users SET original_middle_name = CASE original_middle_name WHEN '' THEN NULL ELSE original_middle_name END WHERE id = ?", [Auth::user()->id]);
        $affected = DB::update("UPDATE users SET updated_middle_name = CASE updated_middle_name WHEN '' THEN NULL ELSE updated_middle_name END WHERE id = ?", [Auth::user()->id]);

        $affected = DB::update("UPDATE users a INNER JOIN (SELECT original_first_name, original_middle_name, original_last_name, COUNT(*)-1 AS duplicatedusernameid FROM users GROUP BY original_first_name, original_middle_name, original_last_name HAVING COUNT(*) > 1) b ON a.original_first_name = b.original_first_name AND a.original_last_name = b.original_last_name SET a.duplicatedusernameid = b.duplicatedusernameid WHERE a.id = ?", [Auth::user()->id]);

        $affected = DB::update("UPDATE users SET duplicatedusernameid = (CASE WHEN duplicatedusernameid = 0 THEN NULL ELSE duplicatedusernameid END) WHERE id = ?", [Auth::user()->id]);
        $affected = DB::update("UPDATE users SET duplicatedusernameid = (CASE WHEN duplicatedusernameid = '' THEN NULL ELSE duplicatedusernameid END) WHERE id = ?", [Auth::user()->id]);

        $affected = DB::update("UPDATE users SET username = CONCAT_WS('.', COALESCE(original_first_name, ''), original_middle_name, COALESCE(original_last_name, ''), duplicatedusernameid) WHERE id = ?", [Auth::user()->id]);

    } else {

        $affected = DB::update('UPDATE users SET original_first_name = CAP_FIRST(original_first_name) WHERE id = ?', [Auth::user()->id]);
        $affected = DB::update('UPDATE users SET original_middle_name = CAP_FIRST(original_middle_name) WHERE id = ?', [Auth::user()->id]);
        $affected = DB::update('UPDATE users SET original_last_name = CAP_FIRST(original_last_name) WHERE id = ?', [Auth::user()->id]);

        $affected = DB::update("UPDATE users SET updated_first_name = original_first_name WHERE id = ?", [Auth::user()->id]);
        $affected = DB::update("UPDATE users SET updated_middle_name = original_middle_name WHERE id = ?", [Auth::user()->id]);
        $affected = DB::update("UPDATE users SET updated_last_name = original_last_name WHERE id = ?", [Auth::user()->id]);

        $affected = DB::update("UPDATE users a INNER JOIN (SELECT original_first_name, original_middle_name, original_last_name, COUNT(*)-1 AS duplicatedusernameid FROM users GROUP BY original_first_name, original_middle_name, original_last_name HAVING COUNT(*) > 1) b ON a.original_first_name = b.original_first_name AND a.original_middle_name = b.original_middle_name AND a.original_last_name = b.original_last_name SET a.duplicatedusernameid = b.duplicatedusernameid WHERE a.id = ?", [Auth::user()->id]);

        $affected = DB::update("UPDATE users SET duplicatedusernameid = (CASE WHEN duplicatedusernameid = 0 THEN NULL ELSE duplicatedusernameid END) WHERE id = ?", [Auth::user()->id]);
        $affected = DB::update("UPDATE users SET duplicatedusernameid = (CASE WHEN duplicatedusernameid = '' THEN NULL ELSE duplicatedusernameid END) WHERE id = ?", [Auth::user()->id]);

        $affected = DB::update("UPDATE users SET username = CONCAT_WS('.', COALESCE(original_first_name, ''), original_middle_name, COALESCE(original_last_name, ''), duplicatedusernameid) WHERE id = ?", [Auth::user()->id]);

    }

Thanks to all who have helped.