SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ip2long MySQL equivalent

    Hi,

    Anyone got an equivalent method of PHP's ip2long() for MySQL (3.23.x)
    Thanks
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  2. #2
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I created a pretty ugly one! Anyone has some improvement, let me know. Here is it

    Code:
    SELECT IF( SUBSTRING_INDEX($ip,'.',1)*16777216 
    + SUBSTRING(SUBSTRING_INDEX($ip,'.',2),1+LOCATE('.',$ip))*65536
    + SUBSTRING(SUBSTRING_INDEX($ip,'.',-2),1,-1+LOCATE('.',SUBSTRING_INDEX($ip,'.',-2)))*256 
    + SUBSTRING_INDEX($ip,'.',-1)
    > 2147483647,
    SUBSTRING_INDEX($ip,'.',1)*16777216
    + SUBSTRING(SUBSTRING_INDEX($ip,'.',2),1+LOCATE('.',$ip))*65536
    + SUBSTRING(SUBSTRING_INDEX($ip,'.',-2),1,-1+LOCATE('.',SUBSTRING_INDEX($ip,'.',-2)))*256
    + SUBSTRING_INDEX($ip,'.',-1)
    - 4294967296,
    SUBSTRING_INDEX($ip,'.',1)*16777216
    + SUBSTRING(SUBSTRING_INDEX($ip,'.',2),1+LOCATE('.',$ip))*65536
    + SUBSTRING(SUBSTRING_INDEX($ip,'.',-2),1,-1+LOCATE('.',SUBSTRING_INDEX($ip,'.',-2)))*256
    + SUBSTRING_INDEX($ip,'.',-1))";
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, sql is particularly clumsy when it comes to repetitively scanning a field for a character and pulling out substrings

    i mean, it would be rather straightforward if all of the 4 pieces of the IP number were exactly three digits, but they typically aren't

    i wrote something similar in microsoft access, it's really ugly too, perhaps even uglier than yours

    http://searchdatabase.techtarget.com...285649,00.html

    rudy


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
  •