SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2000
    Location
    Bangkok,Thailand
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to bar a range of IP's in a SELECT statement

    I want to bar a range of IP's in a SELECT statement that pulls details from a cookie stats table. We have a range of IP addresses - let's say XXX.XXX.XXX.2 to XXX.XXX.XXX.64 what would the syntax be to not pull the records that do not correspaond to these IP's so as to discount traffic from the office for stat reports. I have tried something along the line of the following:

    SELECT TOP 10 URL, COUNT(URL) AS urlcount FROM UserTest WHERE (URL LIKE 'XYZ' AND URL NOT LIKE '%ABC%' AND thetime BETWEEN '8/1/2001' AND '9/1/2001') AND ((Remote NOT LIKE 'xxx.xxx.xxx.64') OR (Remote NOT LIKE xxx.xxx.xxx.65') OR (Remote NOT LIKE xxx.xxx.xxx.66') etc etce etc GROUP BY URL ORDER BY urlcount DESC,URL


    It it does not discount enough of the traffic from the office.

    Does anyone have any suggestions?

    Thanks

    stuart
    http://www.travelfish.org

  2. #2
    SitePoint Enthusiast Atrus's Avatar
    Join Date
    Aug 2001
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you running MySQL? It supports regular expressions in the LIKE statement.

    Instead of :

    Remote LIKE ...


    Try this (not tested) :

    Remote REGEXP "^xxx.xxx.xxx.([2-9]|[1-5][0-9]|6[0-4])$"

    Do you know regexps? This should not need fixes, though.

    Regards,

    Atrus.
    Webmaster - Stefan Meier KG TABAKWAREN - Pfeifen, Premium-Zigarren, ... (_Ger)

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2000
    Location
    Bangkok,Thailand
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, unfortunately I'm using SQL7 - though I'm dashing to the resources now to see if something similar is supported. Never thought of looking at it this way - thanks for the tip.

    Stuart
    http://www.travelfish.org

  4. #4
    SitePoint Enthusiast Atrus's Avatar
    Join Date
    Aug 2001
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tell us whether you find something, ok?

    A little update: At second thought I'd add some extra paranthesis to the regexp, just to make sure that things that belong together stay together.
    Remote REGEXP "^xxx.xxx.xxx.([2-9]|([1-5][0-9])|(6[0-4]))$"

    The 'xxx' are to be a static first part of the address range like in your initial post.

    Atrus.
    Webmaster - Stefan Meier KG TABAKWAREN - Pfeifen, Premium-Zigarren, ... (_Ger)


Bookmarks

Posting Permissions

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