|
|
Posted by Jerry Stuckle on 02/17/06 03:27
frizzle wrote:
> Michael Austin wrote:
>
>>frizzle wrote:
>>
>>>Hi there,
>>>
>>>I'm saving ip addresses of blocked visitors into a mySQL DB.
>>>The function with wich i retrieve the address is below this message.
>>>
>>>What i wonder is, if it's ok to remove the dots from the ip address,
>>>and only
>>>save the numbers into the database. I assume it will be quicker to
>>>compare.
>>>But i don't know if it could mess up ip addresses. E.g. confuse
>>>12.34.56.78 with 123.45.6.78
>>>
>>>Frizzle.
>>>
>>>
>>>
>>>--- function ---
>>>
>>> function getIp()
>>> {
>>> if ( isset( $_SERVER['HTTP_X_FORWARDED_FOR'] ) )
>>> {
>>> return $_SERVER['HTTP_X_FORWARDED_FOR'];
>>> }
>>> else
>>> {
>>> return $_SERVER['REMOTE_ADDR'];
>>> };
>>> };
>>>
>>
>>you cannot remove the dots from the IP unless you store it as
>>
>>CLASSA (number), CLASSB (number),CLASSC (number),CLASSD (number)
>>
>> 123 45 6 78
>>
>>But, the issue is that in order to do a comparison, you have to put them back
>>together - using SQL Query or PHP code - both very expensive.
>>
>>I would leave it as is and store it intact. 123.45.6.78 it is much easier -
>>especially if the column in the db is indexed to do a string comparison.
>>
>> select ipaddress from tablex where ipaddress='123.45.6.78'
>>
>> select classa||'.'||classb||'.'||classc||'.'||classd
>> from tablex
>> where classa||'.'||classb||'.'||classc||'.'||classd='123.45.6.78';
>>
>>While they "look" like they are equivalent - the latter will cause a full table
>>scan to retrieve a single row. This probably would not be a problem until you
>>get several hundred,thousand records for each query.
>>
>>Bottom line: leave it intact and index the column - I would try to use a unique
>>index so that you only store the address one time. (make sure you check to see
>>if it exists before trying to store it to prevent unique validation errors.
>>
>>--
>>Michael Austin.
>>DBA Consultant.
>>Donations welcomed. Http://www.firstdbasource.com/donations.html
>>:)
>
>
> Thanks. So the advise is to save it in 1 field. If i'm right, i'd have
> to be a varchar(15) field, unique, with an index on it?
>
> Frizzle.
>
> (PS, the function to get the IP is right though ? )
>
As Steve said, you can use ip2long to store it in your database as an
INT. Then use long2ip to get it back if necessary.
And searching on an INT will be much faster than searching on a VARCHAR(15).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|