Posted by frizzle on 02/16/06 19:38

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 ? )

[Back to original message]