searching different values for one field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
robtbs
Forum Newbie
Posts: 2
Joined: Mon Jan 24, 2005 6:54 am
Location: Manchester, UK

searching different values for one field

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

look at the IN syntax:

Code: Select all

SELECT * FROM table WHERE field IN('english','japanese');
robtbs
Forum Newbie
Posts: 2
Joined: Mon Jan 24, 2005 6:54 am
Location: Manchester, UK

Post 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...
:(
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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...
Post Reply