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.
精彩评论