Solved

How to create and insert random number into mysql table

Posted on 2014-10-31
19
3,029 Views
Last Modified: 2014-11-06
I am creating a form to populate a table in mysql database. I need the key column to be a random 4 digit number - from a random number generator.

It does not seem possible to insert this number automatically by choosing a mysql datatype.

Given this it seems I should create a 'hidden' field in the form which takes its value from a random number generator which kicks in when the form page is launched.  However I do not know how to do this.

Thanks for any help

james
0
Comment
Question by:jameskane
  • 5
  • 5
  • 4
  • +1
19 Comments
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 100 total points
Comment Utility
Most server languages like PHP and ASP can do that.   Also, MySQL does have a RAND() function.  http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand
To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j – i)). For example, to obtain a random integer in the range the range 7 <= R < 12, you could use the following statement:

SELECT FLOOR(7 + (RAND() * 5));
0
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 100 total points
Comment Utility
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
This is what I've used.  You could remove the letters and just use the numbers if that's what you need.
http://iconoun.com/demo/random_unique_string.php

<?php // demo/random_unique_string.php
error_reporting(E_ALL);
echo "<pre>";

/**
 * GENERATE A SHORT UNIQUE RANDOM STRING FOR USE AS SOME KIND OF KEY.
 *
 * WE DELIBERATELY OMIT LOOK-ALIKE LETTERS LIKE O and 0, I and 1.
 * IF LOWER CASE LETTERS ARE INCLUDED, IT'S WISE TO OMIT LOWER-CASE L.
 *
 * IN SOME FONTS WE MIGHT WANT TO OMIT S,5 AND Z,2.
 *
 * NOTE THAT THE DATA BASE MUST HAVE THE rand_key FIELD DEFINED AS "UNIQUE"
 * NOTE THAT THE LENGTH ARGUMENT MUST MATCH THROUGHOUT SO WE DEFINE() IT.
 *
 * MOVING PARTS OF THE DEMONSTRATION FOLLOW LINE 70
 */

define('ARG_LENGTH', 14);

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// IN REAL LIFE, THIS WOULD BE A PERMANENT TABLE
$len = ARG_LENGTH;
$sql
=
"
CREATE TEMPORARY TABLE myKeys
( id          INT                   NOT NULL AUTO_INCREMENT PRIMARY KEY
, rand_key    VARCHAR($len)  UNIQUE NOT NULL DEFAULT '?'
)
"
;
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// MAKE LOTS OF UNIQUE AND RANDOM STRINGS
$num = 0;
$max = 10000;
$arr = array();
$aaa = time();
while ($num < $max)
{
    $arr[] = make_random_key($mysqli);
    $num++;
}
// SHOW THE WORK PRODUCT
$zzz = time();
$lap = $zzz - $aaa;
echo PHP_EOL . "CREATED $max UNIQUE KEYS IN $lap SECONDS" . PHP_EOL;
print_r($arr);


// FUNCTION TO MAKE A RANDOM STRING
function random_string($length)
{
    // POSSIBLE COMBINATIONS > HUNDREDS OF MILLIONS IF LENGTH > 6
    //           1...5...10...15...20...25...30.
    $alphabet = "ABCDEFGHJKMNPQRSTUVWXYZ23456789";
    $strlen   = strlen($alphabet);
    $string   = NULL;
    while(strlen($string) < $length)
    {
        $random = mt_rand(0,$strlen);
        $string .= substr($alphabet, $random, 1);
    }
    return($string);
}


// FUNCTION TO ENSURE THE RANDOM STRING IS UNIQUE
function make_random_key($mysqli, $mytable='myKeys', $mycolumn='rand_key')
{
    $key = NULL;

    // GENERATE A UNIQUE AND RANDOM TOKEN
    while ($key == NULL)
    {
        $key = random_string(ARG_LENGTH);
        $sql = "INSERT INTO $mytable ( $mycolumn ) VALUES ( '$key' )";
        $res = $mysqli->query($sql);

        // IF THERE IS A QUERY ERROR
        if (!$res)
        {
            // THE LIKELY QUERY ERROR WOULD BE A DUPLICATE VALUE
            if ($mysqli->errno == 1062)
            {
                $key = NULL;
            }
            // OTHER QUERY ERROR
            else
            {
                $err
                = 'QUERY FAILURE:'
                . ' ERRNO: '
                . $mysqli->errno
                . ' ERROR: '
                . $mysqli->error
                . ' QUERY: '
                . $sql
                ;
                trigger_error($err, E_USER_ERROR);
            }
        }
    }
    return $key;
}

Open in new window

0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
140 lines of PHP script
vs
INSERT INTO table (random_value) VALUES (FLOOR(1 + (RAND() * 9999)))

Open in new window

?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
@JulianH: It's a complete example, tested and working, with a link to the test script.  It contains the setup necessary for a self-contained demonstration of the concept.  It contains comments and explanation.  It's what I have actually used in deployed applications.  So yes, there is more code there than you might need, and you could strip out the comments.  You would use a permanent table.  I'm sure our author can take or leave the parts that are helpful or not helpful.  It's intended to be a teaching example, and for that sort of thing I sometimes write more than others.
0
 

Author Comment

by:jameskane
Comment Utility
Firstly, thank you all for taking the time to answer my question.  Two lines of background on this.

I have been using Coldfusion for quite some time and know how to do this with that language. I  do not know PHP and don't want to invest the effort at this time. My project requires that I use PHYTON.  My approach to updating database is to use HTML5 for form creation and CGI in python for the action page. This is what I learned on line and it works well - but the random number is causing a problem I could do it with Coldfusion, but want to stick with a html/phtyon combo.

I need this random number (designated KEY in the table) to be automatically generated when a new customer registers on the system. The registration process entails filling out a form with address etc. From what I have read, the best solution (got to try it tomorrow) is the
INSERT INTO table (random_value) VALUES (FLOOR(1 + (RAND() * 9999)))
from JulianH. I think I can build that into the CGI action page (which is written in python).

Will know tomorrow ! (after halloween !!)

thanks again everyone

james
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
You might want to rethink what you're doing with the random number.  The first problem is that random numbers are not going to unique so they will eventually fail as a key because primary keys must be unique.
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
Comment Utility
If you mark the column UNIQUE, MySQL will throw error number 1062 when an attempt is made to insert a duplicate value.  You can trap this error and retry the insert with a new random number.  As the number of rows in the table grows, the number of unwanted duplicates will grow, too, meaning that at some point the INSERT process will suffer a very large number of retries.  And, of course, there is a theoretical limit of 9999 rows in the table.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Is there a particular reason you want to use a 4 digit random number and not a UUID?

The latter is widely used, supported in MySQL and guaranteed to be unique.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
An auto-increment primary key is most often used.  What is the requirement for a random number?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
@Dave: It might be a "security by obscurity" thing.  Performance is not really an issue if you have a four-digit key and a two-digit requirement.  But if you've underestimated demand, you're going to be stoned to death on INSERT.  This article is a smart warning.
http://kccoder.com/mysql/uuid-vs-int-insert-performance/
0
 

Author Comment

by:jameskane
Comment Utility
Whooh !!  just signed in and read your comments.  Thanks very much to everyone again !!

This is a form facility for a new client to enroll for a service. The client inserts his/her info via a form. I have established a KEY/UNIQUE  CustomerID column in the table which stores client data. The reason for this is to ensure unique identification of clients.

On submitting the registration form the customers id should automatically allocated as part of the new record. This ID will be communicated to the customer.

I did think of using the automatic increment facility within mysql - maybe starting with a number such as 4000 - struck me as a possible security risk though ?  That's why I went for a random number. The number of customers is not going to be more than 3/5 hundred.

The only reason I have not considered a UUID solution is that I have never heard of it before !!!
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Random numbers can be guaranteed NOT to be unique.  'automatic increment' is guaranteed to be unique.
0
 

Accepted Solution

by:
jameskane earned 0 total points
Comment Utility
I have managed to sort this out by using a python random number generator in the action page -
random.randrange(1,9000). I eliminated CustomerID from the html input form and added it into the getData() function in the InsertCustomerInfo.cgi action page as shown below.

I have tried to use the sql function (FLOOR(1 + (RAND() * 9999)  in place of the python function, but it does not work.  In this way it looks like  CustomerID = (1 + (RAND() * 9999)) and gives the error
Error message:
End of script output before headers: InsertCustomerInfo.cgi

I just don't know how to integrate the sql approach into the action page.

+++++++++++++++++++++++++  extract from InsertcCustomerInfo.cgi action page++++++++++++++++++

formData = cgi.FieldStorage()

def getData():
      CustomerID = random.randrange(1,9000)
      FirstName = formData.getvalue('FirstName')
      LastName = formData.getvalue('LastName')
      StreetAddress = formData.getvalue('StreetAddress')
      Town = formData.getvalue('Town')
      PostCode = formData.getvalue('PostCode')
      EMailAddress = formData.getvalue('EMailAddress')
      return CustomerID, FirstName, LastName, StreetAddress, Town, PostCode,EMailAddress

#main program
if __name__== "__main__":
      try:
            htmlTop()
            db, cursor = connectDB()
            CustomerID, FirstName, LastName, StreetAddress, Town, PostCode,EmailAddress = getData()
            insertCustomerInfo(db,cursor)
            htmlTail()
      except:
            cgi.print_exception()
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
(FLOOR(1 + (RAND() * 9999)
Is not a Function - it is part of a SQL query and it does work.

You should take heed of comments by Dave regarding uniqueness of random numbers - if you use this approach pretty much guaranteed to have problems

Although Ray's article reference regarding UUID inserts is valid - in context unless you are doing 100K inserts on a 15Million record database - this is the best option for uniqueness and obscuring your numbering system i.e. auto_increment giving client id of 3 - implies that some other client is 4 next 5 etc.

While the article on the insert delays is interesting - it doe not detract from the fact that UUID in most cases like this is a very viable and widely used means to create a unique unguessable key.
0
 

Author Comment

by:jameskane
Comment Utility
Thanks JulianH - I was not too worried about it, because the CustomeID column is designated KEY and therefore  a error will be generated if duplicate rears its ugly head. I think I can catch the  error statement to request user to resubmit (while maintaining the form info intact to avoid inconvenience ) .. or to generate another RN until a unique one is found - like Ray's php code. That make sense ?
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
I was not too worried about it, because the CustomeID column is designated KEY and therefore  a error will be generated if duplicate rears its ugly head. I think I can catch the  error statement to request user to resubmit (while maintaining the form info intact to avoid inconvenience ) .. or to generate another RN until a unique one is found - like Ray's php code. That make sense ?
Not really - I understand what you are saying but it is definitely not a solution I would ever consider. But if it works for you and your users go for it.
0
 

Author Closing Comment

by:jameskane
Comment Utility
Thanks to everyone (Julian_H, Ray, Dave) for taking the time to contribute to this question.  I have learned that I really need to get  my competence up on mysql - and, for me, triggers in particular and how to integrate sql into python (beyond the common insert and select stuff). Unfortunately (keeping to the template for assigning points) - while I got lots of learning I did not get a solution DIRECTLY from you. But I did eventually scramble to a solution in the PYTHON context, as explained in my last comment.
 
james
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
[Probably Simple] Responsive CSS Layout Question 4 42
radio button value 3 45
Website Home page blank 4 25
Modify Table Width 6 13
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
In this tutorial viewers will learn how add a full-size background image to a webpage using CSS3. Create a new HTML document with an internal stylesheet.: In CSS, define the html element to have a background image. Use a high resolution image.: In t…
In this tutorial viewers will learn how to embed an audio file in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: : The declaration should display (CODE) HTML5 is supported by the most recent versions of all major browsers…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now