Member Avatar for ahudson

Hello All!

This question probably has a really simple answer but I am fairly new to PHP and I'm not very farmiliar with generating numbers yet. This is what I'm having trouble with:

  • Check MySQL to make sure that this UNIQUE number is not already stored. If it is, generate new number. If not, display number.
  • Generate 9-Digit Number
  • Display 9-Digit Number on Screen in this format: 000-000-000 (With the dashes)

Any help that I can get is MUCH appreciated.

Thanks,
Anthony

  • 3 Contributors
  • 2 Replies
  • 3K Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Good goal, but let the database guarantee uniqueness for you. Selecting first and then inserting (as in ’s example) can race under load and the old mysql_* API is long gone. A simpler, safe pattern is: put a UNIQUE index on the column, try to insert a random 9-digit number, and if you hit a duplicate key, just try again. As @diafol suggested, store the value as an INT for speed and add the dashes only when you display it.

SQL once-off:

ALTER TABLE tbl_rand
  MODIFY the_number INT UNSIGNED NOT NULL,
  ADD UNIQUE KEY uq_the_number (the_number);

PHP (PDO) example:

function createUniqueCode(PDO $pdo, int $maxAttempts = 10): array {
    for ($i = 0; $i < $maxAttempts; $i++) {
        $n = random_int(0, 999999999); // PHP 7+; use mt_rand on older PHP
        try {
            $stmt = $pdo->prepare('INSERT INTO tbl_rand (the_number) VALUES (:n)');
            $stmt->execute([':n' => $n]); // succeeds only if unique
            $padded = sprintf('%09d', $n);
            $formatted = substr($padded,0,3).'-'.substr($padded,3,3).'-'.substr($padded,6,3);
            return [$n, $formatted];
        } catch (PDOException $e) {
            if ($e->getCode() !== '23000') { // not a duplicate-key error
                throw $e;
            }
            // duplicate: loop and try another number
        }
    }
    throw new RuntimeException('Could not generate a unique code after several attempts.');
}

Why this scales: with a 1,000,000,000-size space, if you already have M rows, the chance a fresh draw collides is about M/1e9. Even at 10 million rows, you succeed on the first try ~99% of the time. This avoids SELECTs, prevents race conditions, and keeps the display format logic separate from storage.

Recommended Answers

All 2 Replies

Member Avatar for broj1

Maybe not the most elegant solution but it works.

<?php
// connect to DB
...

// run an endless loop
while(1) {

    // generate unique random number
    $randomNumber = rand(0, 999999999);

    // check if it exists in database
    $query = "SELECT * FROM tbl_rand WHERE the_number=$randomNumber";
    $res = mysql_query($query);
    $rowCount = mysql_num_rows($res);

    // if not found in the db (it is unique), break out of the loop
    if($rowCount < 1) {

        break;
    }
}

// pad the number with zeros (if needed)
$paded = str_pad($randomNumber, 9, '0', STR_PAD_LEFT);

// dash delimited string to be displayed
$delimited = '';

// add dashes
for($i = 0; $i < 9; $i++) {

    // add a character
    $delimited .= $paded[$i];

    // add dashes wherever appropriate
    if($i == 2 || $i == 5) {

        $delimited .= '-';
    }
}

echo $delimited; 
?>

My assumption is that you store numbers in the database as integers between 0 and 999999999. If you store them as strings then the code has to be adapted.

An improvement would be to generate random number and check the database all with an SQL. See this post on SO.

Edited by broj1
Member Avatar for Member #120589

With regard to your storing, are you going to store as integer, e.g. 374859053 for 374-859-053
and 23 for 000-000-023. In this way, you may find searching is significantly quicker. The dashes can be added after extraction from the db and preceding 0's added for number less than 9 digits long, e.g. with str_pad().

Just a thought.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.