I have a PHP array with a bunch of numbers in it like this
ARRAY{1, 4, 8, 25, 36, 109}

And I want to do a select like this

SELECT name FROM orders WHERE id NOT IN $array[];

Basically, where the "id" number is not in the php array. Anyone know how to write this statement?

thx

Well, you could probably do something as simple as this:
$statement = "SELECT name FROM orders WHERE id NOT IN ( " . join(',', $array) . ")";
BUT... What do those numbers represent? Have they come from another database query? If so then you're probably going about this the wrong way.

Thanks for the quick reply! I'll give this a try.

Nope, this array of data isn't coming from a query. My actual query doesn't involve customer orders, but rather some statistical information, and the array will be filled with calculated integers. It won't be possible to determine the numbers beforehand or even how many of them there will be. So, this php array + query seems to do the trick for my situation.

I think what foobic was getting at is, how do you know what DB table ID numbers you want to query.

If you know this then you have no problem. Just usually a query is dynamic, that is you don't know what results are coming from the db until the query searches the table and finds the results.

Usually it would be another data field in your table you would want to not include in your query.
Such as don't query if table field name is "DVD" or "TV" etc

Perhaps you work with ID's, which isn't wrong, but it's not often you would do this as the ID's are usually automatically incremented by the db as an index.
But if you know your ID numbers and which one is for what, or you use a different index other than ID then your fine. Of course if ID is your table index, then don't forget any changes to the table, i.e. deleting rows, might change your expected results if you allow the database to increment the lowest value available, rather than the next value up from the last one used.

I understand what he is saying. I just used the id, order, customer setup as an example. My actual query uses much different data, but the concept of WHERE IS NOT IN is what I needed help with.

I do have another related question though. I have a query that ends like this:
WHERE id = (0 || 1 || 3 || 4)
I don't think that will work. I could write it all out, but is there a more efficient way?

think what foobic was getting at is, how do you know what DB table ID numbers you want to query

As I've explained before, I'm not using id numbers in my real query. That was just a simplified query so I could ask a question about the "WHERE NOT IN" statement.

I understand what he is saying. I just used the id, order, customer setup as an example. My actual query uses much different data

No problem, glad you got it sorted.
You can include your real query names you know, no one is going to hack your site from knowing your query values. If it's hackable with knowing those it's unsecure anyway.
It just helps sometimes to know exactly what you are working with (as you see above)

As for your other question, I have to ask as your first question was a bit misleading with the example you gave..
Are you only querying numbers where id = 0-4? or does this list go onto a larger number?

Maybe these links might help, now or in the future
http://dev.mysql.com/doc/refman/4.1/en/comparison-operators.html
http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html

For my 2nd question, this is a real query. I just need to select the rows where id is one of the following 0, 1, 3, or 4

Would that be "WHERE id IN (0, 1, 3, 4)"? Is that the correct syntax?

Okay, i think I have the WHERE IN syntax correct. It has been awhile since I've used the IN() clause. Thanks for your help.

Have you used implode() php function for the array and put it in the array.That will work with using IN

Join / implode are the same function but implode is mostly the appropriate one to use within SQL queries. If you're experiencing problems, by using either of them, you should check your array's dimensional size before attempting to inject / update any of your fields in the database. ;)

Join is an alias of implode - the two are completely interchangeable. (I tend to use join because I learned perl before PHP.)

But in most cases neither is appropriate to use in SQL queries. If you find you need a statement like this then you should look very carefully at your database schema and other queries because it's very likely you're doing something wrong. It sounds like the OP has a rare case where this use is actually necessary.