使用NOT IN和PHP数组形成正确的SQL查询时遇到一些麻烦

Question

ok, I'm not too schooled on SQL yet and what I want to do is, I have an array of search engine bots and IP's (or partial IP's) that I want to exclude from being returned in the SQL results.

The array is already properly formed for the SQL query and looks like this (and is stored in $sql_exclude):

'googlebot', 'crawl', 'spider', 'bluehost', 'amazonaws', 'msnbot', 'surphace-scout', 'scoutjet', 'facebook', 'tfbnw.net', 'digg', 'spinn3r', 'favsys.net', 'trendnet.org', '72.14.192.', '72.14.194.'

The query checks against both the ip and user_agent columns in the db, to see if either of those contain any of the items in the array, and if so, I don't want them returned in the results.

Right now, this is the query I have tried and makes the most "sense" to me, but it's not returning anything at all. Can anyone point out where the error is?

$sql = "SELECT w.* FROM {bad_behavior_log} w WHERE ($sql_exclude) NOT IN w.user_agent AND ($sql_exclude) NOT IN w.ip " . tablesort_sql($header);

Also, since user agents are mixed case, is that an issue? And/or when a user agent it's checking against has the word "GoogleBot" in it and the array term is just "google", will the SQL query automatically find the partial match? (for the partial IP's as well).

Update: For the sake of thoroughness, and in case anyone reads this later, I solved my issue with 2 things:

1) For some reason, not sure if it's my Host's setup (Bluehost), I needed to convert all search strings for my comparison operators (NOT LIKE) into uppercase, or it would arbitrarily match some and not match some mixed case searches.

2) The parenthesis around my SQL variables were also causing the query to choke. i.e. ($sql_exclude) should've been just $sql_exclude.

Here's my final, working, query string: $sql = "SELECT w.* FROM {bad_behavior_log} w WHERE w.user_agent NOT LIKE $sql_exclude_agents AND w.ip NOT LIKE $sql_exclude_ips" . tablesort_sql($header);

and the strings in those variables look like this: $sql_exclude_agents = "'%".implode("%' AND w.user_agent NOT LIKE '%", $exclude_agents)."%'";

(turns into) NOT LIKE '%GOOGLE%' AND w.user_agent NOT LIKE '%YAHOOCACHESYSTEM%' AND w.user_agent NOT LIKE '%RSSGRAFFITI%' AND w.user_agent NOT LIKE '%BITLYBOT%' (etc.)

Answer 1

You are using NOT IN incorrectly, the format should be column NOT IN ($sql_exclude) as opposed to the reverse. I.e. it should be followed by the list of items to check instead of being proceeded by it.

$sql = "SELECT w.* FROM {bad_behavior_log} w WHERE w.user_agent NOT IN ($sql_exclude) AND w.ip NOT IN ($sql_exclude) " . tablesort_sql($header)