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

Answer 1

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 ...
    }

}

Answer 2

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.

Answer 3

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:

1. SELECT all existing QR-codes from the database

2. 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.

3. Repeat this process 100 times and put all new numbers in an array $newNumbers

4. 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;