0

my ip field in the mysql database is VARBINARY(16)

  // Insert into website table
    $ip = inet_pton('::1'); // Local ip
    $data_rec = array(
            'ip_address' => $ip,
            );  
    $this->db->insert('visitors', $data_rec);

The ip doesn't get inserted. It's a blank field. Why is that?

CodeCrack
  • 5,253
  • 11
  • 44
  • 72

1 Answers1

3

inet_pton() returns unprintable characters.
Did you try to read it from the database and do echo inet_topn()?

Given that I have a table 

CREATE TABLE `ip` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varbinary(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

And using this simple script (all checks intentionally omitted for simplicity)

<?php
$in6_addr = inet_pton('::1');

$db = new mysqli('localhost', '******', '******', 'test');

$qry = "INSERT INTO ip (ip) VALUES('$in6_addr')";
$db->query($qry));

$qry = "SELECT * FROM ip WHERE id=1";
$result = $db->query($qry);
if ($row = $result->fetch_array(MYSQL_ASSOC)) {
    echo inet_ntop($row['ip']);
}    
?>

you get the output

::1

But you can't see the value in phpAdmin or Sequel Pro

peterm
  • 91,357
  • 15
  • 148
  • 157
  • I am actually using Sequal Pro.. That really sucks if you can't see it.. What's the reason for that? Is there a way for me to see it or store it in another way then? I need to be able to see it in sequel pro. – CodeCrack Jan 09 '13 at 05:18
  • Yes I actually did get ::1 output.. It sucks I can't see it in sequal pro though.. Is there some kind of fix for that – CodeCrack Jan 09 '13 at 05:25
  • Well, the reason might be to store it in a compact form. That's exactly what `inet_pton()` does for you. How you would store it depends on what you would do with it other than looking at it in SequelPro or other IDE. Other options are: 2XBIGINTs, VARCHAR(39) or VARCHAR(45) depends on whether you need to support IPv4 tunneling. You can read [this](http://stackoverflow.com/questions/166132/maximum-length-of-the-textual-representation-of-an-ipv6-address?lq=1) and [this](http://stackoverflow.com/questions/420680/how-to-store-ipv6-compatible-address-in-a-relational-database) to make right decision – peterm Jan 09 '13 at 05:30
  • I think I'll use VARCHAR(50). Thanks – CodeCrack Jan 09 '13 at 06:03