Save ip address into DB

[frizzle]

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'];
};
};

[David Haynes]

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'];
};
};

If you want it without periods, you need to make the fields line up:

$ip_address = '10.0.1.126';
$foo = explode('.', $ip_address);
$padded = sprintf("%03d%03d%03d%03d", $foo[0], $foo[1], $foo[2], $foo[3]);
printf("ip = %s, padded = %s\n", $ip_address, $padded);

However, if you are going to explode the ip address, why not just store
the octet elements individually?

Also, what about IPv6?

-david-

[Steve]

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.

Use <http://www.php.net/manual/en/function.ip2long.php>.

---
Steve

[Michael Austin]

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

[frizzle]

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

[Jerry Stuckle]

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.
js*******@attglobal.net
==================

[Jasen Betts]

On 2006-02-15, frizzle <ph********@gmail.com> 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

if mysql has an IP address datatype like postgresql does use that.
--

Bye.
Jasen

[frizzle]

Jasen Betts wrote:

On 2006-02-15, frizzle <ph********@gmail.com> 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

if mysql has an IP address datatype like postgresql does use that.
--

Bye.
Jasen

AFAIK it hasn't ...

[Jim Michaels]

"frizzle" <ph********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

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.

try the database's built-in functions.
SELECT INET_ATON('209.207.224.40');
-> 3520061480

SELECT INET_NTOA(3520061480);
-> '209.207.224.40'