Forum Moderators: coopster
I'm also interested in suggestions for improving the efficiency of the code in two ways, my if/else structure and in regards to how long I retain IP addresses. I know traffic levels make that specific topic subjective and I'm not wild about revealing specifics of my statistical analysis so a question I feel might help is what number of rows do I need to start becoming concerned about when MySQL reaches any upper limit in regards to it's auto-increment feature? Retaining an IP for a year versus a week might make sense if it's lower then having the same IP constantly create an incrementally high number?
I've taken care to add useful comments through out the code as much as possible. Thanks in advance for any and all useful replies!
- John
// Step #1&2: Connection to the database.// Step #3: Create Table if none exists
mysql_query("
CREATE TABLE jab_ip_addresses (
id int(10) unsigned NOT NULL auto_increment,
date date NOT NULL,
ip varchar(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
");// Step #4: Assign IP address in PHP to variable
$ipaddress = getenv("REMOTE_ADDR");
$date = Date("Y-m-d");// Step #5 NEW--Determine if IP already exists and if so remove entry?
// Retrieve all the data from the "jab_ip_addresses" table
$result = mysql_query("SELECT * FROM jab_ip_addresses")
or die(mysql_error());// store the record of the "jab_ip_addresses" table into $row
$row = mysql_fetch_array( $result );
echo '<br /><br />IP: '.$row['ip'].'<br />';
echo 'MySQL Date: '.$row['date'].'<br />';
echo 'PHP Date: ' . $date.'<br /><br />';// If removal request does not exist proceed
// Step #6
if (isset($_GET['purge'])) {echo '<br /><br />You have requested to have your IP purged from the database.<br /><br />';
// Delete the client's IP, confirm when the IP is added but it's database ID auto-increments.
mysql_query("DELETE FROM jab_ip_addresses WHERE ip='$ipaddress'")
or die(mysql_error());echo '<br /><br />Your IP address has been purged from the database.<br />
Would you like to <a href="test.php">add your IP address</a> back to the database?
<br />';
die();
}
// No? Then run the script as usual...
else {echo '<br /><br />You have not requested to have your IP purged from the database.<br />
Would you like to <a href="test.php?purge">purge your IP address</a> from the database?
<br /><br />';}// Step #7: Attempt to insert IP Address in to table
// Step #7.1: Select the IP column and determine if the IP address already exists?
$sql = "select * from jab_ip_addresses where ip='" . $ipaddress . "'";
echo $sql;
$result = mysql_query($sql);// Step #7.2: If the result = 1 IP is added, else if result is less then 1 don't add!
if (mysql_num_rows($result) >= 1) {
$error = "The IP address is already listed in the DB.";
echo '<br /><br />'.$error;
echo '<br />result = ' . $result;
}
else
{
$query = "INSERT INTO jab_ip_addresses (ip, date) VALUES ('$ipaddress', CURDATE())";
// $result = @mysql_query ($query);
$result = @mysql_query ($query) or die(mysql_error());
echo '<br /><br />The IP ' . $ipaddress . ' was added to the DB-array because it was not listed.';
echo '<br />result = ' . $result;
}// Step #98: close database when finished!
mysql_close($dbh);
?>
I think the IP is being added correctly in the main script though this took a lot of digging on Google to find out that yes, at least I was adding the IP to the database correctly. So my main concern became this, am I correctly attempting to read the IP address from the database table?
Well I'm pretty sure the answer is no! I get no error messages which is of course about as helpful as lead paint when stranded on a desert island. What is wrong with my code below? I've added everything except user/pass/db name.
- John
<?php
// Step #1: Connect to Server
$username = "user";
$password = "pass";
$hostname = "localhost";
$dbh = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";// Step #2: Choose the database
$selected = mysql_select_db("database_name",$dbh) or die("Could not select first_test");$ipaddress = getenv("REMOTE_ADDR");
$q = "SELECT ip FROM jab_ip_addresses WHERE ip='$ipaddress'";
echo $q;
$result=@mysql_query ($q) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo $row["ip"];
}
?>
- John
$q = "SELECT ip FROM jab_ip_addresses WHERE INET_ATON(ip)='$ipaddress'";
I was able to connect to the MySQL database using the following pattern...
mysql --user=jabcreat_dbname --password=your_password jabcreat_dbname
Then using...
SELECT INET_ATON('209.207.224.40');
It looked like it worked just fine!
However back in PHP I'm not getting any results in my read-only script. I've commented out a couple other attempts though I've must have made at least two dozen variables of using select. I'm doing more research on how to use it correctly. I've got a couple books and I've been reading online. Maybe something will just work eventually?
- John
<?php
// Step #1: Connect to Server
$username = "jabcreat_dbname";
$password = "password";
$hostname = "localhost";
$dbh = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
echo "Step #1: Connected to MySQL<br />";// Step #2: Choose the database
$selected = mysql_select_db("jabcreat_dbname",$dbh) or die("Could not select first_test");$ipaddress = getenv("REMOTE_ADDR");
//$sql = "SELECT id, ip FROM jab_ip_addresses WHERE INET_ATON(ip)='$ipaddress'";
$sql = "select id from jab_ip_addresses";
//$sql = "SELECT INET_ATON('68.56.168.246');";echo 'Step #2: '.$sql.'<br />';
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo $row;
echo $row["id"];
echo $row["ip"];
}
?>
- John
EG, have you actually put anything in it yet?
Your code above will read data from the database, but if there's nothing there, the output will be blank.
INSERT INTO `jab_ip_addresses` VALUES(1, '2008-02-18', 1144563958);
I figured out how to get in to MySQL via shell so if there is anything we want to do that way I can now. Thanks for your reply!
- John
SELECT * FROM jab_ip_addresses;
Then we can see what's in there.
Step #1: Connected to MySQL
Step #2: SELECT * FROM jab_ip_addresses;
ID = 1 and IP = 1144563958
What is the next logical step from here? I am going to guess we want to select the ID of the row that contains the IP address....oh wait, you know I think I was trying to match the regular IP address but not the err, encoded version?
Any way I get the above using...
<?php
// Step #1: Connect to Server
$username = "jabcreat_dbname";
$password = "password";
$hostname = "localhost";
$dbh = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
echo "Step #1: Connected to MySQL<br />";// Step #2: Choose the database
$selected = mysql_select_db("jabcreat_dbname",$dbh) or die("Could not select first_test");// Step #3: Attempt to Read Database
$ipaddress = getenv("REMOTE_ADDR");//$sql = "SELECT id, ip FROM jab_ip_addresses WHERE INET_ATON(ip)='$ipaddress'";
//$sql = "select id from jab_ip_addresses";
//$sql = "SELECT INET_ATON('68.56.168.246');";
$sql = "SELECT * FROM jab_ip_addresses;";echo 'Step #2: '.$sql.'<br />';
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
//echo $row;
echo 'ID = '.$row["id"].' and ';
echo 'IP = '.$row["ip"];
}
?>
$sql = "SELECT id FROM jab_ip_addresses WHERE INET_NTOA(ip)='$ipaddress'";
Put that into your above code and it should work as required (assuming what you're after is the ID of the row containing the matched IP address).
$sql = "SELECT id, INET_NTOA(ip) FROM jab_ip_addresses WHERE INET_NTOA(ip)='$ipaddress'";
$sql = "SELECT `id`,`ip` FROM jab_ip_addresses WHERE INET_NTOA(ip)='$ipaddress'";
On the surface, I have some doubts that the above line is going to be optimized... Won't MySQL create a temporary table for every query since it has to run INET_NTOA on every row of the table in order to perform the comparison? We're talking about some kind of logging/tracking script so the table's probably going to grow rather large rather quickly.
I always do the conversion [php.net] in PHP so that I can index my `ip` INT field and pass the search values directly to MySQL.
So we need to select the ID in order to uniquely identify what row(?) the IP address is listed, correct? However I think we may need to use INET_NTOA again? I tried using the following...
$sql = "SELECT id, INET_NTOA(ip) FROM jab_ip_addresses WHERE INET_NTOA(ip)='$ipaddress'";echo 'Step #2: '.$sql.'<br />';
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
//echo $row;
echo 'ID = '.$row["id"].' and ';
echo 'IP = '.$row["ip"];
//echo INET_NTOA(ip);
echo INET_NTOA($row["ip"]);
}
?>
However I wasn't able to get the IP to echo? I don't think off hand I would need to use INET_NTOA outside of MySQL...isn't it already converted back once we've called it in $sql?
- John
I always do the conversion in PHP so that I can index my `ip` INT field and pass the search values directly to MySQL.
Yes, that's the end game. It'll be far more efficient to just be searching on the int.
However I wasn't able to get the IP to echo?
No, leave the rest of the code as is. The only change was to the SQL statement.
So I presume we've hit end game in regards to MySQL and we just have to handle decoding the IP via INET_NTOA outside of MySQL?
I looked on php.net and could not find INET_NTOA though inet_ntop seems to at least seem like a reason direction to pursue. Am I getting any warmer? Ha! The following doesn't work (regardless of my combinations)...
- John
while ($row = mysql_fetch_assoc($result))
{
//echo $row;
echo 'ID = '.$row["id"].' and ';
echo 'IP = '.$row["ip"];
//echo INET_NTOA(ip);
//echo INET_NTOA($row["ip"]);
$rowip = $row["ip"];
$myipis = inet_ntop($rowip);
echo $myipis;
}
?>
Older stuff...
$sql = "SELECT id, ip FROM jab_ip_addresses WHERE INET_NTOA(ip)='$ipaddress'";echo 'Step #2: '.$sql.'<br />';
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
//echo $row;
echo 'ID = '.$row["id"].' and ';
echo 'IP = '.$row["ip"];
echo INET_NTOA(ip);
echo INET_NTOA($row["ip"]);
}
?>
- John
$rowip = $row["ip"];
$myipis = ip2long($rowip);
echo $myipis;
}
?>
can getenv("REMOTE_ADDR") be trusted?
[url=http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton]INET_ATON[/url]( '127.0.0.1' ) == [url=http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-ntoa]INET_NTOA[/url]( 2130706433 )
[url=http://php.net/ip2long]ip2long[/url]( '127.0.0.1' ) == [url=http://php.net/long2ip]long2ip[/url]( 2130706433 )
The above pseudocode is only meant to illustrate the relationship between the PHP and MySQL functions. Actual working code for PHP has to take into account this note from the PHP manual:
Note: Because PHP's integer type is signed, and many IP addresses will result in negative integers, you need to use the "%u" formatter of sprintf() or printf() to get the string representation of the unsigned IP address.
So, whenever you convert ip addresses in PHP using ip2long for insertion into an unsigned MySQL INT field, this is the code:
sprintf("%u", ip2long( '127.0.0.1' )) == long2ip( 2130706433 )
Now, you can't ask PHP to perform a MySQL function nor can you ask MySQL to perform a PHP function, but you can "mix" the functions since they all perform an identical conversion. So the following INSERT statements produce the identical data:
$ip = '127.0.0.1'; // examples will continue to assume this value for $ip
$sql = "INSERT INTO `ip_addresses` (`ip`) VALUES ( INET_ATON('".$ip."') )";
$sql = "INSERT INTO `ip_addresses` (`ip`) VALUES ( ". sprintf("%u", ip2long( $ip )) ." )";
And, given the above records, you can also SELECT them using any of the below statements:
$sql = "SELECT `ip` FROM `ip_addresses` WHERE `ip`=INET_ATON('".$ip."')";
$sql = "SELECT `ip` FROM `ip_addresses` WHERE INET_NTOA(`ip`)='".$ip."'";
$sql = "SELECT `ip` FROM `ip_addresses` WHERE `ip`=". sprintf("%u", ip2long( $ip )) ."";
Then, finally, when we pull the data out of MySQL, we have two different ways of getting the string IP address:
// this has MySQL convert the IP address:
$sql = "SELECT `ip`,INET_NTOA(`ip`) AS ip_ntoa FROM `ip_addresses` WHERE `ip`=". sprintf("%u", ip2long( $ip )) ."";
// For PHP, first pull it from the table:
$sql = "SELECT `ip` FROM `ip_addresses` WHERE `ip`=". sprintf("%u", ip2long( $ip )) ."";
// ...query, loop code here...
// Then, when we echo the `ip` value later:
echo long2ip($row['ip']);
INET_ATON( '127.0.0.1' ) == ip2long( '127.0.0.1' )
INET_NTOA( 2130706433 ) == long2ip( 2130706433 )
I did not want to quickly reply to the previous post(s) to you Gregg seeing as you took the time to compose it. I'm not making the necessary connections though. Here is what I have tried...
- John
$rowip = $row["ip"];
echo '<br />$ rowip = ' . $rowip.'<br /><br />';
$ip2long = sprintf("%u", ip2long( '$rowip' ));
$long2ip = sprintf("%u", long2ip( '$rowip' ));
echo '$ ip2long = '.$ip2long.'<br />';
echo '$ long2ip = '.$long2ip;
}// A domain name works but not the IP address?!
//$ip = $row["ip"];
$ip = gethostbyname("www.example.com");
printf("%u\n", ip2long($ip));
echo '<br />'.$ip;
echo '<br />'.$out;
Here's a little test page I had built for myself (and host on my local machine) to better understand how it works. I also needed a tool to "decode" IP addresses from my database or encode them for manual inserts.
<table>
<?php
$ip = (isset($_POST['ip']))? $_POST['ip'] : $_SERVER['REMOTE_ADDR'];
$ip = (preg_match('/[^-.0-9]/', $ip))? gethostbyname($ip) : $ip;
if(preg_match('/[.]/', $ip) == false){
echo '<tr><td>long2ip</td><td>'.long2ip($ip).'</td></tr>';
} else {
echo '<tr><td>sprintf("%u", ip2long</td><td>'.sprintf("%u", ip2long($ip)).'</td></tr>';
echo '<tr><td>ip2long</td><td>'.ip2long($ip).'</td></tr>';
}
?>
</table>
<form action="" method="post">
<input type="text" name="ip" value="<?php echo $ip; ?>" />
<input type="submit" value="Go" />
</form>
Try the following values:
192.168.0.1
3232235521
Then, try any IP address and copy and paste the output back into the box to convert it back.
Any way it at least seems to work with the code below. :)
- John
<?php
// Step #1: Connect to Server
$username = "user";
$password = "pass";
$hostname = "localhost";
$dbh = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";// Step #2: Choose the database
$selected = mysql_select_db("database_name",$dbh) or die("Could not select first_test");// Step #3: Attempt to Read Database
$ipaddress = getenv("REMOTE_ADDR");
$sql = "SELECT id, ip FROM jab_ip_addresses WHERE INET_NTOA(ip)='$ipaddress'";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
$rowip = $row["ip"];
$rowipis = long2ip($rowip);
echo '<br />The database IP is = '.$rowipis.'<br />';
}
?>
- John
Working Script
<?php
// Step #1: Connect to Server
$username = "database_name";
$password = "password";
$hostname = "localhost";
$dbh = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
echo '<p>Step 1: Connected to MySQL</p>' . "\n";// Step #2: Choose the database
$selected = mysql_select_db("database_name",$dbh) or die("Could not select first_test");// Step #3: Create Table if none exists
mysql_query("
CREATE TABLE jab_ip_addresses (
id int(10) unsigned NOT NULL auto_increment,
date date NOT NULL,
ip int(11) UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
");// Step #4: Assign IP address in PHP to variable
$ipaddress = getenv("REMOTE_ADDR");
$date = Date("Y-m-d");// Step #5: Determine if IP removal request exists
if (!isset($_GET['purge'])) {//Step #6: Find IP in DB
$sql = "SELECT id, INET_NTOA(ip) FROM jab_ip_addresses WHERE INET_NTOA(ip)='$ipaddress'";
echo '<p>'.$sql."</p>\n";
$result = mysql_query ($sql) or die(mysql_error());
while ($row = mysql_fetch_row($result))
{
// echo '<br />'.$row[1]."\n"; //echos IP in row
$ipfound = $row[1];
}
//echo $ipfound."\n";//Step #6.1: IP Not Found, add to DB
if ($ipfound != $ipaddress) {echo '<p>Your IP was not found and should be added to the DB!</p>'."\n";
$query = "INSERT INTO jab_ip_addresses (ip, date) VALUES(inet_aton('".$ipaddress."'), CURDATE())";
$result = @mysql_query ($query) or die(mysql_error());
echo '<p>The IP ' . $ipaddress . ' was added to the DB-array because it was not listed.</p>';
}//Step #6.2: IP Found, Echo Confirmation?
else {echo "<p>Step 4: Your IP $ipfound was found so you don't need to be added to the DB, <a href=\"test.php?purge\">purge your IP address</a>?</p>"."\n";}} //End Step #6 initial if
else if (isset($_GET['purge'])) {
mysql_query("DELETE FROM jab_ip_addresses WHERE INET_NTOA(ip)='$ipaddress'")
or die(mysql_error());echo '<p>Your IP address has been purged, <a href="test.php">add your IP address</a> back to the database?</p>'."\n";
}// Step #98: close database when finished!
mysql_close($dbh);
?>
Admittedly I use Perl rather than PHP, but I'm sure there's an equivalent.
That way you'd have
$row -> {ip} or $row[3] -> {ip}, so when you change your DB you don't end up having to change hundreds of array indexes!
- John
