Check name is unique if not append

Question

My application requires the user to enter their business name, which the application will automatically create into a unique identifier to be used in URLs, ie

"Bob's Cafe" will become "bobs-cafe"

But if there are duplicate names I would like the application to add a number so if there is already a "bobs-cafe" we will use "bobs-cafe-1" and likewise if there is already a "bobs-cafe-1" we will use "bobs-cafe-2"

Ive used explode and also looked at a regular expressions but I dont know the best way to approach this.

Im stuck in being able to grab the number and incrementing it and returning the string

Answer 1

Adding to Sarfraz's answer, you might want to find it using a LIKE statement

SELECT `urlIdentifier` FROM `businesses` WHERE `urlIdentifier` LIKE `bobs-cafe%`

which will get all the bobs-cafe items - that way, if you get 5 rows you know you have

bobs-cafe
bobs-cafe-1
bobs-cafe-2
bobs-cafe-3
bobs-cafe-4

and that you'll need to add bobs-cafe-5

EDIT - Or this:

SELECT count(*)  as `howMany` FROM `businesses` WHERE `urlIdentifier` LIKE `bobs-cafe%`

Now your result object ( or array ) will have the total number:

echo $resultObject->howMany; // number of bobs-cafe sql found

Answer 2

Why not to add an autoincrement number to every identifier in the URL?
Just like SO does:

stackoverflow.com/questions/2895334/php-application-check-name-is-unique-if-not-append

so, you have both unique identifier and a business name.

This is even better because they are free to change their business name, without changing an identifier.

As for your question it's very simple. Just for the PHP practice:

if (/* you've found the name is already non unique and have the max one in the $id */) {
  $parts = explode("-",$id);
  if (isset($parts[1])) $newid = $parts[0]."-".($parts[1]+1);
  else $newid = $parts[0]."-1";
}

Answer 3

Assuming $user is already in the form bobs-cafe

function username_exists ( $user ) {

      $result = mysql_query("SELECT name FROM table WHERE $name LIKE '$user%' ");
      $count = mysql_num_rows($result);

        if ( $result ) {

            $num = $count+1;
            return username_exists ( $user.'-'.$num ) ;

        } else {

            return $user;
        }

}