Skip navigation
tonybabb
tonybabb
Currently Being Moderated

How to generate a unique random number in a MySQL db

Dec 28, 2011 2:48 AM

I'm creating a volunteer and also a separate vendor application form for an airshow. The volunteer and vendor info is stored in separate tables in a MySQL db, one row per volunteer or vendor. There will be about 100 volunteers and 50 vendors. When the application is submitted it should immediately be printed by the applicant, then signed and mailed in. This past year we had problems with some people who didn't immediately print their application so I'd like to still give them the option to immediately print but also send them an e-mail with a link to their specific row in the MySQL db. I have an autoincrement field as the primary key for each table, but I think sending this key to the applicant in an e-mail would be too easy for them to guess another id and access other people's info.

 

I'm thinking I should add a column to each table which would contain a unique random number and I would then send this key in the e-mail to the applicant. So, can anyone suggest a simple way to do this or suggest a better way of giving the applicant a way to access their own application and no-one elses after they have submitted their form?

 

Thanks all.

 

Tony Babb

1880 Views   6 Replies   Latest reply: tonybabb, Jan 1, 2012 8:40 AM
 
Replies
  • Drymetal
    Currently Being Moderated
    1. Drymetal,
    Dec 28, 2011 3:03 AM   in reply to tonybabb
    Report

    Why not just create a registration page and login?  Then their form could be stored in the database under their "user_id" or something and if they needed it later, they could just login and that info could be displayed to them using their user id.

     
    |
    Mark as:
  • tonybabb
    Currently Being Moderated
    2. tonybabb,
    Dec 28, 2011 3:06 AM   in reply to Drymetal
    Report

    Thanks, that's an option I could use for them, many of these users are technically challenged and I think just giving them an e-mail link would be easier for them. I appreciate the suggestion.

    Tony

     
    |
    Mark as:
  • Drymetal
    Currently Being Moderated
    3. Drymetal,
    Dec 28, 2011 3:59 AM   in reply to tonybabb
    Report

    Ok here is a way to do it.

    In your head put this:

     

    <?php
 $num = rand(98564654, 415231564);                     
?>

     

    This will pull a random number inbetween 98.5 million and 415.2 million.

     

    Or change those numbers to whatever.

     

    Then put something like this above your form:

     

    <p>Application ID: = <?php echo $num;?></p>

     

    This will show Application ID: = Random Number

     

    Then in your form, create a hidden field and for the value put this:

     

    value="<?php echo $num; ?>

     

    So your hidden field might look like this on the form:

     

    <input name="myNum" type="hidden" id="hiddenField" value="<?php echo $num; ?>"/>

     

    Then have that field go into your database for the user id or whatever you want and you can use that to pull their records in an e-mail.

    Hope that helps some.

     
    |
    Mark as:
  • tonybabb
    Currently Being Moderated
    4. tonybabb,
    Dec 30, 2011 2:15 AM   in reply to Drymetal
    Report

    Thanks so much, that was very helpful. I added the code you suggested to create and display the random number - I called it "vollink" and that worked fine. Then I added the hidden field toward the bottom of the form - it shows at line 311 when I do a "View Source in Int Explorer and then tried adding the code to add it to the table and when I tested it failed with "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1" . The test version of the page is here www.hollisterairshow.com/volunteerapp2.php . The changes I made to add it to the table is shown below , I must be missing something blindingly obvious, if you could suggest a fix I'd really appreciate it. I did add the field to the MySQL table also.

     

    Thanks again

     

    Tony

     

    $editFormAction = $_SERVER['PHP_SELF'];
    if (isset($_SERVER['QUERY_STRING'])) {
      $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
    }

    if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
      $insertSQL = sprintf("INSERT INTO volunteers (firstname, lastname, email, thursday, friday, saturday, sunday, monday, activity, talents, specialrequests, tshirt, phone, street, city, st, zip, updatedby, vollink) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, $s)",
                           GetSQLValueString($_POST['firstname'], "text"),
                           GetSQLValueString($_POST['lastname'], "text"),
                           GetSQLValueString($_POST['email'], "text"),
                           GetSQLValueString($_POST['thursday'], "text"),
                           GetSQLValueString($_POST['friday'], "text"),
                           GetSQLValueString($_POST['saturday'], "text"),
                           GetSQLValueString($_POST['sunday'], "text"),
                           GetSQLValueString($_POST['monday'], "text"),
                           GetSQLValueString($_POST['activity'], "text"),
                           GetSQLValueString($_POST['specialtalents'], "text"),
                           GetSQLValueString($_POST['specialrequests'], "text"),
                           GetSQLValueString($_POST['tshirt'], "text"),
                           GetSQLValueString($_POST['phone'], "text"),
                           GetSQLValueString($_POST['street'], "text"),
                           GetSQLValueString($_POST['city'], "text"),
                           GetSQLValueString($_POST['st'], "text"),
                           GetSQLValueString($_POST['zip'], "text"),
            GetSQLValueString($_POST['vollink'], "text"),
                           GetSQLValueString($_POST['lastname'], "text"));

      mysql_select_db($database_adminconnection, $adminconnection);
      $Result1 = mysql_query($insertSQL, $adminconnection) or die(mysql_error());

     
    |
    Mark as:
  • Drymetal
    Currently Being Moderated
    5. Drymetal,
    Jan 1, 2012 8:06 AM   in reply to tonybabb
    Report

    In your code, you have:

     

     VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, $s)",

     

    Change the $s to %s and that should fix the problem for you.

    Let me know.  Thanks!

     

    Chad

     
    |
    Mark as:
  • tonybabb
    Currently Being Moderated
    6. tonybabb,
    Jan 1, 2012 8:40 AM   in reply to tonybabb
    Report

    That was it, thank you so much.

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points