Jump to content
larry29936

Convert ip 66.249.75.215 to 0x249f94bd7

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

Share this post


Link to post
Share on other sites

You are trying to convert the literal string ADDRESS. You need ADDRESS to be a variable, hopefully, in the form xxx.xxx.xxx.xxx.

Share this post


Link to post
Share on other sites
$stmt = $pdo->prepare("UPDATE `download` SET `ip_address` = inet_aton(`ADDRESS`) WHERE ip_address is null;");

@gw1500se Those look like backticks to me

Share this post


Link to post
Share on other sites

Yep, now that I see the code properly posted with the formatter.

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


Link to post
Share on other sites

Of course it does - as already stated, inet_aton() is an SQL function not PHP

Share this post


Link to post
Share on other sites
Posted (edited)
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

Share this post


Link to post
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   |
+---------------+------------+

 

Share this post


Link to post
Share on other sites
57 minutes ago, larry29936 said:

I need the ip in a "0x249f94bd7" format.

Why?

Share this post


Link to post
Share on other sites
Posted (edited)

@benanamen -  I'm comparing to another table has a start and end ip in that format in order to get the country, area, and city from. I also have to deal with IPv6 ip's.

Edited by larry29936

Share this post


Link to post
Share on other sites

Leave it in benanamen's format in your database. Change the query result to whatever format you need when you do the compare.

  • Like 1

Share this post


Link to post
Share on other sites
23 minutes ago, larry29936 said:

I also have to deal with IPv6 ip's.

The you should be using INET6_ATON

24 minutes ago, larry29936 said:

I'm comparing to another table has a start and end ip in that format

Ok, Why is it in that format?

Share this post


Link to post
Share on other sites

That's the way it imported into the table. I don't remember where I downloaded the .csv file from.

Share this post


Link to post
Share on other sites
1 minute ago, larry29936 said:

That's the way it imported into the table.

Is that the way it is in the CSV file?

Share this post


Link to post
Share on other sites
Posted (edited)

I just checked the csv and it's in xxx.xxx.xxx.xxx format. It's almost 5 million rows of data and the ip's were imported into varbinary(32) fields.

Edited by larry29936

Share this post


Link to post
Share on other sites
9 minutes ago, larry29936 said:

the ip's were imported into varbinary(32) fields.

And there is your REAL problem.....

Share this post


Link to post
Share on other sites
16 minutes ago, benanamen said:

And there is your REAL problem.....

So, I should drop the table and recreate it with the ip fields as int(32) unsigned then re-import the .csv? 

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

@Barand, so should I re-import the csv ip's into varchar(32) fields or int(32) unsigned fields?

Share this post


Link to post
Share on other sites

VARBINARY (16) would give you a common type for IPv4 and IPv6

Share this post


Link to post
Share on other sites

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.