How to create and insert random number into mysql table

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
jameskaneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
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
Julian HansenCommented:
0
Ray PaseurCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Open in new window

?
0
Ray PaseurCommented:
@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
jameskaneAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
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
Ray PaseurCommented:
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
Julian HansenCommented:
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
Dave BaldwinFixer of ProblemsCommented:
An auto-increment primary key is most often used.  What is the requirement for a random number?
0
Ray PaseurCommented:
@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
jameskaneAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
Random numbers can be guaranteed NOT to be unique.  'automatic increment' is guaranteed to be unique.
0
jameskaneAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Julian HansenCommented:
(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
jameskaneAuthor Commented:
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
Julian HansenCommented:
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
jameskaneAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.