How to specify named parameter value for NOT IN clause in MySQL query?

Question

I have written the following SQL query:

$media_category_ids = array( 11, 12);
$params = array();
$sql = "SELECT `id`
    FROM query
    WHERE 1=1
    AND WHERE query.media_category_id NOT IN (:media_category_ids)";
$params['media_category_ids'] = implode(",",$media_category_ids);
$prepared_query = $c->prepare($sql);
$prepared_query->execute($params);

However I can't seem to get the syntax for the named parameter in the 'NOT IN' clause correct as I get the following error:

Message: An exception occurred while executing 'SELECT `id` FROM query WHERE 1=1 AND WHERE query.media_category_id NOT IN (:media_category_ids)': SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE query.media_category_id NOT IN ('11,2')' at line 4

Really appreciate it if someone can point me in the right direction.

Just for info: Make sure you know where the ids come from before using implode. If they came from an internal query then fine. If they were posted from the browser then you need to escape them or at the very least, convert them to integers to avoid injection attacks. — Cerad, Feb 13 '14 at 16:31

score 1 · Accepted Answer · answered Feb 13 '14 at 15:12

1

Remove additional WHERE after AND operator

AND WHERE query.media_category_id  
    ^here

answered Feb 13 '14 at 15:12

M Khalid Junaid

63,861
10
90
118

score 0 · Answer 2 · answered Feb 13 '14 at 15:12

0

Use following query, no another WHERE needed:

SELECT `id`
    FROM query
    WHERE 1=1
    AND query.media_category_id NOT IN (:media_category_ids)

answered Feb 13 '14 at 15:12

aksu

5,221
5
24
39

How to specify named parameter value for NOT IN clause in MySQL query?

2 Answers2