PHP & MySQL: How to Store IP Addresses using PHP & MySQL

Demo
Download

Last week, while working on the Wrapports Media Employee Intranet project at work, I needed to re-build a 10-year old IP Address Management application that was not only deprecated, but also did not display or sort the IP Addresses correctly. Writing the new version in PHP with a MySQL backend, here is a look at how I built a IP Address Manager with PHP and MySQL. If you are only interested in downloading the full script, you can skip to PHP & MySQL IP Address Manager Script Download

First, lets setup the most integral part of this script – the MySQL Database. Copy and paste this script into your phpMyAdmin or SQLyog query window:
database.sql

CREATE TABLE IF NOT EXISTS `ips` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ip` int(65) unsigned NOT NULL,
  `hostname` varchar(255) NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notes` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

Looking at the ip field, notice that it is a INT(65) unsigned, which is what will index and sort the IPs correctly (99% of the time INT will be signed).

With the database created, you will need 4 files for this script: index.php, edit.php & 2 classes class.db.php & class.ips.php.

We are going to write the View IP and Add IP into the index.php page.

index.php — [Part 1] Adding & Viewing the IP Addresses:

<?php 
include('class.db.php');
include('class.ips.php');
	
$manager = new IP_Manager;
	
   if(isset($_POST['form'])) {
    $form = $_POST['form'];
			 
     switch ($form) {
	case 'add_ip':
	  $ip 	    = $_POST['ip_address'];
	  $hostname = $_POST['ip_hostname'];
	  $notes    = $_POST['ip_notes'];
	   $notes   = addslashes($notes);
	  $manager->addIP($ip, $hostname, $notes);
	break;
			 		
	default:
	break;
     }
   }
?>

We use a switch() statement to figure out what form is being submitted, whether it is the Add ip or edit IP forms. This also allows you to add more forms to the script, in case you had separate forms for IPv4 or IPv6 addresses. As you can see when retrieving the $notes data, we insert a addslashes() function to deal with quotations and sanitize the data before submitting it to the database. Before we get to the Add IP Address Form we will go through retrieving and displaying IPs from the database.

index.php — [Part 2] Adding & Viewing the IP Addresses:

<html>
<head>
<meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <meta name="keywords" content="php, mysql, ip address, tutorial">
  <meta name="description" content="PHP and MySQL Tutorial and Script Example on how to add 
and store IP Addresses">

<title>MDLORING.COM - PHP & MySQL IP Address Manager Script</title>

<link rel='stylesheet' id='admin-bar-css'  href='./style.css' type='text/css' media='all' />
</head>
<body>
 <div id="wrapper" style="width:950px;margin:auto;">
  <h1>IP Address Manager Tutorial & Script Example</h1>
   <section>
    <nav>
     <ul class="ipList">
	<li><a href="#view">View IPs</a> &#8212;</li>
	<li><a href="#add">Add IPs</a>
     </ul>
    </nav>
   </section>

   <section>
    <div id="ip_List" style="background:#CCCCCC;padding:5px;border:2px solid #729ea5;">
     <h3><a name="view" class="none">IP List & Options</a></h3>
      <table id="tfhover" class="tftable" border="1">
	<tr>
	 <th>ID</th>
	 <th>IP</th>
	 <th>Hostname</th>
	 <th>Notes</th>
	 <th>Modified</th>
	 <th>Options</th>
	</tr>
				
 <?php 
   $ips = $manager->getIPs();
    $int = 0;
	foreach($ips as $ip) { 
	 $int++;
	  $id       = $ip['id'];
	  $new_ip   = $manager->readableIP($ip['ip']);
	  $hostname = $ip['hostname'];
	  $notes    = $ip['notes'];
	  $modified = date('F d, Y', strtotime($ip['modified']));		
  ?>
	<tr>
	 <th><?php echo $id; ?></th>
	 <th><?php echo $new_ip; ?></th>
	 <th><?php echo $hostname; ?></th>
	 <th><?php echo $notes; ?></th>
	 <th><?php echo $modified; ?></th>
	 <th><a href="./edit.php?id=<?php echo $id; ?>">modify</a></th>
	</tr>		
 <?php	} ?>
      </table>
 <?php echo ($int == 0 ? 'No IPs have been <a href=\'#add\'>added</a>' : ''); ?>
</div>


This is a basic HTML table display IPs from the database. Since we are using a PHP Class to pull the data, making the call is only 1 line:

$ips = $manager->getIPs();


with $manager being the instance of our IP_Manager class. The function itself looks like this:

function getIPs() {
  $ips = $this->fetch("SELECT * FROM `ips`");
   return $ips;
}


Since the data is in an array, we use a foreach() statement to cycle through our IP database and list the data. The last bit of code for the Add IP section is a basic HTML form.

index.php — [Part 3] Adding & Viewing the IP Addresses:

 <div id="clear" style="padding:5px;"></div>
  
 <hr />
    <div id="add_file" style="background:#CCCCCC;padding:5px;border:2px solid #729ea5;">
     <h3><a name="add" class="none">Add IPv4 Address</a></h3>
      <form name="add_ip" id="add_ip" action="index.php" method="POST">
        <input type="hidden" name="form" value="add_ip" />
       <p>
        <input type="text" name="ip_address" id="ip_address" placeholder="IPv4 Address" 
               style="width:50%;" />
       </p>
       <p>
        <input type="text" name="ip_hostname" id="ip_hostname" placeholder="IP Hostname" 
               style="width:50%;" /><br />
       </p>
       <p>
        <textarea name="ip_notes" id="ip_notes" rows="5" cols="20" placeholder="IP Address Notes" 
               style="width:50%;"></textarea><br />
       </p>
        <input type="submit" value="Add IP" />
        <input type="reset" value="Clear Form" />
     </form>
    </div>
  </section>
</body>
</html>

Moving onto the Edit IP functionality of our IP Manager, the setup is very similar but with a few function changes.

edit.php — [Part 1] Modifying a selected IP Address:

<?php 
	include('class.db.php');
	include('class.ips.php');
	
	$manager = new IP_Manager;
	
	if(isset($_POST['form'])) {
	 $form = $_POST['form'];
	
	  switch ($form) {
			
	   case 'edit_ip':
	     $ip       = $_POST['ip_address'];
	     $hostname = $_POST['ip_hostname'];
	     $notes    = $_POST['ip_notes'];
	      $notes   = addslashes($notes);
	     $id       = $_POST['id'];
	     echo $manager->editIP($ip, $hostname, $notes, $id);
	    break;
	
	    default:
	    break;
          }
	}
?> 

Like with the Add IP file, we use a switch() statement in case we ever add any further functionality to this page. With our code setup for when the Edit IP Form is submitted, we can write the actual form and to make sure an IP ID has been set from the previous View IP table in index.php.

edit.php — [Part 2] Modifying a selected IP Address:

<section>
	
<?php 
  if(isset($_GET['id'])) {
    $id = $_GET['id'];

     if(isset($_GET['a'])) {
       $a = $_GET['a'];
	if($a == 'del') {
	  echo $manager->deleteIP($id);
	}
     } else {
	$data = $manager->getIP($id);
	  $ip 	    = long2ip($data[0]['ip']);
	  $hostname = $data[0]['hostname'];
	  $notes    = $data[0]['notes'];
?>
   <div id="edit_ip" style="background:#CCCCCC;padding:5px;border:2px solid #729ea5;">
    <h3><a name="edit" class="none">Edit IP Address</a></h3>
     <p><span class="total"><a href="./edit.php?a=del&id=<?php echo $id; ?>">delete ip</a> &#8212;
        this action cannot be undone!</span>
     <form name="edit_ip" id="edit_ip" action="index.php" method="POST">
      <input type="hidden" name="form" value="edit_ip" />
     <p>
      <input type="text" name="ip_address" id="ip_address" placeholder="IP Address" 
             style="width:50%;" value="<?php echo $ip; ?>" />
     </p>
     <p>
      <input type="text" name="ip_hostname" id="ip_hostname" placeholder="IP Hostname" 
             style="width:50%;" value="<?php echo $hostname; ?>" /><br />
     </p>
     <p>
      <textarea name="ip_notes" id="ip_notes" rows="5" cols="20" placeholder="IP Address Notes"
style="width:50%;"><?php echo $notes; ?></textarea><br />
     </p>
      <input type="submit" value="Edit IP" />
    </form>
   </div>

 <?php 
	} //end if action
      } else {
	echo "<h2>No IP Address was selected, <a href=\"javascript:history.back()\">go back</a>.";
      }
?>
</section>

Here we first check to make sure that the $id = $_GET[‘id’]; variable isset(), and from there retrieve the IPs data using our $manager->getIP($id) function, which looks like this:

function getIP($id) {
  $ip	= $this->fetch("SELECT * FROM `ips` WHERE id = '$id'");
     return $ip;
}


With our index.php and edit.php files finished, which are the user interface portions of the script, we can move onto the “meat” of our code, the PHP Classes that hold our functions that make the script work. First we need to setup our database class that holds the MySQL Connection information.

class.db.phpMySQL Database Information PHP Functions Class:

<?php 

class DB_Class {
	var $host = "localhost"; //database host
	var $username = ""; //database username
	var $password = ""; //database password
	var $database = ""; //mysql database
	
	
	function query($sql) {
	      $this->db = mysql_connect($this->host, $this->username, $this->password);
	       mysql_select_db($this->database, $this->db) or die ("Could not select database");
		 $result = mysql_query($sql, $this->db) or die ("Invalid query: " . mysql_error());
		  return $result;
	}

	function numRows($result) {
		$count = mysql_num_rows($result);
		  return $count;
	}

	function fetch($sql) {
		$this->db = mysql_connect($this->host, $this->username, $this->password);
		 mysql_select_db($this->database, $this->db) or die ("Could not select database");
		  $data = array();
		   $result = $this->query($sql);

		while($row = mysql_fetch_assoc($result)) {
			$data[] = $row;
		}
		  return $data;
	}

}

?>

Finally, our IP Address Manager Class, which holds all of the core functionality of the script.

class.ips.phpIP Address Manager Core PHP Functions Class:

<?php 
/************************************
 * APPLICATION: IP Address Manager
* AUTHOR: Michael Loring
* WEBSITE: http://www.mdloring.com
* CREATED: November 02, 2013
************************************/
	
 class IP_Manager extends DB_Class {
 	
	function addIP($ip, $hostname, $notes) {
	  $new_ip = IP_Manager::convertIP($ip);
	    if($this->query("INSERT INTO `ips` SET ip = '$new_ip', hostname = '$hostname', 
                             notes = '$notes'")) {
		$msg = "<strong>Success!</strong> $new_ip has been <em>added</em>";
	    } else {
		$msg = "<strong>Error</strong> There was a problem <em>adding</em> $ip";
	    }
		return $msg;
	}
	
	function editIP($ip, $hostname, $notes, $id) {
	  $new_ip = IP_Manager::convertIP($ip);
	    if($this->query("UPDATE `ips` SET ip = '$new_ip', hostname = '$hostname', 
                             notes = '$notes' WHERE id = '$id'")) {
		$msg = "<strong>Success!</strong> $ip has been <em>modified</em>";
	    } else {
		$msg = "<strong>Error</strong> There was a problem <em>modifying</em> $ip";
	    }
		return $msg;
	}
	
	function getIPs() {
	  $ips = $this->fetch("SELECT * FROM `ips`");
		return $ips;
	}
	
	function getIP($id) {
	  $ip	= $this->fetch("SELECT * FROM `ips` WHERE id = '$id'");
		return $ip;
	}

        function deleteIP($id) {
	  $ip_data = IP_Manager::getIP($id);
	   $ip = IP_Manager::readableIP($ip_data[0]['ip']);
	    $this->query("DELETE FROM `ips` WHERE id = '$id'");
	     $msg = "<strong>Success!</strong> $ip has been <em>deleted</em> &#8212; 
		     <span class=\"total\"><a href=\"./index.php\">back to ip list</a></span>";
		return $msg;
	}
	
 	function convertIP($ip) {
        	return ip2long($ip);
    }
	
	function readableIP($ip) {
			return long2ip($ip);
	}
	
 }

?>


From this PHP Class there is nothing overly complicated or special, but there are 2 PHP functions that you should take note of that make this script work so well with PHP: ip2long(), which takes the IPv4 and generates it into a full integer network address (ex: 3182235556), and long2ip(), which converts the network address back into a [dotted] human readable address (ex: 127.0.0.1). Though simple to implement into your project, these 2 functions are the base of the project and is what makes this script tick.

And there you have it! A PHP based IP Address Manager with a MySQL backend that is specifically built and structured to properly store, retrieve and display IP Addresses using PHP and MySQL.

OPTIONAL style sheet:
style.css

body {
	font-size:12px;
	font-family:Verdana;
}

li {
	display:inline;
}

hr {
	border:5px solid #729EA5;
	border-radius:2px;
	margin-bottom:15px;
}

input, textarea {
	border: 1px solid #729ea5;
	padding:5px;
	font-family: Verdana;
	font-size:12px;
}

p.success {
	color: green;
}

p.fail {
	color: red;
}

img.thumb {
	width:100px;
	height:100px;
}

ul.ipList {
	list-style-type:none;
	display:inline;
	font-size:14px;
	text-transform:uppercase;
}

li.ipList {
	display:inline;
	text-transform:uppercase;
	color:#ACC8CC;
}

a.none {
	text-decoration:none;
	color:#000022;
}

nav {
	background:#CCCCCC;
	border:2px solid #ACC8CC;
	border-radius:5px;
	padding:5px;
	margin-bottom:10px;
	color:#678589;
	text-align:center;
}

nav a {
	color:#000022;
	text-decoration:none;
	font-weight:bolder;
}

nav a:hover {
	color:#729EA5;
	font-weight:bolder;
	text-transform:lowercase;
}

span.total {
	color:#06616D;
	font-weight:bolder;
	font-size:12px;
}

span.total a:hover {
	color:#729EA5;
	font-weight:bolder;
	text-transform:lowercase;
}

span.total a {
	color:#000022;
	text-decoration:none;
	font-weight:bolder;
}
	
table.tftable {
	font-size:12px;
	color:#333333;
	width:100%;
	border-width: 1px;
	border-color: #729ea5;
	border-collapse: collapse;
}

table.tftable th {
	font-size:12px;
	background-color:#acc8cc;
	border-width: 1px;
	padding: 8px;
	border-style: solid;
	border-color: #729ea5;
	text-align:left;
}

table.tftable tr {
	background-color:#d4e3e5;
}

table.tftable td {
	font-size:12px;
	border-width: 1px;
	padding: 8px;
	border-style: solid;
	border-color: #729ea5;
}

Leave a Comment

Your email address will not be published. Required fields are marked *