Re: Multiple Select storing

[Michael Stassen]

On Apr 3, 2005, at 12:50 PM, Robb Kerr wrote:
Please excuse the length and complexity of this post. I am facing a
complicated problem and need to find a solution. Also, I have posted 
this on
both PHP and MySQL boards so please overlook the possibly slightly 
off-topic
subject.

I have got one table that contains personal information about users. 
Another
table consists of a list of many interests. This table is composed of 
simply
2 fields - an ID and the interest. (see below)

I can build an HTML form which includes a multiple select list out of 
which
the user can select their interests. I have the list configured so 
that it
displays the "interest" while the value stored is the "ID". PHP stores 
this
entry as an Array which I can easily turn into a comma delimited list 
before
storing in the database.

Here's my problem. If I store a comma-delimited list in a text field 
in the
Users table, how do I query to determine anyone that has selected 
specific
interests? If I use a full-text search and someone selects "1" for 
their
search criteria, the query will return anyone who selects 1, 10, 11, 
12, 13,
14, etc. as their interest.

This problem has surely occurred for others. It would affect how you 
store
newsletters for which one might subscribe, keywords in a clipart 
system,
interests the user has, anything from which you want your users to 
select
multiple entries from a long list of choices.

Table = Interests
Fields = ID, int, auto-increment
            Interests, text
Table = Users
Fields = ID, int, auto-increment
            Name, text
            Address, text
            Interests, text
Thanx in advance for any help you might provide. If anyone knows of 
anything
written on configuring this type of system, please include a link.

Robb Kerr
Digital IGUANA
You can do this with FIND_IN_SET().  Something like
  SELECT ID, Name, Address FROM Users WHERE FIND_IN_SET(1, Interests);
to find users with interest 1.  See the manual for details 
<http://dev.mysql.com/doc/mysql/en/string-functions.html>.

That isn't the right way to do it, however.  One major problem with 
this setup is that an index on Interests in Users cannot be used to 
help this query, or any other which pulls a single interest out of the 
list.  The better way to do this is with a 3rd table which relates 
interests to users.

  CREATE TABLE user_interests (user_id INT, interest_id INT, PRIMARY 
KEY (user_id, interest_id));

and get rid of the Interests column in Users.  Then, instead of
Users:
 ID  Name  Address    Interests
  1  Joe   Main St.   1,5,7,13
  2  Sue   Elm St.    2,4,8
you'd have
Users:
 ID  Name  Address
  1  Joe   Main St.
  2  Sue   Elm St.
user_interests:
 user_id  interest_id
    1        1
    1        5
    1        7
    1       13
    2        2
    2        4
    2        8
See how each user has one row for each interest?  Now, to find all 
users with a particular interest, you join the Users and user_interests 
table:

  SELECT u.ID, u.Name, u.Address
  FROM Users u
  JOIN user_interests ui ON u.ID = ui.user_id
  WHERE ui.interest_id = 1;
or, to search by interest name
  SELECT u.ID, u.Name, u.Address
  FROM Users u
  JOIN user_interests ui ON u.ID = ui.user_id
  JOIN Interests i ON i.ID = ui.interest_id
  WHERE i.interest = 'Sailing';
To list Joe's interests:
  SELECT i.interest
  FROM Users u
  JOIN user_interests ui ON u.ID = ui.user_id
  JOIN Interests i ON i.ID = ui.interest_id
  WHERE u.Name = 'Joe';
The PRIMARY KEY on (user_id, interest_id) in user_interests prevents 
duplicate rows and serves as an index to make the joins to this table 
fast.

Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]