开发者

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_.'custome开发者_StackOverflow中文版r_group 
                                                                  WHERE id_customer = '.intval($cookie->id_customer).')').'
 ORDER BY `level_depth` ASC, cl.`name` ASC'


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


Sure, change it in

AND c.id_category NOT IN (VAR_ARRAY)


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'


I think you can use

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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜