470,815 Members | 1,318 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,815 developers. It's quick & easy.

Save ip address into DB

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

Feb 15 '06 #1
8 8268
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-

Feb 15 '06 #2
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

Feb 15 '06 #3
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
:)
Feb 16 '06 #4
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 ? )

Feb 16 '06 #5
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
==================
Feb 17 '06 #6
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
Feb 17 '06 #7

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 ...

Feb 19 '06 #8

"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'
Mar 19 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

117 posts views Thread by Steevo | last post: by
2 posts views Thread by Saxman | last post: by
4 posts views Thread by Jonny | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.