2

I have an array which contains a bunch of ID:s...

I can't figure out how to write a query for finding all records which are NOT inside this array, in mysql.

    SELECT * FROM main_table WHERE ..........

Any ideas?

Thanks

2 Answers2

11

Like this:

$str = implode(',', $your_array);

The above statements converts an array into comma-delimited string.

"SELECT * FROM main_table WHERE id NOT IN ('$str')"

More Info:

Sarfraz
  • 377,238
  • 77
  • 533
  • 578
  • 2
    Little tweak: `$str = count($your_array) != 0 ? implode(',', $your_array); : "null";`. `IN` doesn't react well to empty braces. – Femaref Aug 15 '10 at 18:35
  • I just did the same. But as my array grows in size I received "MySQL server has gone away" error. I found several solutions how to tweak MySQL to swallow such requests but I feel that this solution is not very suitable for a large arrays. – Vlada Katlinskaya Jul 14 '15 at 07:17
  • 1
    I tried this sql statement applied to the WordPress wp_users table and user_email field, with a comma delimited string of five email addresses as the list. It doesn't work, returning all email addresses. I just found out it doesn't work because the list (or if you use implode()) doesn't wrap each list item with a ' (opening and closing apostrophe). So, implode(",",$str) produces "user1@gmail.com,user2@mydomain.com,user3@mydomain.com". That fails the sql. The list has to be constructed like this: WHERE user_email NOT IN ('user1@gmail.com','user2@mydomain.com','user3@mydomain.com')"; – TARKUS Nov 12 '15 at 16:34
5
SELECT * 
  FROM main_table
 WHERE id NOT IN(1, 2, 3)
Aillyn
  • 23,354
  • 24
  • 59
  • 84