> Hello MySQL users > > I was wondering if one of you could advise me on the best solution to a > problem I'm having - I'm sure this has been done before but haven't found > anything in the archives. > > 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|%'). >
One thing you could do is add a little programming to split the entry into its parts, once retrieved, then run your select on those. But a better solution would be to re-design your table/s to accomodate multiple values. -- Amer Neely, Softouch Information Services W: www.softouch.on.ca E: [EMAIL PROTECTED] V: 519.438.5887 Perl | PHP | MySQL | CGI programming for shopping carts, data entry forms. "We make web sites work!" --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
