generating an sequential five digit alphanumerical ID

Question

General Overview:

The function below spits out a random ID. I'm using this to provide a confirmation alias to identify a record. However, I've had to check for collision(however unlikely), because we are only using a five digit length. With the allowed characters listed below, it comes out to about 33 million plus combinations. Eventually we will get to five million or so records so collision becomes an issue.

The Problem:

Checking for dupe aliases is inefficient and resource heavy. Five million records is a lot to search through. Especially when this search is being conducted concurrently by different users.

My Question:

Is there a way to 'auto increment' the combinations allowed by this function? Meaning I only have to search for the last record's alias and move on to the next combination?

Acknowledged Limitations:

I realize the code would be vastly different than the function below. I also realize that mysql has an auto increment feature for numerical IDs, but the project is requiring a five digit alias with the allowed characters of '23456789ABCDEFGHJKLMNPQRSTUVWXYZ'. My hands are tied on that issue.

My Current Function:

 public function random_id_gen($length)
 {
     $characters = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ';
     $max = strlen($characters) - 1;
     $string = '';

     for ($i = 0; $i < $length; $i++) {
         $string .= $characters[mt_rand(0, $max)];
     }

     return $string;
 }

Answer 1

Why not just create a unique index on the alias column?

CREATE UNIQUE INDEX uniq_alias ON MyTable(alias);

at which point you can try your insert/update and if it returns an error, generate a new alias and try again.

Answer 2

What you really need to do is convert from base 10 to base strlen($characters).

PHP comes with a built in base_convert function, but it doesn't do exactly what you want as it will use the numbers zero, one and the letter 'o', which you don't have in your version. So you'll need a function to map the values from base_convert from/to your values:

function map_basing($number, $from_characters, $to_characters) {
    if ( strlen($from_characters) != strlen($to_characters)) {
       // ERROR!
    }

    $mapped = '';
    foreach( $ch in $number ) {
       $pos = strpos($from_characters, $ch);
       if ( $pos !== false ) {
          $mapped .= $to_characters[$pos];
       } else {
          // ERROR!
       }
    }

    return $mapped;
}

Now that you have that:

 public function next_id($last_id)
 {
    $my_characters = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ';
    $std_characters ='0123456789abcdefghijklmnopqrstuv';

    // Map from your basing to the standard basing.
    $mapped = map_basing($last_id, $my_characters, $std_characters);

    // Convert to base 10 integer and increment.
    $intval = base_convert($mapped, strlen($my_characters), 10);
    $intval++;

    // Convert to standard basing, then to our custom basing.
    $newval_std = base_convert($intval, 10, strlen($my_characters));
    $newval = map_basing($newval_std, $std_characters, $my_characters);


    return $newval;
 }

Might be some syntax errors in there, but you should get the gist of it.