How do I create a function which puts a unique entry in the sql table or loop?

Question

I basically want to create a unique login varchar in a table based of a first and last name.

So Jo Blob signs up and we go:

// check to see if username 'JoBlob' exists....

SELECT * FROM special_users WHERE username = $names[0] . $names[1]

// if it doesn't exist 

    INSERT INTO special_users username= $names[0] . $names[1]



// BUT if it does exist 

    // add some numbers to the username and insert

I'm curious how I can turn this into a function so that it would go through and then try JoBlob1 (if that exists, then) JoBlob2 (unless that exists), JoBlob3 etc etc and into infinity? I have some ideas but they seem excessively long ways around.

How should I approach this?

Answer 1

Well, the ugly solution is will be to use count( your new user name.family ) and add the special list name.family + the result +1 (translated to char of course)

I just use a random string generator, less readable, but do the job for me.

Answer 2

The following code outlines the general procedure - there is no error handling, that's left to the OP:

/**
 * @param  array $names
 * @param  PDO   $db
 * @return array|false -- false on error and an array(
 *     'id'       => auto-increment-id
 *     'username' => the username inserted
 * )
 */
function addUser(array $names, PDO $db) // $db is the PDO-resource, that's the DB connection handle
{
    $searchStmt = $db->prepare('SELECT COUNT(*) FROM special_users WHERE username = :username');
    $count = 0;
    while(true) {
        $username = $names[0] . $names[1];
        if ($count > 0) {
            $username .= $count;
        }
        $searchStmt->execute(array(':username' => $username));
        if ($searchStmt->fetchColumn(0) == 0) {
            // insert new user here
            $insertStmt = $db->prepare('INSERT INTO special_users (username) VALUES (:username)');
            $inserted = $insertStmt->execute(array(':username' => $username));
            if ($inserted) {
                return array(
                    'id'       => $db->lastInsertId(),
                    'username' => $username
                );
            } else {
                return false;
            }
        } else {
            $count++;
        }
        // break condition to avoid endless loop
        if ($count > 1000) {
            return false;
        }
    }
}

Answer 3

not sure that overhead-wise this is the best approach but this would work (untested code though)

function checkUnique($username){
 return (mysql_num_rows(mysql_query("SELECT username FROM special_users WHERE username = '".mysql_real_escape_string($username)."'"))>0) ? FALSE : TRUE;
}

function findUsername($username,$iterator=0){
 $usernamecheck = ($iterator>0) ? ($username.$iterator) : $username;
 if(checkUnique($usernamecheck)){
  return $usernamecheck;
 }else{
  findUsername($username,($iterator+=1));
 }
}

$unique = findUsername('JoBlob'); // use the output of findUsername for your insert

Answer 4

Exactly what ask -

insert into special_users (username) select concat('Joe',(select count(0) from special_users where username like 'Joe%'));

but your algorithm itself very bad at my point of view

Answer 5

Here's a function that will do just that, if that's really the way you'd like to handle your usernames. We can add a simple return statement to the end if necessary. It uses plain PHP MySQL calls:

function addUser( $name, $connection )
{
    assert( $connection && strlen( $name ) );
    $count_query = "SELECT COUNT(*) FROM special_users WHERE username LIKE '"
                   . mysql_real_escape_string($name) . "%'";

    $results  = mysql_query($count_query); 
    $row      = mysql_fetch_array($results);
    $username = ($row[0]) ? mysql_real_escape_string($name) . $row[0] 
                          : mysql_real_escape_string($name);

    $insert_query = "INSERT INTO special_users (username) VALUE ('$username')";

    if (!mysql_query( $insert_query, $connection ) )
    {
        die('Error: ' . mysql_error());
    }
}

//$con defined somewhere above...
for( $i = 0; $i < 5; ++$i )
    addUser( "David Letterman", $con );