Hi Amer Thanks for replying - I wonder if you could clear up a point for me. I'm going to have many, many possible multi values - too many for a SET or ENUM column as some other people have suggested.
I'm happy using the following tables: parts (partid int, partname varchar(255)) cats (catid int, catname varchar(255)) parts_cats (id int, partid int, catid int) To track which categories are assigned to which parts. I can use the following query to return parts and associated categories: SELECT partid,catid,partname,catname FROM parts_cats LEFT JOIN parts ON parts_cats.partid=parts.partid LEFT JOIN cats ON parts_cats.catid=cats.catid However, as I won't know how many categories are assigned to a given part, how can I: a) Form a query that only returns a list of parts that match a specifc set of categories (e.g. 1=>CatA' AND 5=>'CatE' AND 26=>'CatZ' only) b) Know how many unique records I'm dealing with. Using the SQL above, if a part has, say, 3 categories then 3 rows would be returned. 4 categories and 4 rows are returned, etc. - how can I tell how big my result set is so that I can page through it using LIMIT? Thanks again- james At 22:43 10/02/2002 -0500, Amer Neely wrote: > > 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 James Carrier Bullet Online :: Aim Higher [http://www.bulletonline.com] 41b Beavor Lane, London W6 9BL Tel +44 (0) 20 8834 3442 Fax +44 (0) 20 8741 2790 --------------------------------------------------------------------- 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
