selecting products that are in all selected categories

[robtbs]

Hi,
products in my database are listed in multiple categories..
i want to be able to return the images that are listed in all the categories selected, its easy to list all the products that are in either category ... but i want them to only display if listed in both/all the categories search on.

when searching i POST
$_POST['c']
as an array of category IDs from a form

I have tables as follows

products
categories
products_to_categories

products_to_categories can contain multiple rows for each product...

So i need to select

DISTINCT product WHERE the product is listed in all the submitted categories...

iv been trying to do...
"SELECT DISTINCT(product) FROM products_to_categories WHERE category = '1' AND category = '2'"

but that returns nothing, even though there is a product listen in both categories 1 + 2

This can be done with the query surely? but i cant figure it out 🙁

here is my current code
it builds a list of the product ids i need to output to the results page

$first = TRUE;
						foreach($_POST['c'] as $this_cat)
						{
							if($first == TRUE)
							{
								$catsql = "category = '".$this_cat."'";
								$first = FALSE;
							}
							else
							{

							$catsql .= " OR category = '".$this_cat."'";
						}							
					}

					$numcats = count($_POST['c']);

					$query1 = "SELECT * ".
					"FROM ".PRODUCT_CATEGORIES_TABLE." ".
					"WHERE ".$catsql." ORDER BY product";
					$result1 = mysql_query($query1)
									or die ("Tried to execute1: <i>".$query1."</i><br/>
									MYSQL said: <i>".mysql_error()."</i><br/>//end");

					$sameasbefore = NULL;
					$inallcats = TRUE;

					while($row1 = mysql_fetch_array($result1))
					{
						if($sameasbefore != $row1['product'])
						{
							$cats[] = $row1['product'];
							$sameasbefore = $row1['product'];																	
						}												

					}

I appreciate your help
Rob

[Crave]

I don't fully understand what you are trying to do, but from your SQL statements using "Distinct" I came up with this:

"select product FROM products_to_categories WHERE category = '1' AND category = '2' group by product"

[robtbs]

Thanks for your reply,
iv tried your idea..
unfortunately it doesnt work either...
it returns the same results

as my

SELECT DISTINCT(product) FROM table WHERE category = '1' AND category = '2' etc

(nothing)

basically what i want to achieve... is images can be listed in more than one category...

if for example i search

cat 1 + cat 2
only images that are listed in both those categories will be returned.

as apposed to return all the images from both categories...

hope this makes it a little clearer.
Thanks
Rob

[Crave]

Ok, I get it now.

I found a way to do it, using an inner join on itself as if it was two seperate tables:

SELECT t1.product FROM table t1 INNER JOIN table t2 ON t1.product = t2.product WHERE (t1.category = 1) AND (t2.category = 2)

It's kind of messed up, but it should work.

[robtbs]

OMG
thank you so much,
i have managed yo modify your example so it can support unlimited number of selected categories...

The finished result...
www.theimagecentre.com/search.php

many thanks Crave