CODEWITHSUNDEEP
phpmysqldatabaserandom
Abk
Abk

Reputation: 2233

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);
    }
}

Upvotes: 1

Views: 926

Answers (3)

Ben Shoval
Ben Shoval

Reputation: 1750

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.

Upvotes: 1

baldrs
baldrs

Reputation: 2161

Create unique index for pin column: 

ALTER TABLE `pin_serial` ADD UNIQUE INDEX (`pin`)

Then change your query to 

INSERT INTO pin_serial (serial, pin) VALUES('$f_serial', '$pin')
    ON DUPLICATE KEY UPDATE serial_pin = VALUES(serial_pin)

(note ON DUPLICATE KEY UPDATE serial_pin = VALUES(serial_pin) part, this is just example, you can just pin=pin to skip) It will update query if row with that pin value already exists, or insert a new row.

You also can use INSERT IGNORE statement, which will just ignore duplicates.

More about INSERT in MySQL docs on topic

Please note that in your example, the script is vulnerable to Sql injection attack. To avoid it, you should first pass your parameters to mysqli_real_escape_string function to make the data inside sql-safe(by escaping ambiguous characters)

Upvotes: 3

user783388
user783388

Reputation:

if you want to write the serial into the row where the pin generated pin already sits (effectively overwriting the old serial that is already there, you would use an UPDATE statement:

... "UPDATE pin_serial SET serial='$serial'";

If you just want to skip this pin/serial combo I think yo already got the answer. Your code should work

Upvotes: 0

Related Questions