mysql search using php array

[severndigital]

OK so i have a search query for Mysql that looks like this.

 

SELECT *
FROM prod_list
WHERE name LIKE '%$search%'
OR short_desc LIKE '%$search%'
OR description LIKE '%$search%'
AND access_levels LIKE '%$user_level%'
ORDER BY {$orderby} {$ordermeth}
LIMIT {$startRange},{$endRange}";

 

I would like to add to it and search through a php array against another field.

 

$items = array(1,10,11,15,17);

$sql = "SELECT *
FROM prod_list
WHERE name LIKE '%$search%'
OR short_desc LIKE '%$search%'
OR description LIKE '%$search%'
AND access_levels LIKE '%$user_level%'
AND cat_id IN ......HOW DO I SEARCH AGAINST THE ARRAY????
ORDER BY {$orderby} {$ordermeth}
LIMIT {$startRange},{$endRange}";

 

how do i search against the array??

 

I have tried the following with no success.

$items = implode(',',$items);

$sql = "SELECT * ....
AND cat_id IN '$items'
....";

//and also
$sql = "SELECT * ....
AND cat_id ('".join("','", $items)."')
....";

 

Both had bad results.

 

 

 

 

 

 

 

Thanks in advance,

C

[pocobueno1388]

Try using implode()

 

$sql = "SELECT *
FROM prod_list
WHERE name LIKE '%$search%'
OR short_desc LIKE '%$search%'
OR description LIKE '%$search%'
AND access_levels LIKE '%$user_level%'
AND cat_id IN (". implode(',', $items) .")
ORDER BY {$orderby} {$ordermeth}
LIMIT {$startRange},{$endRange}";

[severndigital]

I tried that originally but it doesn't seem to work.

 

//the cat_id for it items in this test should be 13

//the array would be
$items = array(10,11,12,18);

when I search i get the results, but they all have a cat_id of 13 where it should be returning Zero results, since they do not match the array

 

Any ideas??

 

[fenway]

You have an order of operations issue... use parens.

[severndigital]

can you please elaborate?

 

I thought i have parens around the IN query.

 

do i need to paren all the querys?

[fenway]

Your ORs and ANDs are getting mixed up.

 

$sql = "SELECT *
FROM prod_list
WHERE ( name LIKE '%$search%'
OR short_desc LIKE '%$search%'
OR description LIKE '%$search%' )
AND access_levels LIKE '%$user_level%'
AND cat_id IN (". implode(',', $items) .")
ORDER BY {$orderby} {$ordermeth}
LIMIT {$startRange},{$endRange}";