PHP: Writing unique random number to database

Question

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

baldrs · Accepted Answer · 2016-07-10 18:28:46Z

3

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)

edited Jul 10, 2016 at 18:28

answered Jul 10, 2016 at 18:05

baldrs

2,1611 gold badge26 silver badges35 bronze badges

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

6 Comments

Blag Over a year ago

Good idea, but I'll add a warning about the SQL injection risk (even if it seem to be Pi/arduino local code)

baldrs Over a year ago

@Blag good point, added note about sql injection to the answer.

Abk Over a year ago

The pin and serial column are already a unique key. I tried the solution you provided but still got multiple pin written.

baldrs Over a year ago

@user3186314 do you have unique index(pin, serial_pin)? Then you must have unique pair, that is, error will occur only if you insert existing combination of serial and serial_pin. You need to change to have two unique indexes then.

Abk Over a year ago

Pin and serial are unique. I was suprised when I saw duplicates in the pin column.

|

Ben Shoval · Accepted Answer · 2016-07-11 04:13:39Z

1

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.

edited Jul 11, 2016 at 4:13

answered Jul 10, 2016 at 18:38

Ben Shoval

1,7522 gold badges15 silver badges20 bronze badges

3 Comments

Abk Over a year ago

When I tried your solution, almost all the pin values are the same. It did not work for me. What am I doing wrong?

Ben Shoval Over a year ago

Sorry... stupid mistake on my part. Change "$f_serial = $serial_prefix . $serial;" to "$f_serial = $serial_prefix . $pin;" and it will work. I've corrected this in the answer.

Ben Shoval Over a year ago

Great! Glad I could help.

user783388 · Accepted Answer · 2016-07-10 18:05:30Z

0

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

answered Jul 10, 2016 at 18:05

user783388

Collectives™ on Stack Overflow

PHP: Writing unique random number to database

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related