Unique code based on unique email address in mysql table?

I have a mysql table which will store users email addresses (each is unique and is the primary field) and a timestamp.

I have added another column called 'unique_code' (varchar(64), utf8_unicode_ci).

What I would very much appreciate assistance with is;

a) Generating a 5 digit alphanumeric code, ie: 5ABH6

b) Check all rows the 'unique_code' column to ensure it is unique, otherwise re-generate and check again

c) Insert the uniquely generated 5 digit alphanumeric code into 'unique_code' column, corresponding to the email address just entered.

d) display the code on screen.

What code must I put and where?

My current php is as follows:

require "includes/connect.php";

$msg = '';

if($_POST['email']){

    // Requested with AJAX:
    $ajax = ($_SERVER['HTTP_X_REQUESTED_WITH']  == 'XMLHttpRequest');

    try{
        if(!filter_input(INPUT_POST,'email',FILTER_VALIDATE_EMAIL)){
       开发者_Go百科     throw new Exception('Invalid Email!');
        }

        $mysqli->query("INSERT INTO coming_soon_emails
                        SET email='".$mysqli->real_escape_string($_POST['email'])."'");

        if($mysqli->affected_rows != 1){
            throw new Exception('You are already on the notification list.');
        }

        if($ajax){
            die('{"status":1}');
        }

        $msg = "Thank you!";

    }
    catch (Exception $e){

        if($ajax){
            die(json_encode(array('error'=>$e->getMessage())));
        }

        $msg = $e->getMessage();        
    }
}

Hope this helps:

a) I did something very similar to this where I was generating unique codes which were to be used as URLs. I wrote this to generate the codes:

private function _generateCode($length = 5) {

    $characters = 'bcdfghjkmnpqrstvwxyz';

    $string = '';
    for ($i = 0; $i < $length; $i++) {
        $string .= $characters[rand(0, strlen($characters) - 1)];
    }

    return $string;

}

$characters is a string of "allowed" characters. We chose to remove the vowels so that there was no chance of making unwanted words :) You could change this. There are simpler ways to write it but we needed something quite specific.

You would use it like this:

$unique_code = _generateCode();

b) For this just wrap your insert statement in a select statement check for that unique code. If the code exists then generate another code and try again. One way you could do this is (NB: this isn't tested and it may be susceptible to an infinite loop if you happen to get into a situation where you've used up all your codes ;) And you should probably add a check to make sure the INSERT was succesful):

$unique_code = "";
$inserted = false;
// Keep looping until we've inserted a record
while(!$inserted) {
    // Generate a code
    $unique_code = _generateCode();
    // Check if it exists
    if ($result = mysqli->query("SELECT unique_code FROM coming_soon_emails WHERE unique_code = '$unique_code'")) {
        // Check no record exists
        if ($result->num_rows == 0) {
            // Create new record
            $mysqli->query("INSERT INTO coming_soon_emails (email,unique_code) VALUES ('" . $mysqli->real_escape_string($_POST['email']) . "','$unique_code')");
            // Set inserted to true to ext loop
            $inserted = true;
            // Close the result object
            $result->close();
        }
    } else {
        // Quit if we can't check the database
        die('Something went wrong with select');
    }   
}

// Output the code
echo $unique_code;

c) To insert the unique code just add this to your insert statement where $unique_code is the variable assigned the return value from the function above:

$mysqli->query("INSERT INTO coming_soon_emails (email,unique_code) VALUES ('".$mysqli->real_escape_string($_POST['email'])."','$unique_code')");

d) Just echo the variable you assigned the code to e.g:

echo $unique_code;

A slightly more elegant solution would be to use MySQL to do most of the work for you, by setting the unique_code field type to VARCHAR(5) and placing a UNIQUE index on it. You could then use the query:

$sql = "UPDATE coming_soon_emails SET `unique_code` = MD5(CONCAT(`email`, NOW()))
        WHERE `email` = {$email}";

and your PHP code would look like:

while(!$mysqli->query($sql)) {
    // If the error that was thrown wasn't a duplicate key problem,
    // something else is wrong (ie can't connect to MySQL server).
    if($mysqli->errno != 1062) {
        // Error handling code
        break;
    }
}

Using this approach, moving to a longer unique code (eg 6 chars) is as easy as altering the unique_code field to be of length 6.