INET_NTOA(ip) problem

[kgrayjr]

Been messing around with storing and retrieving an INT IP address with not much luck getting the IP back in its original form. Here is what I am using to pull the IP from the DB:

 

PHP Code:

function verifyIp($dbh,$user){

    $sql = "SELECT INET_NTOA(ip)
            FROM userlogin 
            WHERE username='$user'
            LIMIT 1";
            
    $result = $dbh->query($sql);
    $row = $result->fetch(PDO::FETCH_ASSOC);
    echo 'User IP: '.$row[ip]; // purely for testing

} 

 

When run, all I get on the screen is (note there is not IP shown):

User IP:

 

If I replace SELECT INET_NTOA(ip) with SELECT ip, the IP shows in INT format. This tells me that the function is grabbing the IP from the database.

 

Just for better understanding, here is how the IP was grabbed:

 

PHP Code:

function updateIP($dbh,$ip,$user){
    $sql = "UPDATE userlogin
            SET ip=INET_ATON(?)
            WHERE username=?";
    $q = $dbh->prepare($sql);
    $q->execute(array($ip,$user));
} 

 

I am guessing it is something silly that I am missing, but just cannot seem to locate the problem.

Any ideas why INET_NTOA(ip) is not working as I would expect would be very much appreciated.

 

PHP / 5.3.1

mysql / 5.1.41

Apache / 2.2.14

 

Thanks,

Ken

[jl5501]

is the numeric representation stored in an int field?

 

Also, is the original dotted quad, a valid IP address?

[kgrayjr]

Here is the sql for the mysql database. Stored as int unsigned, for in the documentation some IP's raise issues when stored as a straight int.

CREATE TABLE IF NOT EXISTS `userlogin` (
  	`loginId` int(11) NOT NULL AUTO_INCREMENT,
  	`username` varchar(30) NOT NULL,
  	`password` varchar(30) NOT NULL,
  	`email` varchar(255) NOT NULL,
  	`role` varchar(30) NOT NULL,
  	`sessionid` varchar(50) NOT NULL,
  	`ip` int(10) unsigned NOT NULL,
  	PRIMARY KEY (`loginId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 

As far as the IP, the IP is on a lan (this box hitting sandbox): 192.168.33.24

 

This script started due to an article I read. So playing with the concept, now it has become purely an obsession to get this thing to work.

[kgrayjr]

OK, got it figured out. But, I am not sure why things are working as they do.

 

When run, the query stores the ip into $row['INET_NTOA(ip)']

 

I was expecting the query to activate INET_NTOA and return it into a column name the same as the DB column name: $row['ip']

 

I am curious why it works as it does.

[kgrayjr]

Well duh. All worked out and I feel rather foolish, the following assigns everything as I expected

 

$sql = "SELECT INET_NTOA(ip)
    	    AS ip
            FROM userlogin 
            WHERE username='$user'
            LIMIT 1";

 

I have always mistakenly thought that the array index names were assigned by mysql. Probably because in most cases I use SELECT * or SELECT column_name in my calls. Never SELECT function_name

 

Posting this just in case someone else is as noobish as I am