link users to devices with mysql and php

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 eac开发者_开发百科h 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?

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);

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.

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());