Page 1 of 1

MySQL optimal table for IP list with purge dates?

Posted: Wed Feb 13, 2008 7:09 pm
by JAB Creations
My first custom MySQL table will be part of an existing MySQL database. I want to create a table that can contain two things: IP Address with an associated date. I'm not sure what the best types are for such information to begin with.

I plan on having IP addresses with X amount of age eventually be purged from the list to be able to keep it from becoming a monstrosity to back up and/or upload if I change hosts.

I also don't know how the heck I'll take an IP address with PHP and send it to the database via targeting the table and I think the field I want? I have no clue how to have PHP check to first see if the IP exists which I'm sure would be a wise thing to do.

This is my first serious venture in to MySQL besides dealing with stuff by third parties. Wish me luck! :mrgreen:

Re: MySQL optimal table for IP list with purge dates?

Posted: Wed Feb 13, 2008 7:54 pm
by JAB Creations
I'm going to post as I'm going along trying stuff out. I know I can't create databases from scratch without having to first manually go through my host's control panel which is fine. I'm using an existing database and here is what I have...

Code: Select all

// Create a MySQL table in the selected database
mysql_query("CREATE TABLE jab_ip_addresses (
ip INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
 name VARCHAR(30), 
 age INT)")
or die(mysql_error());  
echo "Table Created!";
I'm cleaning this up slowly but it works. I know how to use PhpMyAdmin pretty good, I just need good folks who know what I'm better off using to go with by default.

Any way I'm trying to figure out what types of data an IP address is best saved as in the database. I'm also reading about dates. I'd presume MySQL has an easy method of merely placing the date of which a row is created?

Re: MySQL optimal table for IP list with purge dates?

Posted: Wed Feb 13, 2008 8:10 pm
by JAB Creations
Here is a slightly more refined version, suggestions for improvements are very much welcomed!

Code: Select all

mysql_query("CREATE TABLE jab_ip_addresses (
ip INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(ip),
date DATETIME)")
or die(mysql_error());  
echo "Table Created!";
Unless I get a reply about how to improve creating the table I'm going to try and have my IP address added to the database next.

Re: MySQL optimal table for IP list with purge dates?

Posted: Wed Feb 13, 2008 8:18 pm
by JAB Creations
Having a little trouble getting my IP address added to the table...

Code: Select all

$ipaddress = getenv("REMOTE_ADDR");
 
// Insert a row of information into the table "jab_ip_addresses"
mysql_query("INSERT INTO jab_ip_addresses 
(ip) VALUES($ipaddress) ") 
or die(mysql_error());  
echo "Data Inserted!";

Re: MySQL optimal table for IP list with purge dates?

Posted: Wed Feb 13, 2008 11:27 pm
by JAB Creations
Here is everything except for the connection of the PHP...

Code: Select all

<?php
//connection stuff not listed though present here
 
mysql_query("
CREATE TABLE jab_ip_addresses (
  id int(10) unsigned NOT NULL auto_increment,
  user_date varchar(20) NOT NULL,
  ip varchar(20) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;  
");
 
$ipaddress = getenv("REMOTE_ADDR");
 
mysql_query("INSERT INTO jab_ip_addresses 
(ip) VALUES({$ipaddress}) ")
or die(mysql_error());  
echo "Data Inserted!";
 
mysql_close($dbh);
?>
I need to insert the IP address without it duplicating...how do I tell PHP/MySQL to create a new record/row if the IP address is not yet part of the database?

Re: MySQL optimal table for IP list with purge dates?

Posted: Wed Feb 13, 2008 11:27 pm
by JAB Creations
Here is everything except for the connection of the PHP...

Code: Select all

<?php
//connection stuff not listed though present here
 
mysql_query("
CREATE TABLE jab_ip_addresses (
  id int(10) unsigned NOT NULL auto_increment,
  user_date varchar(20) NOT NULL,
  ip varchar(20) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;  
");
 
$ipaddress = getenv("REMOTE_ADDR");
 
mysql_query("INSERT INTO jab_ip_addresses 
(ip) VALUES({$ipaddress}) ")
or die(mysql_error());  
echo "Data Inserted!";
 
mysql_close($dbh);
?>
I need to insert the IP address without it duplicating...how do I tell PHP/MySQL to create a new record/row if the IP address is not yet part of the database?

Re: MySQL optimal table for IP list with purge dates?

Posted: Thu Feb 14, 2008 12:09 am
by JAB Creations
Progress! Right now when I reload the page it adds a single row inserting my IP address. At least it's adding my IP address though I need to have the script reframe from creating duplicate IP addresses, suggestions welcome at this time please! Here is what I have currently besides the connection steps...

Code: Select all

// Step #3: Create Table if none exists
mysql_query("
CREATE TABLE jab_ip_addresses (
  id int(10) unsigned NOT NULL auto_increment,
  user_date varchar(20) 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");
 
// Step #5: Attempt to insert IP Address in to table
mysql_query("
INSERT INTO jab_ip_addresses (ip) VALUES ('$ipaddress')
");
 
// Step #99: close database when finished!
mysql_close($dbh);

Re: MySQL optimal table for IP list with purge dates?

Posted: Thu Feb 14, 2008 2:57 am
by JAB Creations
WHOA I'm feeling great! The script below has evolved to the point where it will automatically create the table and then add your IP address if it's not yet in the database. Echoing variables helps a lot! I've added PHP comments to explain what the code is doing.

Next Step: I've got to figure out how to add the initial time the IP address is added to the database. Casually off hand I think I might later attempt to have PHP have MySQL remove an IP address if it's X amount of time too old (say a day, month, year, etc). Right now I'm just going to figure out how to get the date added! :mrgreen:

Code: Select all

// Step #3: Create Table if none exists
mysql_query("
CREATE TABLE jab_ip_addresses (
  id int(10) unsigned NOT NULL auto_increment,
  user_date varchar(20) 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");
 
// Step #5: Attempt to insert IP Address in to table
// Step #5.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 #5.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) VALUES ('$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 #99: close database when finished!
mysql_close($dbh);

Re: MySQL optimal table for IP list with purge dates?

Posted: Thu Feb 14, 2008 3:52 am
by JAB Creations
The site tizag.com has some pretty helpful information and keeps coming up for many of my searches. CURDATE at the end of line 29 below which seems to be MySQL specific (and thus won't work in PHP) is exactly what I wanted; unless of course there is a more specific date format for determining if the IP should be dropped (if it's too old). That's my next goal to figure out tonight. I'm on a roll tonight! :mrgreen:

Code: Select all

// 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("F d, Y");
 
// Step #5: Attempt to insert IP Address in to table
// Step #5.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 #5.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);
 
// Step #99: Show an example of what the date will look like in the MySQL database
echo '<br /><br />The date and it\'s exact formating added to MySQL: ';
echo Date("Y-m-d");

Re: MySQL optimal table for IP list with purge dates?

Posted: Thu Feb 14, 2008 8:39 am
by JAB Creations
Ha! I've added the option for testing purposes to intentionally purge your own IP from the database. You can confirm it's been removed even after it's been added back by checking if the id has auto-incremented! So essentially my goal is to have the script automatically drop an IP address if the current PHP date and the database date don't match up. That will prevent the database from aging and force the script to trigger again (in case for example your neighbor just so happens to get your IP address and they also just so happen to visit the same site the script is using). It could be adjusted later on though I think one day should be fine. If anyone has any suggestions as always please feel free to post your thoughts, you won't interrupt my "on the roll" or anything. :mrgreen: Any way now I just have to test it across the dates so instead of waiting for the server's midnight hour I'm just going to manually change the entry to a different date (past or future shouldn't matter).

Code: Select all

// Steps #1 and #2 = database connection stuffages
// 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);
?>