Adding random number into DB issue

PHP
1

I have 3 columns in my DB: email (primary), unique_code, timestamp

Users enter their email address and it is added to the DB, including an alphanumeric 5 digit unique code in the ‘unique_code’ column and a timestamp in the ‘timestamp’ column.

[COLOR=“DarkRed”]1. The email address is being added, but not the unique code? What is the issue here and what can I should I specifically do to fix it?

  1. The next thing is that I need to display that code to the user in where it says <?php echo $unique_code;?>. How can I do that?[/COLOR]

DB is as follows:

Field 	Type 	Collation 	Attributes 	Null 	Default 	Extra 	Action
email 	varchar(64) 	utf8_unicode_ci 		No 	None 		
unique_code 	varchar(64) 	utf8_unicode_ci 		No 	None 		
timestamp 	timestamp 		on update CURRENT_TIMESTAMP 	No 	CURRENT_TIMESTAMP

INDEXES
PRIMARY BTREE Yes No email 7 A

Full code

<?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('This email already exists in the database.');
        }

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

        $msg = "Thank you!";
        $mysqli->query("INSERT INTO coming_soon_emails VALUES('email', SUBSTRING(MD5(UUID()),FLOOR(RAND()*25),5), UNIX_TIMESTAMP())");
echo "Something went wrong:" . $mysqli->error;
    }
    catch (Exception $e){

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

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


<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>example</title>

<link rel="stylesheet" type="text/css" href="css/styles.css" />

</head>

<body>

<div id="container">

    <form id="form" method="post" action="">
        <input type="text" id="email" name="email" value="<?php echo $msg?>" />
        <input type="submit" value="Submit" id="submitButton" />
    </form>

    <div id="thankyou">
    Thank you! <?php echo $unique_code;?></p>
    </div>


</div>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js"></script>
<script src="js/script.js"></script>
</body>
</html>

Thank you!

2

I thought I provided a suitable solution in your other thread?

3

Just noticed that! Sorry - didn’t see a reply on that thread.

How does it work? The unique_code is UNIQUE and the code in http://www.sitepoint.com/forums/php-34/php-what-inspires-you-630930.html creates the unique_code? A little confusing… sorry.

Thanks Again!!

4

Also if this makes it easier to understand…

Tutorial is here: http://tutorialzine.com/2010/10/ajaxed-coming-soon-page/

Where it does this:

    15            $mysqli->query("INSERT INTO coming_soon_emails

    16                            SET email='".$mysqli->real_escape_string($_POST['email'])."'");

    17     

    18            if($mysqli->affected_rows != 1){

    19                throw new Exception('This email already exists in the database.');

    20            }

Once it checks the email is not a duplicate, I would like it to add a unique (no two email records should have the same) Alphanumeric code like this: AQ4ILB9

Then when the user gets the “Thank you!” message in the textbox, I want it to also display the unique code as above.

I have to setup a new column in the DB for the addition of a code, correct? What properties must it have when adding to do the above code insertion? Possibly automatically creating the unique code for each record so the DB does the random code insertion work rather than a loop check in php?

How can I display the code to the user once the “Thank you!” message is displayed.

Any help editing the tutorial would be much appreciated!

Thanks!

5

The code in linked thread generates a code using the unique id from the new inserted record, so it’s always guaranteed to be unique. The way you’re approaching it currently is awfully wasteful and inefficient.

Here’s a some code that may help.


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

if('POST' === $_SERVER['REQUEST_METHOD']){
  
  $mysqli->query(sprintf(
    "INSERT INTO table (email, created) VALUES ('%s', NOW());",
    $mysqli->real_escape_string(filter_input(INPUT_POST, 'email'))
  ));
  
  $code = $Base62Converter->encode($mysqli->insert_id);
  
  $mysqli->query(sprintf(
    "UPDATE table SET code ='%s' WHERE id = %d AND email = '%s' LIMIT 1;",
    $mysqli->real_escape_string($code),
    $mysqli->insert_id,
    $mysqli->real_escape_string(filter_input(INPUT_POST, 'email'))
  ));
  
}
6

Thanks Anthony! Your code helped a great deal even though I went with another method :slight_smile: Thank you! Very much appreciate your help.