Abk Abk - 1 month ago 8
MySQL Question

PHP: Writing unique random number to database

I am trying to write a serial and random PIN to mysql database but some PIN values are written multiple times.

how do I skip writing $pin into pin column if it already exist?

The snippet follows:

<?php

for($serial = 1000; $serial <= 1600; $serial++) {
$serial_prefix = "HCIS";

//generate random figures.
$rand_pin1 = rand(10599, 99999);
$rand_pin2 = rand(22222, 89898);
$pin = $rand_pin1 . $rand_pin2;
$f_serial = $serial_prefix . $serial;

$check = "SELECT pin FROM pin_serial WHERE pin = '$pin'";
$check_query = mysqli_query($connection, $check);
if(mysqli_num_rows($check_query) > 0){
// how do I skip writing $pin into pin column if it already exist here

}
elseif(mysqli_num_rows($check_query) == 0){
//inserting a generated figure and $serial into serial and pin column.
$pin_serial_query = "INSERT INTO pin_serial (serial, pin) VALUES('$f_serial', '$pin')";
mysqli_query($connection, $pin_serial_query);
}
}

Answer

A do..while loop should solve your problem:

for ( $serial = 1000; $serial <= 1600; $serial++ ) {

  $serial_prefix = "HCIS";

  do {

    // generate random figures
    $rand_pin1 = rand( 10599, 99999 );
    $rand_pin2 = rand( 22222, 89898 );
    $pin = $rand_pin1 . $rand_pin2;
    $f_serial = $serial_prefix . $pin;

    $check = "SELECT pin FROM pin_serial WHERE pin = '$pin'";
    $check_query = mysqli_query( $connection, $check );

  } while ( mysqli_num_rows( $check_query ) >0 );

  //inserting a generated figure and $serial into serial and pin column.
  $pin_serial_query = "INSERT INTO pin_serial ( serial, pin ) VALUES ( '$f_serial', '$pin' )";
  mysqli_query( $connection, $pin_serial_query );

}

While this will solve your immediate issue, as the number of rows grows you'll end up sending more and more SQL requests until you find an unused PIN. You will likely be happier with the result if you allow mySQL to generate a unique PIN for each new row.