I think you could use 'WHERE catid IN'
For example, on PHP
$catlist="1,2,3,4,5";
... select ... where ... catid in ($catlist)
And of course you should use category id in web form instead category name.
----- Original Message -----
From: Beasley, Julien <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 31, 2001 7:50 AM
Subject: AND on a many to many table, with arbitrary ANDs
> Hello everyone,
>
> I am in the process of building a search engine on a database. I have two
> tables that have a many-to-many relationship: A story table and a category
> table.
>
> Story Table:
> -----------
> id | story|
> -----------
> 1 | Alice in Wonderland
> 2 | Peter Pan
> 3 | Pokemon's adventure
> 4 | Tale of Two cities
> 5 | War and Peace
>
> ..etc..
>
>
>
> Category Table:
> --------------
> id | category|
> --------------
> 1 | Children
> 2 | Classics
> 3 | Tolstoy
> ..etc..
> `
>
>
> Storycategories Table:
> ----------------------
> storyid | catid |
> ---------------------
> 1 | 1
> 1 | 2
> 2 | 1
> 2 | 2
> 3 | 1
> 4 | 2
> 5 | 2
> 5 | 3
> ..etc..
>
>
> >From my (admittedly dim) understanding of SQL, this is how one is
supposed
> to organize a many-to-many relationship. So peter pan is listed as 1
> (childrens) and 2 (classics).
>
> I am writing a web interface to do searches for stories. In my web
> interface, you have something like this:
>
> ---------------------------------------------------------------
>
> []Classics []Childrens []Tolstoy []Fiction ...
>
> Search Categories:
> (x) match all categories
> ( ) match any category
>
> ---------------------------------------------------------------
>
> In order to build a query that matches ALL categories, I use aliasing. To
do
> a search on stories that are Children AND Classics, I make two instances
of
> each table as shown in the example below.
>
> select
> S.story
> from
> stories_tbl S,
> categories_tbl C1, storycategories_tbl O1,
> categories_tbl C2, storycategories_tbl O2
> where
> S.id = O1.storyid and O1.catid = C1.id and
> C1.category like "Children" and
> S.id = O2.storyid and O2.catid = C2.id and
> C2.category like "Classic"
> group by
> story
>
>
> This query works fine. My problem arises when I have many categories to
> choose from. If I check many of the categories boxes, and select match all
> categories, my program builds a query with a large amount of tables, I get
> an error like this:
>
> Error 1116: Too many tables. MySQL can only use 32 tables in a join
>
> My guess is that there is a limit of 32 tables in a join, and that my use
of
> aliasing here ends up violating the 32 table limit.
>
> Even worse is when I make a query that has a little less than 32 tables.
> This won't give an error, but will leave a process running on the machine
> that consumes 100% cpu. After a while, this process will render the
database
> unusable, and no one can use the database until the process is killed!
>
>
> So to clarify, the query that ends up being built looks like this:
> select
> S.story
> from
> stories_tbl S,
> categories_tbl C1, storycategories_tbl O1,
> categories_tbl C2, storycategories_tbl O2
> ....
> categories_tbl Cn, storycategories_tbl On
>
> where
> S.id = O1.storyid and O1.catid = C1.id and
> C1.category like "Children" and
> S.id = O2.storyid and O2.catid = C2.id and
> C2.category like "Classic"
> ...
> S.id = On.storyid and On.catid = Cn.id and
> Cn.category like "xxxx"
> group by
> story
>
>
> This query fails when "n" is around 16.
>
> My question is, how can I build a query that will support an arbitrary
> number of ANDs on a many to many relationship? I'd like to be able to do a
> search on stories that are "Children AND Classics AND Tolstoy AND....
> Category N" where N is fairly large (hundred or so)..
>
> Is there any way to do this? Am I using aliasing incorrectly?
>
> Those of you that have made it in reading this far, thank you :). I
> appreciate your attention and any assistance you might lend me in this
> matter.
>
> Julien Beasley
>
>
>
> ---------------------------------------------------------------------
> 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
