SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Adding random number into DB issue..

    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.

    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?

    2. 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?



    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 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. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I thought I provided a suitable solution in your other thread?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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-...ou-630930.html creates the unique_code? A little confusing.. sorry.

    Thanks Again!!

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also if this makes it easier to understand..

    Tutorial is here: http://tutorialzine.com/2010/10/ajax...ing-soon-page/

    Where it does this:


    PHP Code:
        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. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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'))
      ));
      
    }
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Anthony! Your code helped a great deal even though I went with another method Thank you! Very much appreciate your help.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •