search LIKE and NOT LIKE

[twinzen]

hi everybody,
i have the following code to search a database. but the first to parts - LIKE and NOT LIKE are giving so confusing results that i have no idea whats wrong. rest of the code seems to work - i put it here so you'd get the whole pictue of what i'm trying to do.

<?php
//if we have inforamtion from form (a hidden field)
if($_POST['form'] == 2)
{
//if we have the words that have to be in
if(strlen($_POST['string']))
{
 $xp = explode(" ", $_POST['string']);
 $count = count($xp);
//if more than one word was inserted
 if($count > 1)
 {
  foreach($xp as $row)
  {
//wheter we need a connector and is it then AND or OR
   if($i == $count - 1)
   {
   $and2 = "";
   }
   else
   {
//$_POST['and2'] - is either AND or OR
   $and2 = $_POST['and2'];
   }
  $srch .= "(field1 LIKE '%".$row."%' OR field2 LIKE '%".$row."%' OR field3 LIKE '%".$row."%')".$and2;
  $i++;
  }
 }
 else
 {
//not much need for this though
  $srch = "field1 LIKE '%".$_POST['string']."%' OR field2 LIKE '%".$_POST['string']."%' OR field3 LIKE '%".$_POST['string']."%'";
 }
 $x = "(".$srch.")";
 $and=" AND ";
}
//if we have words taht should no be in, basically same as above
if(strlen($_POST['string2']))
{
 $xp = explode(" ", $_POST['string2']);
 $count = count($xp);
 if($count > 1)
 {
  foreach($xp as $row)
  {
   if($i == $count - 1)
   {
   $and2 = "";
   }
   else
   {
//$_POST['and3'] - is either AND or OR
   $and2 = $_POST['and3'];
   }
  $srch2 .= "(NOT(field1 LIKE '%".$row2."%') AND NOT(field2 LIKE '%".$row2."%') AND NOT(field3 LIKE '%".$row2."%'))".$and3;
  $i++;
  }
 }
 else
 {
  $srch2 = "NOT(field1 LIKE '%".$_POST['string2']."%') AND NOT(field2 LIKE '%".$_POST['string2']."%') AND NOT(field3 LIKE '%".$_POST['string2']."%')";
 }
$x = $and."(".$srch2.")";
 $and=" AND ";
}
//if type is defined (not relevant - this works)
if(strlen($_POST['type']))
{
$type = $and."type IN ('".implode("','",$_POST['type'])."')";
 $and=" AND ";
}
else
{
$type = $and."tyyp IN ('a','b','c','d')";
 $and = " AND ";
}
//if min is defined (not relevant - this works)
if(strlen($_POST['min']))
{
$min = $and."value >= '".$_POST['min']."'";
 $and=" AND ";
}
//if max is defined (not relevant - this works)
if(strlen($_POST['max']))
{
$max = $and."value <= '".$_POST['max']."'";
}

$query = "SELECT * FROM Table WHERE ".$x.$x2.$type.$min.$max;
$query2 = mysql_query($query);

while($row = mysql_fetch_array($query2))
{
echo $row['id']." - ".$row['field2']."<br>";
}
}
?>

thanks in advance!

[tomhath]

You should echo the query and look it over.

This line is probably part of the problem, you should have parenthesis around the OR'd together conditions:

 //not much need for this though
  $srch = "field1 LIKE '%".$_POST['string']."%' OR field2 LIKE '%".$_POST['string']."%' OR field3 LIKE '%".$_POST['string']."%'";
 

ALso, I see at least one typo, might be others:

if(strlen($_POST['type']))
{
$type = $and."type IN ('".implode("','",$_POST['type'])."')";
 $and=" AND ";
}
else
{
$type = $and."tyyp IN ('a','b','c','d')";
 $and = " AND ";
}

[twinzen]

the echoed statement looks like this

SELECT * FROM Toode WHERE ((brand LIKE '%IN1%' OR mark LIKE '%IN1%' OR lyhitutvustus LIKE '%IN1%') AND (brand LIKE '%IN2%' OR mark LIKE '%IN2%' OR lyhitutvustus LIKE '%IN2%')) AND ((NOT(brand LIKE '%NOT1%') AND NOT(mark LIKE '%NOT1%') AND NOT(lyhitutvustus LIKE '%NOT1%')) AND (NOT(brand LIKE '%NOT2%') AND NOT(mark LIKE '%NOT2%') AND NOT(lyhitutvustus LIKE '%NOT2%'))) AND tyyp IN ('digi','peegel','kompakt','lisa') AND hind >= '1000' AND hind <= '2000'

or like this

SELECT * FROM Toode WHERE ((brand LIKE '%IN1%' OR mark LIKE '%IN1%' OR lyhitutvustus LIKE '%IN1%') OR (brand LIKE '%IN2%' OR mark LIKE '%IN2%' OR lyhitutvustus LIKE '%IN2%')) AND ((NOT(brand LIKE '%NOT1%') AND NOT(mark LIKE '%NOT1%') AND NOT(lyhitutvustus LIKE '%NOT1%')) OR (NOT(brand LIKE '%NOT2%') AND NOT(mark LIKE '%NOT2%') AND NOT(lyhitutvustus LIKE '%NOT2%'))) AND tyyp IN ('digi','peegel','kompakt','lisa') AND hind >= '1000' AND hind <= '2000'

but it is not working. for instance, if i search for fields including 'a' i get, say, 100 answers. if i search for fields not including 'a' i ger 300 answers - but altogether there are more than thousand fields!

or maybe i have completely misused LIKE for searching but it seemed a right and logical thing to use...

please help