Convert binary(16) data in MySQL field

Question

I "inherited" from a collegue a MySQL database with dozen of fields. Since I've worked very few times with databases, I'm looking for little advice here.

There's a binary(16) field named "IP": I suppose it is used to store user IPs. A typical stored value is, for example, 00000000000000000000ffff3d024463.

Using PHP (or even MySQL, if this is possibile), how can I convert this data to a plain IP address?

score 0 · Answer 1 · edited May 23 '17 at 12:17

0

have you tried

$data = hex2bin($ip);
var_dump($data);
//not sure how your data was put into the column IP

on your sql query you can also try

$query = mysql_query("SELECT `HEX(IP)` FROM `database`");
$row = mysql_fetch_array($query);
foreach($row['ip'] as $ip)
{
   $data = hex2bin($ip);
   echo $data."<br />";
}

//code not tested

SQL Query with binary data (PHP and MySQL)

http://php.net/manual/en/function.hex2bin.php

http://php.net/manual/en/function.bin2hex.php

edited May 23 '17 at 12:17

Community

1
1

answered Mar 05 '17 at 16:08

DarkSideKillaz

138
6

The hex2bin returns bool(false) – Mar 05 '17 at 16:28
Does this page help at all? I honestly would change the column from binary to varchar and store ip address as string. http://stackoverflow.com/questions/6382738/convert-string-to-binary-then-back-again-using-php – DarkSideKillaz Mar 05 '17 at 19:04
1

Try `bin2hex ();` – DarkSideKillaz Mar 05 '17 at 19:05
I must not alter the database, I only can retrieve values – Mar 06 '17 at 08:26

score 0 · Accepted Answer · answered Mar 08 '17 at 16:45

0

Solved: I had to convert that number using a MySQL function (INET6_NTOA).

answered Mar 08 '17 at 16:45

Convert binary(16) data in MySQL field

2 Answers2