MySQL IP list - INET_ATON Optimization failed! Please Help!
Posted: Sun Feb 17, 2008 2:19 pm
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...
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...
Here is that test script...
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. 
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);
?>Code: Select all
INSERT INTO `jab_ip_addresses` VALUES(1, '2008-02-17', 2130706433);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"];
}
?>