note 40986 deleted from function.ip2long by nlopess
| From: | nlopess@php.net | Date: | Thu, 25 Mar 2004 15:24:54 +0000 |
| Subject: | note 40986 deleted from function.ip2long by nlopess | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-67169@lists.php.net to get a copy of this message | ||
Note Submitter: www.ad-rotator.com
----
To have MySQL to do this task (for thousands rows in DB, this is much faster than PHP)
function _getIp2LongSQL($ip) {
return "
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))";
}
Usage: table IpTable has 2 columns: ipLong, ipStr
$sql = "UPDATE IpTable SET ipLong="._getIp2LongSQL('ipStr')."";
$sql = "SELECT ipStr,"._getIp2LongSQL('ipStr')." AS ipLong FROM
IpTable";