[SOLVED] need help on converting ip address (varchar15) to int

[superkingkong]

hi guys,

 

i have a table with ip address as varchar15 datatype. I need to convert it to int.

how can i do that?

 

appreciate your help, thanks.

[gffg4574fghsDSGDGKJYM]

You have ip address in format '192.0.34.166' to be converted in a format like this '3221234342' ?

 

If so you can use ip2long http://www.php.net/manual/en/function.ip2long.php

 

Make a little php script that will loop through all your record and update them.

 

When done only change the datatype to Unsigned INT from string. MySQL will automaticly convert the number string version to a integer and you won't lose any data.

 

Be sure to save your table/db just in case

 

MySQL also have built-in functions to do that :

inet_ntoa http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-ntoa

inet-aton http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton

 

[superkingkong]

You have ip address in format '192.0.34.166' to be converted in a format like this '3221234342' ?

 

If so you can use ip2long http://www.php.net/manual/en/function.ip2long.php

 

Make a little php script that will loop through all your record and update them.

 

When done only change the datatype to Unsigned INT from string. MySQL will automaticly convert the number string version to a integer and you won't lose any data.

 

Be sure to save your table/db just in case

 

MySQL also have built-in functions to do that :

inet_ntoa http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-ntoa

inet-aton http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton

 

thanks for the reply.

 

i've done a script, it ran successfully, but when i look at the table, the ipaddress field is showing the same value "1176924178" for all the 2000+ records. is it supposed to be like that? or is my logic wrong? i'm just a newbie in PHP

 

would appreciate if someone could please help. below is the code:

btw, i've tried the INET_ATON or the ip2long, both also showing the same result. All ips int are the same. As you suggested, i'm doing it on a varchar(15) datatype field. It is still the same result after i converted it to int(12) unsigned

 

$result = mysql_query("SELECT * FROM weblog");
while($row = mysql_fetch_array($result))
{
$ipaddress = ip2long($row['ipaddress']);
//	$upd = mysql_query("UPDATE weblog SET ipaddress = INET_ATON('$ipaddress')");
$upd = mysql_query("UPDATE weblog SET ipaddress = $ipaddress");
if (!$upd)
{
	echo "<br />Failed to update";
} else {
	echo "<br />Success!";
}
}
?>

[gffg4574fghsDSGDGKJYM]

I hope you saved your database because you lost the ipaddress column.

 

When you use :

 

UPDATE weblog SET ipaddress = $ipaddress;

 

You update all row in the database.

You must tell mysql to update 1 row not the whole table with a WHERE.

 

Now you need to recover from a backup, because all the ipaddress columns have the same value, then run a script like that :

 

<?php
$sql = "SELECT id,ipaddress FROM weblog;";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{
  $ipaddress = $row['ipaddress'];
  $id = $row['ipd'];
  $sql = "UPDATE weblog SET ipaddress = INET_ATON('".$ipaddress."') WHERE id=".$id.";";
  $upd = mysql_query($sql);
if (!$upd)
{
	echo "<br />Failed to update";
} else {
	echo "<br />Success!";
}
}
?>

 

Use id column or any other PRIMARY KEY to uniquely identify the row. Look carefully at the script i didn't test it and change id to whatever the primary key is.

 

You still need to doing this script on the varchar until all value are converted to unsigned int, then convert it to unsigned int. You can leave it as varchar if you want it will work, but the performance will be better if you convert it to unsigned int after all data are processed.

[superkingkong]

thank you very much

 

I did backup my table earlier i knew that i'll do something wrong

 

anyway, i have another question, now that everything is in the right order, i'm trying to display the table.

 

Unfortunately, the ipaddress field is not showing anything (if i use the INET_NTOA()), but it shows integer if i use without the inet_ntoa function.

 

Appreciate if you could help further, thanks.

 

<?php

$result = mysql_query("SELECT id, INET_NTOA('ipaddress'), referrer, datetime, page, hostname, useragent FROM weblog ORDER BY id DESC");
echo "<table border=2>";

while($row = mysql_fetch_array($result))
{

echo "<tr>";
echo '<td><a href="' . $row['referrer'] . '">Referrer</a> </td>';
echo "<td>" . $row['datetime'] . " (" . $row['timezone'] . ") </td>";
echo "<td>" . $row['page'] . " </td>";
echo "</tr>";
echo "<tr>";
echo "<td>" . $row['ipaddress'] . " </td>";
echo "<td>" . $row['hostname'] . " </td>";
echo "<td>" . $row['useragent'] . " </td>";
echo "</tr>";
}
echo "</table>";
?>

[gffg4574fghsDSGDGKJYM]

Unfortunately, the ipaddress field is not showing anything (if i use the INET_NTOA()), but it shows integer if i use without the inet_ntoa function.

 

If you converted all the row to integer you don't need inet_ntoa() anymore. Except maybe when you want to insert new row.

[superkingkong]

Unfortunately, the ipaddress field is not showing anything (if i use the INET_NTOA()), but it shows integer if i use without the inet_ntoa function.

 

If you converted all the row to integer you don't need inet_ntoa() anymore. Except maybe when you want to insert new row.

 

right now, it's integer in mysql, but i need to show the result on the web page. so, by just pulling it straight it out, it only display 10 int. how can i use the NTOA or ATON to display it as dotted ip address (on mysql side).

 

thanks.

[fenway]

Use INET_NTOA().... what's the problem?

[superkingkong]

Use INET_NTOA().... what's the problem?

 

thanks, i managed to figure it out. I must use alias

 

what i did earlier was, using inet_ntoa on select statement, then on displaying, i use $row['ipaddress'], which is not working.

 

after using expression with alias, AS IP, no problem now

 

thanks