more keys on one field in sql ???

michel1965

I have a problem and i don't know how to handle this.

I have a table named 'Hobby' wich contains all kinds of hobby's, like 'computers','sports','internet','football','movies', and so on (up to more than 100 kinds of hobby's).
There's also a table called 'users' with one field that uses the 'HobbyID' from my 'Hobby' table to tell the kind of hobby the user has. Works fine if the user have just one hobby, but what to do (in most cases) if the user has more then one hobby. Is there a possibility to create a unlimited value of keys in one field ?

Michel Hendriks (Netherlands)

flipis

Use a table name User_Hobby. If user 1 has hobbies 1 and 2, the records would read like this:

USER HOBBY
1 1
1 2

Then, use SELECT DISTINCT(HOBBY) FROM User_Hobby WHERE USER = 1

and get all the hobbies of the user.

Hope it helps

fLIPIS

bastien

A better way is to create an array of hobbies and store then in one field. I do this for a shopping cart where products have multiple options.

I assume that you are using checkboxes to get the hobbies. Just create an array by naming all the checkboxes the same. Then on th epage that does the processing to add the values to the DB just add the line

$hobbies = implode(",",$hobbiesCheckbox)

Depending on the values you pass to the page you will get a string of words if you set the values of the chcek boxes to value="computers" and value="climbing" which get the result:
$hobbies="computers,climbing"

numbers if value="1" and value="2"
$hobbies="1,2"

If you're not dutch, you're not much

Bastien

flipis

The problem with your method is that you cannot do a query like:

SELECT CUSTOMERS.customer FROM CUSTOMERS LEFT JOIN HOBBIES on HOBBIES.id = CUSTOMERS.hobby WHERE HOBBIES.id = 4

or even a simpler:

SELECT id FROM customers WHERE hobby = 4

I used your method before on a DB i made and it gave me so much trouble, that now i prefer having three tables instead of one. My opinion only.

Dutch? I'm Spanish and i don't consider myself less. :-)

fLIPIS

michel1965

I think I prefer the way fLIPIS sugests, I thank you both for helping me out with this.