Hello James, > Basically the problem I am having is how best to handle multiple values for > a specific column, in this case the values in question are coming from an > HTML SELECT MULTI box processed by PHP. > > The way I have been doing this so far is to have a delimited value stored > in a varchar column, e.g. If my select box returns the values 2,4 and 7 I > insert into my table the string '|2|4|7|'. > > Surely there must be a better way than this - but it escapes me. In this > setup the only way to match a specifc value when searching is to use the query: > > SELECT dataid,title FROM table WHERE category LIKE '%|4|%' > > Which obviously has a huge performance penalty - and of course you can't > JOIN against any of these values. > > The only other way I thought of was to use a separate table for the > category entries: > > SELECT dataid,title,category FROM table LEFT JOIN table_categories ON > table.dataid=table_categories.dataid > > But in the example above this would return 3 entries, which I don't want, > and I can't select a particular dataid which satisfies more than category, > e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement > would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%').
The separate table idea is standard relational theory. The tbl schema might involve (1) a unique Id column (with AUTO_INCREMENT), (2) a 'copy' of whatever data from the existing tbl(s) to sufficiently identify the particular response/select box, and (3) one of the select box return values - there would then be as many rows as there were responses in the select box (n=3 in this example). The second of these must be sufficient information to uniquely identify which table entry in the original "table" relates to the particular select-response - these are called Foreign Keys. To retrieve all of the select-responses, you would indeed issue a SELECT...JOIN and thus be able to reassemble the HTML select command, for example. Yes you are correct (in your example) such a SELECT will produce a resultset of 3 rows. This will allow you to search on a single, or multiple (concurrent) select-responses. As mentioned, this is SOP. You say you want multiple responses but not multiple "entries", but not why. Perhaps most of the 'problem' lies hidden there? =dn --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
