Page 1 of 1

searching different values for one field

Posted: Mon Jan 24, 2005 7:04 am
by robtbs
hi,
i have the following query to grap products that are in selected categories when posted though a form....

the results it giving are not what i want :(

heres the code...

Code: Select all

foreach($_POSTї'c'] as $this_cat)
{
     if($first == TRUE)
     {
     $catsql = "category = '".$this_cat."'";
     $first = FALSE;
     }
     else
     {
      $catsql .= " OR category = '".$this_cat."'";
     }							
}
						
$query1 = "SELECT DISTINCT(product) FROM " . PRODUCT_CATEGORIES_TABLE . " " .
"WHERE " . $catsql;
$result1 = mysql_query($query1) or die ("Tried to execute1 :<i>" . $query1 . "</i><br/>
      MYSQL said: <i>" . mysql_error() . "</i><br/>//end");
								
						
while($row1 = mysql_fetch_array($result1))
&#123;
     $cats&#1111;] = $row1&#1111;'product'];
&#125;
This is returning the product ids for items in either of the selected categories....
what i want is for products ids that are in all the selected categories...

So if
product 1 is in cat 1 + 2
and product 2 is in just cat 1.

if i search for cat 1+2 only product 1 is returned...

in the database i have rows...
with the product ID and the category. so if a product is in multiple categories there is 1 or more rows with product id = 1 for example...

Hope someone has some ideas.
Thanks
Rob

Posted: Mon Jan 24, 2005 8:06 am
by feyd
look at the IN syntax:

Code: Select all

SELECT * FROM table WHERE field IN('english','japanese');

Posted: Mon Jan 24, 2005 12:50 pm
by robtbs
thanks for your reply :(
unfortunately iv already tried that method...

and Group By
both queries return no results at all...
even though there are quite a few products listed in both the categories im trying...
:(

Posted: Mon Jan 24, 2005 2:51 pm
by JAM
This might be the issue...

Code: Select all

Notice: Undefined variable: first in page.php on line XXX
SELECT DISTINCT(product) FROM table WHERE OR category = 'foo' OR category = 'bar' OR category = 'muu'
Seems as it doesn't see that the $first is set the first time, hence jumping down to the else () statement, making the query fubar.Notice the echoed out query...

Add...

Code: Select all

$catsql = '';
$first = TRUE;
...before the foreach loop and see what that gets you. (Fixing another issue the no declared variables).

You might want to bump up the error reporting abit if this is on a production server...