Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Location
    Austin, TX
    Posts
    10

    Unanswered: How do you wildcard matches with WHERE IN?

    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!

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by Ahhk
    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.

    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
    Store your blacklist in a (temporary) table and use MySQL's multiple table delete syntax with a SUBSTRING() condition.

    --
    felix

  3. #3
    Join Date
    Sep 2005
    Location
    Austin, TX
    Posts
    10
    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!
    Last edited by Ahhk; 10-13-05 at 13:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •