CODEWITHSUNDEEP
phpsqlmysql
Dan
Dan

Reputation: 13

PHP MySQL != /w List of values

Is it possible to select data with a single query from a mysql table with a string of values? I have the following query and am trying to retrive results where the c.id_category is not equal to any of the values in the string. which would consist of something like "1,67,23,34,65"

   'SELECT DISTINCT c.*, 
          cl.*
     FROM `'._DB_PREFIX_.'category` c 
LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` 
                                           AND `id_lang` = '.intval($params['cookie']->id_lang).')
LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
    WHERE 1'.(intval($maxdepth) != 0 ? ' 
      AND `level_depth` <= '.intval($maxdepth) : '').'
      AND (c.`active` = 1 OR c.`id_category`= 1)
      AND c.`id_category` != VAR_ARRAY
      AND cg.`id_group` '.(!$cookie->id_customer ?  '= 1' : 'IN (SELECT id_group 
                                                                   FROM '._DB_PREFIX_.'customer_group 
                                                                  WHERE id_customer = '.intval($cookie->id_customer).')').'
 ORDER BY `level_depth` ASC, cl.`name` ASC'

Upvotes: 1

Views: 1679

Answers (5)

GolezTrol
GolezTrol

Reputation: 116140

You can use the IN operator to check if a value is in a list of value, so you could write the condition like

AND c.id_category in (1,67,23,34,65)

Because the string contains the value in that format, you could build the query like this:

"AND c.id_category in (" . VAR_ARRAY . ")"

But if this string comes from an external source, you should check this string for invalid values to prevent SQL injection. It's best to explode the string to separate value, cast each of them to int, and build a new string from those values. That way, you'll prevent SQL injection.

Upvotes: 0

VoodooChild
VoodooChild

Reputation: 9784

I think you can use 

SELECT * 
FROM TABLE t
WHERE t.id_category NOT IN ('1', '67', '23', '34', '65')

Upvotes: 0

jb1785
jb1785

Reputation: 742

Try this:

SELECT DISTINCT c.*, cl.*
  FROM `'._DB_PREFIX_.'category` c 
  LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.intval($params['cookie']->id_lang).')
  LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
  WHERE 1'
  .(intval($maxdepth) != 0 ? ' AND `level_depth` <= '.intval($maxdepth) : '').'
  AND (c.`active` = 1 OR c.`id_category`= 1)
  AND c.`id_category` NOT IN (VAR_ARRAY)
  AND cg.`id_group` '.(!$cookie->id_customer ?  '= 1' : 'IN (SELECT id_group FROM '._DB_PREFIX_.'customer_group WHERE id_customer = '.intval($cookie->id_customer).')').'
  ORDER BY `level_depth` ASC, cl.`name` ASC'

Upvotes: 0

Mr Griever
Mr Griever

Reputation: 4023

Something like: SELECT * FROM Foo WHERE bar NOT IN (1,67,23,34,65); sounds like it would fit your needs.

Upvotes: 2

Ass3mbler
Ass3mbler

Reputation: 3925

Sure, change it in

AND c.id_category NOT IN (VAR_ARRAY)

Upvotes: 1

Related Questions