Jump to content

Convert ip 66.249.75.215 to 0x249f94bd7


larry29936

Recommended Posts

How do I update a table and convert say, 66.249.75.215, from one column to another column as 0x249f94bd7. Both columns are varbinary. I'm using PDOConnect and this isn't working:

    $stmt = $pdo->prepare("UPDATE `download` SET `ip_address` = inet_aton(`ADDRESS`) WHERE ip_address is null;");
    $stmt->execute() ;

I'm getting the following error:

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1411 Incorrect string value: '`test`.`download`.`ADDRESS`' for function inet_aton in /home/larry/web/test/public_html/index.php:31 Stack trace: #0 /home/larry/web/test/public_html/index.php(31): PDOStatement->execute() #1 {main} thrown in /home/larry/web/test/public_html/index.php on line 31

Link to comment
Share on other sites

The code now reads:

  $test = inet_aton($ip);
    echo var_dump($test);
    $stmt = $pdo->prepare("UPDATE download SET ip_address = (?) WHERE ip_address is null;");
    $stmt->execute($test) ;

It errors at "$test = inet_aton($ip);  I never see the var_dump()

Edited by larry29936
Link to comment
Share on other sites

mysql> CREATE TABLE `download` (
    ->   `download_id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `ip_address` int(10) unsigned DEFAULT NULL,
    ->   `address` varchar(50) DEFAULT NULL,
    ->   PRIMARY KEY (`download_id`)
    -> );

mysql> INSERT INTO download (address) VALUES ('66.249.75.215');

mysql> UPDATE `download` SET `ip_address` = inet_aton(`ADDRESS`) WHERE ip_address is null;

mysql> SELECT address
    ->      , HEX(ip_address) as ip_address
    -> FROM download;
+---------------+------------+
| address       | ip_address |
+---------------+------------+
| 66.249.75.215 | 42F94BD7   |
+---------------+------------+

 

Edited by Barand
Link to comment
Share on other sites

mysql> CREATE TABLE `download` (
    ->   `download_id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `ip_address` varchar(50) DEFAULT NULL,
    ->   `address` varchar(50) DEFAULT NULL,
    ->   PRIMARY KEY (`download_id`)
    -> );

mysql> INSERT INTO download (address) VALUES ('66.249.75.215');

mysql> UPDATE `download` SET `ip_address` = hex(inet_aton(`ADDRESS`)) WHERE ip_address is null;

mysql> SELECT address
    ->      , ip_address
    -> FROM download;
+---------------+------------+
| address       | ip_address |
+---------------+------------+
| 66.249.75.215 | 42F94BD7   |
+---------------+------------+

 

Link to comment
Share on other sites

1 hour ago, larry29936 said:

Everything I've seen has said to use varbinary fields for IPv6 ip's.

According to my manual...

Quote

Topic: INET6_ATON
Syntax: INET 6_ ATON (expr)

Given an IP v6 or IP v4 network address as a string, returns a binary string that represents the numeric value of the address in network byte order (big endian). Because numeric-format IP v6 addresses require more bytes than the largest integer type, the representation returned by this function has the VARBINARY data type: VARBINARY (16) for IP v6 addresses and VARBINARY (4) for IP v4 addresses. If the argument is not a valid address, INET 6_ ATON () returns NULL .

 

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.