Random Autogenerated PIN number

[Shamrox]

I'm trying to create a table of user names and each one would have a random 5 digit number associated with it. Is there a way to create the mysql table and populate the names and have the numbers autogenerated randomly, just as if i used the standard auto generated numbering?

[fenway]

Why not use FLOOR( RAND() * 100000 )?

[Shamrox]

Can you expand on that a bit?  also, let me define a bit more.  I will have a list of names that will most likely be in .csv format that i'd import into an empty mysql table. It is at this point that i'd want all the PINS assigned and autopopulated.

Possible using your above method?

[fenway]

You just want a different number for each one, right? This can be accomplished with the above... and if you're using a new version of mysql, then I believe that you can set the values of additional columns that aren't in your column spec in-line.

[aschk]

Possible using your above method?

 

try it and see... 

[Shamrox]

Alright, I've gotten it all figured out how to generate a random 5 digit number between upper and lower limits. Now, what I can't figure out is how I go about finding out if that number has already been assigned in the database.

These numbers are used as PINs for using a copier and I can't give out the same number twice.

 

Any assist?

 

Oh, and if it does find the number in the database, how do i make it look for a new unused one?

 

 

[Shamrox]

Ok, I started working on this bit of code to generate the number and then start checking to see if it exists. I need a bit of helping finishing it up. Basically, i create a number, query the database table to get all the existing numbers, then start checking to see if it matches the new random. not sure how to close the loop.

 

<?php

function checkPin() {
$rando = rand(10000, 99999);
$sql = "SELECT pin FROM `mfppins` ORDER BY pin ASC";
$results = mysql_query($sql);
if(mysql_num_rows($results) > 0){
	if $row['pin'] == $rando {


	}else{
		return false;
	}
}else{
	$rando = rand(10000, 99999);
	return $rando;
}
}

?>

[Shamrox]

Added a bit more. Anyone think this is correct?

 

<?php
function checkPin() {
$rando = rand(10000, 99999);
$sql = "SELECT pin FROM `mfppins` ORDER BY pin ASC";
$results = mysql_query($sql);
while($row = mysql_fetch_array($results, MYSQL_ASSOC)){
$ranpin[] = $row;
}
$found = false;
for($x=0; $x<count($ranpin); $x++){
	if($rando == $ranpin[$x]['pin']){
		$found = true;
		checkPin();
	}else{
	return $rando;
		}
	}
}	

?>

[aschk]

The only problem I forsee with this sort of thing is,

say you have issued 89998 pins already and you're wanting to supply the last one of the bunch, you've got to hope like hell that rand() eventually gives you the number you're looking for. In other words your script could be going a long long long time before it finds that number...

 

And DON'T do recursive lookups on your DB. Your system will go down quicker than you say "s*** it's gone down"

i.e.

1) select all pins from db and put into an array

2) generate random number

3) use in_array() to see if number exists, otherwise do generate another random number (and test in_array() again).

[Shamrox]

Very good point about if a lot have been issued. Anyway around that?

[fenway]

Alright, I've gotten it all figured out how to generate a random 5 digit number between upper and lower limits. Now, what I can't figure out is how I go about finding out if that number has already been assigned in the database.

These numbers are used as PINs for using a copier and I can't give out the same number twice.

 

Any assist?

 

Oh, and if it does find the number in the database, how do i make it look for a new unused one?

If you're really limited to 5-digit PIN (i.e 100K records), why not simply fill the entire table with 100K records and then auto-magically pick one?

 

Better yet, have another table of *just* pins, generate the entire sequence, randomize the order (you can alter table order by rand()), and then assign a user_id to each record, and simply use "SELECT pin from pin_table WHERE user_id IS NOT NULL LIMIT 1"... voila!

[Shamrox]

Thank you everyone for you help and suggestions. I finally got it working.

[aschk]

Yeah, i agree with fenway's solution. Best to generate all the numbers first then assign them to the user.

 

Consider the following:

Your debit/credit card has a 4-pin meaning there are only 9999 combinations of pin number. Now i know (for a fact) there are more than 9999 card holds in the world , so what am i getting at?

Well we know that each card number is unique (the 16 digit long code), and combine this with a pin (of your choice) and you have a completely unique sequence, despite the fact that many people might in fact have the same pin.

Thus, applying it to your situation, each user is unique (by username or id), and having some of those users with the same pin number is NOT a problem because the user+pin combination will always be unique.