Giving a 4 Digit Random Code to all users within Database

Question

I would like to update my users database with a 4 digit random number.

I seem to have got stuck, on the bit for the loop, so that it will give a different random number to each user in the database. So far i have this.

$newpincode = array(sprintf("%04d",mt_rand(0,9999)));

$users_sql = "SELECT * FROM wifi_users";
$result_users = mysql_query($users_sql) or die(mysql_error());
while($row = mysql_fetch_array($result_users)){

foreach ($newpincode as $pin) {

    $sql = "UPDATE wifi_users SET `pincode` = '" . $pin . "' WHERE id = '" . $row['id'] . "'";
    $resultCount = mysql_query($sql) or die(mysql_error());
    $count = mysql_fetch_assoc($resultCount);
    }
}

Aside from using the deprecated MySQL extension, you're trying to use `mysql_fetch_assoc()` after an `UPDATE` statement. What's `$count` supposed to store? You're overwriting it on each loop. — ironcito, Aug 20 '13 at 16:24

Mike Brant · Accepted Answer · 2013-08-20T16:24:51.997

3

Why not just run a single query:

UPDATE wifi_users SET `pincode` = LPAD(FLOOR(RAND() * 10000), 4, '0')

Note this doesn't guarantee uniqueness, but then again neither does your current code. 4 digits doesn't give you enough values to provide uniqueness anyways if you plan on having more than 10000 users.

edited Aug 20 '13 at 16:24

answered Aug 20 '13 at 16:17

Mike Brant

70,514
10
99
103

Would that solution not sometimes return values that are less than 4 characters in length? Maybe `ROUND((RAND() * (9999-1000))+1000)` would be better. – John Dorean Aug 20 '13 at 16:21
Exactly the very simple thing i required, that saved me coding another script, wonderful. Thankyou – Clifford Yeti Mapp Aug 20 '13 at 16:22
@ChrisWhite Yes you are correct. I updated the example to simply left pad with zeroes to 4 characters in length. This would allow for values like `0001`, `0010`, `0100` that wouldn't be available if you simply returned a range between 1000-9999. – Mike Brant Aug 20 '13 at 16:26

score 1 · Answer 2 · edited May 23 '17 at 11:51

1

If you're looking for a unique random number for each user check this out: How to Generate Random number without repeat in database using PHP?

edited May 23 '17 at 11:51

Community

1
1

answered Aug 20 '13 at 16:19

lostphilosopher

4,361
4
28
39

score 0 · Answer 3 · answered Aug 20 '13 at 16:17

0

$newpincode = rand(0, 9).rand(0, 9).rand(0, 9).rand(0, 9);

answered Aug 20 '13 at 16:17

Stefan Dunn

5,363
7
48
84

score 0 · Answer 4 · answered Aug 20 '13 at 16:19

The problem is that the same pin code is given to all users.

Also, your inner loop would theoretically update the same record multiple times, although in this case that only happens once per record.

To issue a new pin code for each record, you have to move the pin code generation inside the outer loop and remove the inner loop.

As an aside, consider using PDO and prepared statements.

Giving a 4 Digit Random Code to all users within Database

4 Answers4