aussiedave — 2011-08-17T03:35:02-04:00 — #1
The goal: Have 100 users into a table with each having a number assigned to them, chosen at random from a pre-setup table full of numbers. Each number is different and shouldn't be used for more than 1 user. Once 100 users have been added, no more inserts can be made and an alert will appear in the 101st insert.
I have two tables setup in MySQL.
The table 'users' (which is empty) and another table 'number' (100 rows with values to assign to new users), how could I use PHP to go:
On form submit, insert a new user (name as the input) into 'users', get a random value from 'number' and assign it to the user, mark that value from 'number' as "used up so no other name can get that value", and once the 100 users have been added (ie: the 100 values from 'number' have been assigned), the 101st insert will say "Sorry, database is full".
It's quite confusing for me to understand how to do this in PHP..
Thanks in advance.
cups — 2011-08-17T04:01:45-04:00 — #2
Heres an idea, set your tables up like this:
address varchar // and so on
So you have entries like:
So that the PK id in the users table is the FK id_ref in the random numbers table.
So to get Bobs random number:
SELECT name, number
FROM users as u
LEFT JOIN rand_numbers as r
ON u.id = r.id_ref
Untested but should give: 76 "Bob"
All you have to do is pre-populate the rand_numbers table - you could use a PHP rand() function to do that, and constrain the rand_numbers.number field to be unique.
Maybe there is a cleverer way to do this in sql of course, but if it is only 100 rows this should not be tooooo onerous - as a one-off task.
tgavin — 2011-08-17T11:30:44-04:00 — #3
Are you using the random number for an publicly visible identifier, such as what YouTube does with their videos? Or are you using it as an internal identifier?
If it's an internal identifier, just let SQL do it with auto_increment each time you add a new user.
If it's publicly visible you may want to generate an alphanumeric hash instead.
Regardless, instead of marking the record as used up in the numbers table, why not just delete it? When you create a new user, just select a random number, then insert the random number into the users record and then delete the random number from the random numbers table. That way there can never be mistakes and all you'd have to do when a new user tries to sign up is check to see if the users table contains 100 records, or if the random table is empty. Either or should work