Loop PHP code to INSERT a specific number of unique rows to MySQL

Question

Having a bit of trouble with a PHP & MySql script I'm trying to create. It's a bit complex so hope a guru out there can take a stab.

I need to generate 100 random 8 digit numbers and insert them as new rows into a MySQL table. The numbers have to be unique (i.e. not already in the column) and I need to make sure that I create exactly 100 rows - the problem being that if the 8 digit number is already in the column, existing solutions that suggest INSERT IGNORE etc will just skip over a row so I would end up only adding eg. 98 rows if 2 rows already contain the number.

So far I have gotten to a point where I can generate a random number (within limits), check the DB for that number in the specified column, if it doesn't exist it creates a new row. Concurrently the unique number is used to generate and save a QR code - the ultimate goal being to simply run the script once to create 100 QR codes and store their info in the DB.

So far I can do it for one number - any ideas on the best way to loop this?

<?php

// Connection variables:
$hostname="mydbhost";
$user="user";
$pass="password";
$dbname="name";

// Create connection
$con=mysqli_connect($hostname,$user,$pass,$dbname);

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// Pull the data from the DB
$result = mysqli_query($con,"SELECT number FROM qrcodes");

$data=array();

// Fill array with the data from the DB
while($row = mysqli_fetch_array($result))
{
    $data [] = $row[0];

}
//Min & Max values for the random numbers
$min = 80000000;
$max = 80009999;

//Set content to be included in QR Code and Label
$qrcontent = rand($min,$max);


if(in_array("$qrcontent", $data))
{
    //echo "Error: It's already in the DB!";
}
else
{
    //When not in the database
    mysqli_query($con,"INSERT INTO qrcodes (id, number, status) VALUES ('', '$qrcontent', '0')");

// the rest of the else clause creates and saves the QR code - just included it for interest sake
$text = "$qrcontent"; // URL for code to 
$label = "$qrcontent"; // message to display below barcode


//google chart API URL
// $image = "http://chart.apis.google.com/chart?cht=qr&chs=190x190&chl=" . $text . "&chld=h";
$image = "http://chart.apis.google.com/chart?cht=qr&chs=190x190&chl=" . $text . "&chld=h|2";

//Set path to font file to be used for label
$font = '/include/fonts/arialbd.ttf';

//Get the QR code image from google
$background = imagecreatefrompng($image); 
$swidth = imagesx($background); 
$sheight = imagesy($background); 

//If label field isn't blank, draw it in below the code
if(isset($label))
{
    $font_color = imagecolorallocate($background, 0, 0, 0); // set font color
    if(strlen($label) >= 15)
    {
        //split string in two, down the middle
        $half = (int) ( (strlen($label) / 2) ); // cast to int incase str length is odd
        $left = trim(substr($label, 0, $half));
        $right = trim(substr($label, $half));

        // Write Text
        imagettftext($background, 10, 0, 58, 181, $font_color, $font, $left);
        imagettftext($background, 10, 0, 58, 190, $font_color, $font, $right);
    } else {
        // Write Text
        imagettftext($background, 12, 0, 58, 190, $font_color, $font, $label);
    }
}


// Output header and final image 
header("Content-type: image/png"); 
header("Content-Disposition: filename=" . $image); 
// Save image to specified file
imagepng($background, "/var/www/images/qrcodes/".$qrcontent.".png"); 
// Optional - print image to screen in browser too
imagepng($background);


// Destroy the images 
imagedestroy($background); 

}

mysqli_close($con);

Appreciate any insight

All you want to do is prevent users from seeing a number in the URL and changing it just to see what happens? I'd suggest just doing some quick math on the database ID field when generating the codes, e.g. multiply it by 7, subtract 5, and then base64_encode it. Do the reverse on the page you display. $id=25; echo base64_encode(($id * 7) - 5); \\prints "MTcw" — miken32
– miken32, Commented Feb 20, 2014 at 18:18
Note that this does not provide security, but does provide more obscurity than your proposed method, and much lighter on the DB side. — miken32
– miken32, Commented Feb 20, 2014 at 18:19
That's a handy little trick - will keep it in mind. This is just to deter random punters guessing a number to use our application in the real world - but it's unlikely and there are more security layers in any case. Cheers though — contool
– contool, Commented Feb 20, 2014 at 19:11

ntaso · Accepted Answer · 2014-02-20 17:00:35Z

1

There are several factors at play here:

How often do you want to repeat the process? At some point in time, your 8 digits will be exhausted and also, 8 digits could be relatively easy to guess.

Do the numbers have to be truly random? Think about adding digits or alphanumeric characters.

These things aside, you want to do the following:

SELECT all existing QR-codes from the database
Create a new number in a loop:
```
do {
    $random = myRandomNumberGenerator();
} while (!in_array($random, $existingNumbers) && !in_array($random, $newNumbers));
```
You probably want to set a limit to prevent infinite loops. This way, you don't fail if there's a duplicate.
Repeat this process 100 times and put all new numbers in an array $newNumbers

Turn your entire array into a single SQL statement.

foreach ($newNumbers as $new) {
    $str .= "('', '$new', '0'),";
}

And finally build and execute the query:

"INSERT INTO (id, number, status) VALUES " . $str;

answered Feb 20, 2014 at 17:00

ntaso

6146 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Onimusha Over a year ago

8 digits could be relatively easy to guess - really? I'm thinking of a number between 10,000,000 (ten million) and 99,999,999 (ninetynine thousand nine hundred and ninetynine), i'll give you 1 million guesses :) Basically I think it's not true. Yes if he needs close to that many million records then unique random numbers won't be suitable but other than that it's fine

ntaso Over a year ago

Well, not guessable by a human being, but if you write a script that iterates between 10,000,000 and 99,999,999, it all depends on the capability of the machine/network/whatever circumstances :)

contool Over a year ago

Thanks ntaso, that's exactly what I was thinking, just wasn't sure how to do it. This would work - Ed's answer above is pretty elegant though and takes much of the strain off the database, particularly when iterating a vast number of times.

ntaso Over a year ago

Fine with me, but just a small note: My code has 2 queries in total, Ed's answer has 100 ;)

elixenide · Accepted Answer · 2014-02-21 14:43:06Z

0

There's a lot of irrelevant code above, but the gist of what you want is to use a UNIQUE key on the number column and code like this:

// your connection code ....

// How many rows inserted so far?
$rows_inserted = 0;
$numbers_inserted = array();

// Loop until done
while($rows_inserted < 100) {
    // Create random number
    //Set content to be included in QR Code and Label
    $qrcontent = rand($min,$max);

    mysqli_query($con,"INSERT INTO qrcodes (id, number, status) VALUES ('', '$qrcontent', '0')");

    if(mysqli_affected_rows($con) == 1) {
        $rows_inserted++;  // update the counter
        mysqli_affected_rows($con);
        // All your image manipulation code and so on ...
    }

}

edited Feb 21, 2014 at 14:43

answered Feb 20, 2014 at 17:01

elixenide

44.9k16 gold badges79 silver badges103 bronze badges

10 Comments

contool Over a year ago

Brilliant, thanks Ed, that worked like a charm. Logic & code works perfectly.

contool Over a year ago

For anyone who might be facing the same issue in the future - to change a column in a database to be a UNIQUE value use - ALTER TABLE "tablename" ADD UNIQUE ("columnname")

contool Over a year ago

Ed, after some testing I've realised that although this code will generate 100 numbers and only leave 1 instance of each in the DB, if it generates a number that is already in the database, it will just skip entering a new row, leaving the original. Ideally, it would check the DB for a number, if there already there it will create a new one - this way, I will always end up with 100 extra unique rows. Any ideas?

elixenide Over a year ago

Hmm... It should enter 100 rows, no matter what; that's what the mysqli_affected_rows is for. Try replacing that line with if(mysqli_affected_rows($con) === 1) {.

contool Over a year ago

Ed - I've noticed an error after a few days of testing. When I run the code above it executes and all the QR images are generated. However, the loop is not inserting some items into the database. E.g I ran this to insert 1900 rows, but it only inserted 1184. I tried to run the code to insert a single entry that should have been created but it enters a continuous loop - as if the PHP code is recognising the entry in the database and trying to skip to the next number (it can't as I've limited it), however the number isn't actually in the DB - any further ideas to hopefully tie this up?

|

user3105700 · Accepted Answer · 2014-02-20 17:23:38Z

0

Just generating the random numbers:

$picked = array();
$result2 = mysqli_query($con,"SELECT * FROM qrcode");
for($i=0;$i<mysqli_num_rows($result2);$i++) {
    $row = mysqli_fetch_array($result2);
    $picked[] = $row['number'];
}
$randomn = array();
for($i=0;$i<100;$i++) {
    $n = 00000000;
    while(in_array($n,$randomn) || in_array($n,$picked)) {
        $n = mt_rand($min,$max);
    }
    $randomn[] = $n;
}

Then use the $randomn array to get all the random numbers.

edited Feb 20, 2014 at 17:23

answered Feb 20, 2014 at 17:00

user3105700

3555 silver badges16 bronze badges

Collectives™ on Stack Overflow

Loop PHP code to INSERT a specific number of unique rows to MySQL

3 Answers 3

4 Comments

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related