Page 1 of 1

Unique code based on users unique email address into mysql

Posted: Tue Mar 15, 2011 1:34 am
by iCeR
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:**

Code: Select all

    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)){
    			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();		
    	}
    }

Re: Unique code based on users unique email address into mys

Posted: Tue Mar 15, 2011 6:25 am
by divedj
To generate your unique id you could use build in php function uniqid() which returns something like "0914d05d9070d908e711cba89ccf726a" and modify it a bit with substr() to shorten it ot the requested 5 characters. You could end up with having only 5 numbers or 5 characters in your id.
A little mor control would give you using
chr(rand(65,90)) which would return a single random character between A and Z
chr(rand(97, 122)) would return a single random character between a and z
chr(rand(48, 57)) would return a single random number between 0 and 9.

To check if the generated id already exists just use a select statment in your db query, selecting the just generated id and check with mysqli->num_rows for a positive result. Getting 1 as result means your id already exists and you have to regenerate the id.
Getting 0 as result insert the id in the database the same way as you do with your email just add a where clause like

Code: Select all

$mysqli->query("INSERT INTO coming_soon_emails
                                                SET unique_code='".$generatedIdVar." 
                                                WHERE email = ".$_POST['email'].'");
Hope I could give you a couple of ideas how to go about.