MYSQL Multiple LIKE not working

[imperium2335]

How do I get LIKE to check multiple values? It works if you type in a single word, but not multiple, which I want it to in order to get an accurate search result. Here is what I am trying to do:

$target = "siemens 6s" ;

$multis = explode(" ", $target) ;

$query = "SELECT parts_table.*, manufacturers.* FROM parts_table, manufacturers WHERE parts_table.ManufacturerID = manufacturers.ManufacturerID AND parts_table.PartNumber LIKE" ;

foreach($multis as $current) {

$queryext .= " '%$current%' OR" ;

} $queryext = substr($queryext, 0, -3) ; // Remove the last 4 charecters i.e. " AND".

$query .= $queryext ;

echo $query .= " OR manufacturers.ManufacturerID = parts_table.ManufacturerID AND manufacturers.ManufacturerName LIKE" . $queryext ;



include("dbconnect.php") ;

$result = mysql_query($query) ;

while($row = mysql_fetch_assoc($result)){

echo $row['ManufacturerName'] ;
echo $row['PartNumber'] ;

}

[bh]

in your foreach at

$queryext .= " '%$current%' OR" ;

after the first cycle you havent got 'LIKE' before your '%$current%' ....

[imperium2335]

Not sure what you mean, if i echo the finished query it looks like this:

 

SELECT parts_table.*, manufacturers.* FROM parts_table, manufacturers WHERE parts_table.ManufacturerID = manufacturers.ManufacturerID AND parts_table.PartNumber LIKE '%siemens%' OR '%6s% OR manufacturers.ManufacturerID = parts_table.ManufacturerID AND manufacturers.ManufacturerName LIKE '%siemens%' OR '%6s%

 

[iSE]

it should be

 

WHERE field = value  OR field = value OR field = value...

 

so you should have

 

WHERE field LIKE value OR field LIKE value

 

as LIKE as an opperand.

[bh]

 LIKE '%siemens%' OR '%6s% OR manufacturers.ManufacturerID = parts_table.ManufacturerID AND manufacturers.ManufacturerName LIKE '%siemens%' OR '%6s%

 

Well, wheres your 'LIKE' clause eg. before your %6s% ?

[imperium2335]

Thanks! but it still doesnt work :

 

SELECT parts_table.*, manufacturers.* FROM parts_table, manufacturers WHERE parts_table.ManufacturerID = manufacturers.ManufacturerID AND parts_table.PartNumber LIKE '%siemens%' OR LIKE '%6SN%' OR manufacturers.ManufacturerID = parts_table.ManufacturerID AND manufacturers.ManufacturerName LIKE '%siemens%' OR LIKE '%6SN%'

 

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in part-search-new.php on line 33

[imperium2335]

Is there any way to match and compare substrings in mysql queries as im having a hard time getting my search function to work if the user types in more than one word?

[imperium2335]

I have also just tried this with no succes:

 

SELECT parts_table.*, manufacturers.* FROM parts_table, manufacturers WHERE parts_table.ManufacturerID = manufacturers.ManufacturerID AND WHERE 'siemens 6SN' IN parts_table.PartNumber OR manufacturers.ManufacturerID = parts_table.ManufacturerID AND WHERE 'siemens 6SN IN manufacturers.ManufacturerName

[iSE]

The format of your statements must be

 

SELECT cols FROM table WHERE field OPERAND value LOGIC field OPERAND value LOGIC field OPERAND value...

 

So...

 

SELECT parts_table.*, manufacturers.* FROM parts_table, manufacturers WHERE parts_table.ManufacturerID = manufacturers.ManufacturerID AND parts_table.PartNumber LIKE '%siemens%' OR parts_table.PartNumber LIKE '%6SN%' OR manufacturers.ManufacturerID = parts_table.ManufacturerID AND manufacturers.ManufacturerName LIKE '%siemens%' OR manufacturers.ManufacturerName LIKE '%6SN%'

 

That work?

[imperium2335]

No, now it's bringing back all the stuff with "siemens" in:

 

SELECT parts_table.*, manufacturers.* FROM parts_table, manufacturers WHERE parts_table.ManufacturerID = manufacturers.ManufacturerID AND parts_table.PartNumber LIKE '%siemens%' OR parts_table.PartNumber LIKE '%6SN2460-2CF00-0GB0%' OR manufacturers.ManufacturerID = parts_table.ManufacturerID AND manufacturers.ManufacturerName LIKE '%siemens%' OR parts_table.PartNumber LIKE '%6SN2460-2CF00-0GB0%'

 

I just want it to bring back "siemens 6SN2460-2CF00-0GB0" i.e. 1 part (which there is only one of).

 

Typing in just 6SN2460-2CF00-0GB0 will bring back one record like it should.

 

Siemens will bring back all siemens parts which is correct to.

 

But "siemens 6SN2460-2CF00-0GB0" brings back the same results as typing in just siemens.

 

Any idea how I can get it to work properly? Thanks!

[DavidAM]

If you are going to mix AND with OR you have to mark the groups that go together (using parenthesis):

SELECT parts_table.*, manufacturers.* 
FROM parts_table, manufacturers 
WHERE (parts_table.ManufacturerID = manufacturers.ManufacturerID 
  AND (parts_table.PartNumber LIKE '%siemens%' 
    OR parts_table.PartNumber LIKE '%6SN2460-2CF00-0GB0%'))
OR (manufacturers.ManufacturerID = parts_table.ManufacturerID 
  AND (manufacturers.ManufacturerName LIKE '%siemens%' 
    OR parts_table.PartNumber LIKE '%6SN2460-2CF00-0GB0%'))

 

Actually, if you use a JOIN here, you can eliminate that issue since the ManufacturerID comes out of the WHERE clause:

SELECT parts_table.*, manufacturers.* 
FROM parts_table JOIN manufacturers 
  ON parts_table.ManufacturerID = manufacturers.ManufacturerID 
WHERE parts_table.PartNumber LIKE '%siemens%' 
  OR parts_table.PartNumber LIKE '%6SN2460-2CF00-0GB0%'
  OR manufacturers.ManufacturerName LIKE '%siemens%'