Checking if values exist in the database

I got such a great answer to my last MySQL question, I thought I’d try again.

I get some values from a snippet parameter, and explode() that into an array. Is there a way to query the database to make sure that all those values do in fact exist in the specified field in the database?

This is a parameter called "categories". I need to check a "category" field in the database to make sure that there is at least one row with the "category" field set to match each of these "categories" from the snippet parameter. If one of those parameters isn’t found in the database, an error needs to be returned so the snippet call can be corrected.

Now, I can loop through the array using a SELECT COUNT(*) WHERE category = ... query for each of these items, but what if there gets to be a dozen, or two dozen "categories"? If there is some query that can be run against an array in a single query, it would be much better. Even if all it did was return an error if one of the values wasn’t found in the table it would be sufficient.

???

No, all that does is check if a given value is in the given array It can be used with a subquery, but that doesn’t help me here.

Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described in Section 11.2.2, “Type Conversion in Expression Evaluation”, but applied to all the arguments.

mysql> SELECT 2 IN (0,3,5,7);
-> 0
mysql> SELECT ’wefwf’ IN (’wee’,’wefwf’,’weg’);
-> 1

I think I’ll run a query with DISTINCT, then use PHP’s array handling to compare the two arrays. I was just wondering if I could accomplish the same thing as part of the query.

I have no idea what you are trying to accomplish, but using WHERE category_column IN (’foo’, ’bar’) will check if the value of the category column is foo or bar. Maybe I don’t understand your goal or data.

Ah! I had the whole group in one set of quote marks, that’s why it didn’t work! It was very puzzling...

Anyway, that doesn’t tell me if a value was entered in the parameter that doesn’t exist in the table’s ’category’ field for any of the records.

Quote from: sottwell at Mar 31, 2010, 04:42 PM

Anyway, that doesn’t tell me if a value was entered in the parameter that doesn’t exist in the table’s ’category’ field for any of the records.

No, but NOT IN will.

After I got it all worked out, I sat looking at it for a while, trying to decide what to do with it, and then asked myself why I even thought I needed to do this. Well, at least I learned something.