----- Original Message ----- From: "Robb Kerr" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Sunday, April 03, 2005 12:50 PM Subject: Multiple Select storing
> 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. > Big mistake. The situation you are describing is called a one-to-many relationship and you should never try to store the entire one-to-many relationship in a single row of a table. This is very bad form as database design goes. > > > 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. > Your basic problem is that you are trying to store the data incorrectly. Instead of trying to store the interests for ID 27 like this: ID Interest 1 Politics 27 Fishing, swimming, hockey, travel You should be storing it like this: ID Interest 1 Politics 27 Fishing 27 Swimming 27 Hockey 27 Travel The query to get all of the interests for ID 27 is dead simple: select Interest from mytable where ID = 27 The primary key of this table will be the combination of ID and Interest. In other words, the combination of ID and interest will always uniquely identify a row in the table. Therefore, you can have lots of interests for ID 27 and you can have lots of people whose interest is hockey but you can only have one row for ID 27 that says hockey is an interest. > > > Table = Interests > > Fields = ID, int, auto-increment > > Interests, text > > > > Table = Users > > Fields = ID, int, auto-increment > > Name, text > > Address, text > > Interests, text > > If you also want to store addresses for users, you will want to store them in a separate table. Assuming a single address for each ID, that could be as simple as this: ID Address 1 123 Main Street 27 456 Park Street But you may prefer to split the address out into different parts to make each part easily searchable. For example: ID Number Street City Province Postal_Code Phone 1 123 Main Street Toronto Ontario M2C 5S9 416-555-1212 27 456 Rue Parque Montreal Quebec H2S 3P8 514-555-1212 Now you can find people on the basis of their street, city, province, postal code, etc. very easily. You can also handle multiple addresses if you need to but this will require some modifications to the second table. If you need to get both addresses and interests in the same result, you can easily accomplish this by joining the two tables. > > 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. > This is standard normalization technique. If you Google on 'Normalization tutorial' you may find something useful. Another possibility might be a search on 'Database Design'. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
