Question

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

Answer 1

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

Answer 2

保证、改变

和c.id_ category NOT IN (VAR_ARRAY)

Answer 3

为此:

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

Answer 4

我认为你可以使用

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

Answer 5

您可使用IN操作员,检查价值是否列在价值清单中,以便你能够写上类似情况。

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

由于说明含有这种形式的价值,你可以提出这样的问题:

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

但是,如果这种扼杀来自外部来源,那么你就应当检查这种对无效价值的扼杀,以防 SQL。它最能探索这种扼杀,使之具有单独价值,使其中每一个国家都参与进来,并从这些价值中形成新的特征。这样,你就防止了卡片注射。

友情链接