Skip to main content
A
Anonymous
December 28, 2011
Answered

How to generate a unique random number in a MySQL db

  • December 28, 2011
  • 2 replies
  • 5044 views

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

This topic has been closed for replies.
Correct answer Drymetal

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


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

2 replies

A
Anonymous
January 1, 2012

That was it, thank you so much.

Drymetal
Drymetal
Inspiring
December 28, 2011

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.

A
Anonymous
December 28, 2011

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

Drymetal
Drymetal
Inspiring
December 28, 2011

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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices