CODEWITHSUNDEEP
phpmysqlsqldatabase
Abilash Erikson
Abilash Erikson

Reputation: 351

combination of multiple AND , NOT IN condition is not working properly in php mysql query

Hi i write the following query

SELECT *
FROM `product`
WHERE (`color` IN ('black'))
   OR (`pattern` IN ('pattern-1', 'pattern-2', 'pattern-3'))
  AND `p_category` LIKE '%Blind%'
  AND id NOT IN('20', '19', '18', '17', '11')
ORDER BY id DESC

but for some reason it showing the product with id '20', '19', '18', '17', '11'

i think this information is enough for to solve this .

Update

Here i cannot figure out where i need to change . Here i am using ajax . All the operation is take place in that page . Post variable getting to ajax called page the operation i performed is 

$keyword_array= ( [keyword] => Array ( 
                    [0] => color*black 
                    [1] => pattern*pattern-1
                    [2] => pattern*pattern-2
                    [3] => pattern*pattern-3 ) 
        [y_id] => Array ( 
                    [0] => 20
                    [1] => 19
                    [2] => 18
                    [3] => 17
                    [4] => 11 ) 
       [category] => Romex Blind );


       foreach ( $keyword_array as $value) {
            list($before, $after) = explode('*', $value);
            $elements[$before][] = $after;
        }

        $parts = [];

        foreach ($elements as $column => $values) {
            $parts[] = "(`$column` IN ('" . implode("', '", $values) . "'))";
        }

        $appear=implode("', '", $_POST['y_id']);

        $query = "SELECT * FROM `product` WHERE " . implode(' OR ', $parts). "  AND `p_category` LIKE '%".$_POST['category']."%' AND id NOT IN('{$appear}') ORDER BY id DESC";

Please tell what is the error in this query .

Upvotes: 1

Views: 564

Answers (2)

Melvin Angelo Jabonillo
Melvin Angelo Jabonillo

Reputation: 59

You may write query like this

SELECT * FROM product WHERE (color = 'black' OR pattern IN ('pattern-1', 'pattern-2', 'pattern-3')) AND p_category LIKE '%Blind%' AND id NOT IN (20, 19, 18, 17, 11) ORDER BY id

You may use only OR if there are two or more conditions and just need one answer like

in you case you query seems wrong data will get

Upvotes: -1

Jens
Jens

Reputation: 69470

make breakets around the or expresseion:

SELECT *
FROM `product`
WHERE (`color` IN ('black')
   OR `pattern` IN ('pattern-1', 'pattern-2', 'pattern-3'))
  AND `p_category` LIKE '%Blind%'
  AND id NOT IN('20', '19', '18', '17', '11')
ORDER BY id DESC

UPDATE try

 $query = "SELECT * FROM `product` WHERE (" . implode(' OR ', $parts). ")  AND `p_category``

Upvotes: 4

Related Questions