Solved

How to create and insert random number into mysql table

Posted on 2014-10-31
19
3,240 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 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 100 total points
ID: 40415360
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 52

Assisted Solution

by:Julian Hansen
Julian Hansen earned 100 total points
ID: 40415409
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40415626
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 52

Expert Comment

by:Julian Hansen
ID: 40415721
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
ID: 40415967
@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
ID: 40416274
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 83

Expert Comment

by:Dave Baldwin
ID: 40416470
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
ID: 40416521
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 52

Expert Comment

by:Julian Hansen
ID: 40416585
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40416628
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
ID: 40416736
@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
ID: 40416932
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 83

Expert Comment

by:Dave Baldwin
ID: 40417011
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
ID: 40417052
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 52

Expert Comment

by:Julian Hansen
ID: 40417957
(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
ID: 40417965
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 52

Expert Comment

by:Julian Hansen
ID: 40418898
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
ID: 40425785
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Find out what you should include to make the best professional email signature for your organization.
In this tutorial viewers will learn how to embed videos in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <video> tag to insert a video. Define the src as the URL of your video; this is similar to …
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

910 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

19 Experts available now in Live!

Get 1:1 Help Now