Page 1 of 1

MySQL IP list - INET_ATON Optimization failed! Please Help!

Posted: Sun Feb 17, 2008 2:19 pm
by JAB Creations
After taking a suggestion to improve the IP address script below to use int(10) instead of vchar everything stopped working correctly and everyone helping seems to have come to a loss. So with the first script the table is created but nothing is added when I visit the script. Here it is...

Code: Select all

// Steps 1/2 connection related.
// 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 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());
 
$q = "SELECT * FROM jab_ip_addresses WHERE 'ip' = $ipaddress";
$result=mysql_query($q);
if mysql_num_rows($result)
{}
 
echo '<br />Step 5<br />'.$result.'<br /><br />';
 
 
 
 
 
// 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();
}
 
// Allow client to echo when their IP was originally added to the database.
else if (isset($_GET['date'])) {
 
$ipaddress = getenv("REMOTE_ADDR");
$q = "SELECT date FROM jab_ip_addresses WHERE ip='$ipaddress'";
$result=@mysql_query ($q) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo $row["date"];
echo $row["ip"];
}
 
 
 
echo '<br /><br /><br />';
}
 
/*
 
$requestdate = mysql_query("
 SELECT `id`
FROM `jab_ip_addresses`
WHERE `ip` = CONVERT( _utf8 '68.56.168.246'
USING latin1 )
COLLATE latin1_swedish_ci
LIMIT 0 , 30
");
 
echo '<br /><br />Date IP Added: '.$row['date'].'<br />';
 
}
*/
 
// 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
{
// INSERT INTO db (ip) VALUES('inet_aton('".$IPAddress."'))
 
 
//$sql = "INSERT INTO db (ip) VALUES('inet_aton('".$IPAddress."'))"; 
$query = "INSERT INTO jab_ip_addresses (ip, date) VALUES(inet_aton('".$IPAddress."'), CURDATE())"; 
//$query = "INSERT INTO jab_ip_addresses (ip, date) VALUES ('$ipaddress', CURDATE())";
//$query = "INSERT INTO db (ip) VALUES('inet_aton('".$IPAddress."'))";
// $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);
?>
The second script is a variation for testing purposes. When I execute the PHP file merely by visiting it in the browser and then do an export in PhpMyAdmin I get the following...

Code: Select all

INSERT INTO `jab_ip_addresses` VALUES(1, '2008-02-17', 2130706433);
Here is that test script...

Code: Select all

// Steps 1/2 connection related.
 
// 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
mysql_query("INSERT INTO jab_ip_addresses (ip, date) VALUES(INET_ATON('127.0.0.1'), CURDATE());") 
or die(mysql_error());
 
// Step #5
$q = "SELECT INET_NTOA(ip) FROM jab_ip_addresses";
$result=@mysql_query ($q) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo $row;
echo $row["date"];
echo $row["ip"];
}
?>
I'm really surprised at the lack of any example code when doing a Google for MySQL ip address list. I did fine getting the basics but optimizing stuff is different. :banghead:

Re: MySQL IP list - Optimization failed! Please Help!

Posted: Sun Feb 17, 2008 4:33 pm
by JAB Creations
I've figured out that I've been adding the IP address using inet_aton correctly. The problem is reading it. Here is a read-only file though I get no error message! It will echo the MySQL command that attempts to read the IP address (which is your own if you run this). The user/pass/database names have been changed.

Code: Select all

<?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 INET_ATON(ip)='$ipaddress'";
echo $q;
$result=@mysql_query ($q) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo $row["ip"];
}
?>