Unique code based on users unique email address inserted into mysql table?

PHP
1

I have a mysql table which will store users email addresses once submitted (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 in 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.

The table should look like:

email unique_code timestamp
test@test.com 5ABH6 2011-03-15 14:51:18
test2@test.com 9QRE0 2011-03-15 14:51:24

What code must I put and where?

My current php is as follows:

<?php

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

If the email address needs to be unique, just hash it and use that; although the resulting code will be more than 5 characters.


<?php
error_reporting(-1);
ini_set('display_errors', true);

function get_email_hash($email){
  return sha1(strtolower(trim($email)));
}

var_dump(
  get_email_hash('anthony.sterling@example.org')
);

/*
  string(40) "a6425feef427af3b06e20beefad4930c50c3222a"
*/
3

Hi Anthony,

Thanks for that!
It would be great if I didn’t mind having such a long string.

The string will be used to give users a unique referral code to be used at the end of the URL.

ie: example.com/FD5J1

What is the best way to create this with my above code? And how can I then do a check of the unique code column (to avoid multiple users having the same referral code) and insert it there, in the same row as the user?

Thank you again, so much!!

4

For example, once a user submits their email address this is displayed…

Refer friends <p style=“margin-top:20px;”><?php $URL = “example.com/”; $referral = substr(md5(uniqid(microtime(true),true)),0,5); echo $URL;echo $referral;?>

Obviously my php can be refined…

But with that $referral, it must go through a check of the unique_code column in the db, row by row, if unique, assign that users row with the code, otherwise regenerate, check and then assign.

Any chance you could help update my code in my original post?

Thank you!

5

Can the code be less than 5 chars?

6

Forsure! Less than 5 is perfectly ok :slight_smile:

7

OK then, simples. :slight_smile:

In your database table, store id and email address, something similar to…


CREATE TABLE `database`.`affiliate` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email`()
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;

The table will ensure you only have unique entries, so we use the id column to generate the code in question; much like a Short URL service.

Here’s the code that will do that. :slight_smile: