0

I need to generate a unique random code to store in a database with a user id.

What I'm trying to do is create a php script that first generates a random string of a given length, then checks a database to see if that string already exists, if so, generate a new random string.

The database will be organized by email address or some other field like customer_id. Each user can have say up to 5 devices associated with their account.

As a test I've created two MYSQL tables, one called users: email, firstname, lastname

the other called udevices. Udevices has 6 fields, one for the email address and 5 for the devices: email, dev1, dev2, dev3, dev4, dev5

all fields in both tables are VARCHAR

It occurs to me that another way to organize this is to have just two fields - email and device and then for each device just add another record to devices. Not sure which is most efficient.

So what i'm looking for is how to write a SELECT statement that will query the database for a given email address and a device string.

So, to boil the question down:

Can someone give me an example of a SELECT statement as described above? Is this even possible? Web searches on the topic bring up people talking about having to loop through each db record. Is that the only way, and if so, can someone give me an example of a PHP script that can loop through each record to check if a string already exists in a database?

alphablender
  • 2,168
  • 5
  • 27
  • 42

3 Answers3

2

You need a minimum of two tables, but most probably three if you need device descriptions, etc... I would go with three tables if I were you.

users: user_id | email | name | surname

devices: device_id | device_name | ...

user_devices: user_id | device_id

On users and devices the user_id and device_id must be the primary keys. On user_devices user_id and device_id must be the compound primary key.

Then the query to select all the devices of a user would be:

    SELECT d.* FROM devices d
INNER JOIN user_devices ud 
        ON d.device_id = ud.device_id
       AND ud.user_id = 123;

As far as the unique random code, you must tell us what its content will be (i.e. where will the uniqueness be based upon). If you have that, you can easily use one of the hashing functions such as md5(), etc... to generate the random string.

EDIT

If you do not need to verify the value of the random string, then you can generate one with the base_convert and microtime. The odds of duplicates are down to the microsecond. That is, if both visitors will request the code on that same microsecond they will get the same string, which is hardly ever the case, but still possible.

$string = base_convert(microtime(true), 10, 36);
Community
  • 1
  • 1
Shef
  • 44,808
  • 15
  • 79
  • 90
  • It just has to be a random string of say 5 characters that is NOT in the database already. – alphablender Sep 04 '11 at 06:50
  • @alphablender: Do not think with the `NOT in database` attitude. That solution will be so resource hungry you don't even want to think like that. Hashing functions such as `md5()` have such a phenomenally small chance of duplicates. You could use `$id = md5($email . $device_name . uniqueid());` to generate a unique hash. – Shef Sep 04 '11 at 06:59
  • will that unique hash be short enough for users to easily type in when they visit the website, 5 or 6 characters? Isn't an MD5 hash pretty long? – alphablender Sep 04 '11 at 07:26
  • To rephrase that, is there a hashing function that generates a short alphanumeric string instead of a long one? – alphablender Sep 04 '11 at 07:43
  • No, it won't be that short. What exactly is the use case here? Can you use an integer be used for generating the "hash"? Meaning, can the hash be based on the value of `device_id`? – Shef Sep 04 '11 at 07:44
  • The idea is to link a device to a users account, so the user enters the random string displayed on device into website, which gets associated with a unique hardware id of the device and the user's account. – alphablender Sep 04 '11 at 07:50
  • Well, I just found something that might be useful: base64 or base36 encoding of the md5 hash: [link](http://stackoverflow.com/questions/1116860/whats-the-best-way-to-create-a-short-hash-similiar-to-what-tiny-url-does/1117008#1117008) The thing is, doesn't an md5 hash of a string always return the same? I need to generate fresh codes every time the user opens the app if they haven't linked yet. – alphablender Sep 04 '11 at 07:55
  • To make this even clearer, if you buy a Roku and want to watch Netflix, you get a random 5 digit string to enter on the Netflix website, which links your device to Netflix. You can click "get new code" and get a fresh code. I'm trying to do the same for a different system. – alphablender Sep 04 '11 at 07:58
  • @alphablender: Yes, do you need to verify the hash when the user enters the string? Or it doesn't matter, it just should be unique among all users? – Shef Sep 04 '11 at 07:59
  • Just unique among all users. edit: it also gets stored on the device. – alphablender Sep 04 '11 at 08:26
  • @alphablender: Then use the code on my edit. Also, make sure that the column on the table has a `UNIQUE` constraint, so even if it occurred that two users requested the string at the same microsecond one of them will be able to use, the other one should request another string. – Shef Sep 04 '11 at 08:30
1

it really depends on your final design, whether the udevices have static number of devices (as your current solution) or dynamic one (that 'another way' you stated). you don't need postprocessing via PHP, SQL alone can query it.

So what i'm looking for is how to write a SELECT statement that will query the database for a given email address and a device string

hey, isn't it too basic? read up your SQL book

Is that the only way, and if so, can someone give me an example of a PHP script that can loop through each record to check if a string already exists in a database?

what for? just SELECT and count the result. if it's > 0, then it exists. otherwise it doesn't.

LeleDumbo
  • 9,192
  • 4
  • 24
  • 38
  • I'm not an SQL or PHP programmer, though I've hacked a few things together before. I told my client to hire someone else to do this part of the job, but then I thought, maybe this is pretty simple to hack together and I'll see if I can figure it out. There are so many aspects to MYSQL that I think it is better to ask questions than to attempt to learn every possible aspect of a complex system which might take me 3-6 months before I get how to do this one thing. If you have a chapter of a mysql book to recommend however, that might be more helpful than just telling me to RTFM. – alphablender Sep 04 '11 at 07:34
  • As it turns out, I have a copy of Ben Forta's "MySQL Crash Course" I forgot about, so I guess I'll be taking your advice after all! – alphablender Sep 04 '11 at 08:27
0

A table with thing1, thing2, thing3 is clearly a repeating group, and should be normalized out. So your idea of having email/device is the right direction, however, using email address as the key is a bad idea. You are better off making a numeric auto_increment key for both tables, and having user_id be a foreign key in the device table that links them together, so that 1 user can have many devices associated with that user.

You can then query this table easily using select count(*) as countof from devices where device = '$devicename'. However, you can easily insure that your devices are unique by using something like: 

$device = md5($email . uniqueid());
gview
  • 14,876
  • 3
  • 46
  • 51
  • And to be clear, using that technique you would not have to run a check query first. Also, the right way to handle the uniqueness problem with mysql is to place a unique index/constraint on the device column. Then insert with your code, and catch the constraint violation. – gview Sep 04 '11 at 06:47
  • could a numeric auto-increment key be a value between 1679616 and 60466175 decimal so I could generate a base36 code the correct length from it? – alphablender Nov 02 '11 at 01:18
  • You can tell mysql to start with a particular number for autoincrement. Alter table yourtable AUTO_INCREMENT = 1679616. Of course you could also just use a constant and add to the key that offset for the purposes of generating your code. – gview Nov 02 '11 at 01:26