Hi all!

Ok, I have a MySQL log whose records each contain an IP (in addition to other fields) and I am wanting to remove those records where the IP matches my blacklist array.

$blacklist = array (
'192.168.1.1',
'192.168.0.',
);

Now, for a full IP (192.168.1.1) it is easy:

DELETE FROM [tablename] WHERE [fieldname] IN ('".implode("','", $blacklist)."')

But, how could I remove those records who's IP start with one of the values in the array? In other words, a wildcard as so: 192.168.0.*

In PHP this would be easy to just match the left substring of the IP in the record with that of the array value ('192.168.0.'), or eregi, etc

How can this be done the efficient MySQL way?

Thanks for any help!

Store your blacklist in a (temporary) table and use MySQL's multiple table delete syntax with a SUBSTRING() condition.

--
felix

Ok, I'm confused.

Since the wildcard IPs in the blacklist table would be of varying length (ex. 1.2.3. or 111.222.333.), wouldn't I have to get the length of each of those wilcard IPs first in order to set the substring length? And, if so, how would that be cycled? :0

I guess I could specify the wildcard length as a seperate field in the blacklist table, but I still don't understand how I would compare each of the main log's record IPs based on the length field of each of the wildcard IPs in the blacklist table.

Thanks again, felixg!