Simple PHP web based address book using MySql

Today we will be creating a web based address book using PHP and MySql, it will be using the $_GET superglobal, as a means of reviewing the database contents.

Before we start here are the source files:

http://diadde.com/dl/addressBook.zip

Working demo:

http://diadde.com/test/addressTest.php

First, create the data base using this sql snippet:

CREATE TABLE IF NOT EXISTS `address` (
`id` int(4) NOT NULL auto_increment,
`name` varchar(30) default NULL,
`phone` varchar(30) default NULL,
`email` varchar(30) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

Then insert some data into our database using this snippet:

INSERT INTO `address` (`id`, `name`, `phone`, `email`) VALUES
(1, 'Laika Clay', '430-555-2252', 'laika@doggie.com'),
(2, 'Tiger Clay', '658-555-5985', 'tiger@kittie.us'),
(4, 'A Clay', '555-777-0000', 'clay@php.com'),
(5, 'Santa Clause', '888-888-7777', 'santa@np.net');

Now that we have that in place, we can create the form that be used for adding and editing the entry:

<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=added" method="post">
<table class="tableStyleClassTwo">
<tr><td>Name:</td><td><div align="left">
<input type="text" name="name" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> | <input name="Submit" type="submit" id="Submit" value="Add New Contact" <?php if($disable ==1){?>disabled<?php } ?>/></td></tr>
<input type="hidden" name="mode" value="added">
</table>

Simple enough, we will use this form in our all in one page address book, it will be used in adding and editing the database contents (addresses).

First thing we want to do is to setup the variable that will be used to run this thing:

$mode = $_GET['mode'];

The above will be called throughout the app to control the functions of it.

Before we can use the $mode var we setup a switch, this will be used in different sections of the app to do the actions:

switch($mode){

//cases in bewteen

}

If you are used to using ASP or Vb Script switch is the same as select case.

The cases are as follows:

• add
• added
• edit
• edited
• remove

ADD:

This Case will add new contacts to our database, and is called by a hyperlink which will use the word 'add', look in the address bar after you click this, and will see this after the ? mark.

case 'add':
?>
<h2>Add Contact</h2>
<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=added" method="post">
<table class="tableStyleClassTwo">
<tr><td>Name:</td><td><div align="left">
<input type="text" name="name" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> | <input name="Submit" type="submit" id="Submit" value="Add New Contact" <?php if($disable ==1){?>disabled<?php } ?>/></td></tr>
<input type="hidden" name="mode" value="added">
</table>
</form>
<?php
break;

ADDED:

This will called the case added, but unlike add it will use the querystring (address bar) to function and then redirect the page after we are done with adding the contact.

//added a record
case 'added':
//first setup the vars
$name = $_POST['name'];
$phone = $_POST['phone'];
$email = $_POST['email'];
//then lets use'em
$sql = "INSERT INTO address (name, phone, email) VALUES ('" . $name . "','" . $phone . "','" . $email . "')";
//echo $sql;
//return;
mysql_query($sql);
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

EDIT:

Calls the address using the address bar vars with name , email , phone number , but moore importantly the id of the record. We need this to perform the edit on that record, we have to be specific on the updating of the records, other wise we could update all the records with same information (no go)

?>
<h2>Editing: <?=$_GET['name'];?></h2>
<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=edited" method="post">
<table width="399" class="tableStyleClassTwo">
<tr><td width="87">Name:</td>
<td width="551"><div align="left">
<input type="text" value="<?=$_GET['name'];?>" name="name" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" value="<?=$_GET['phone'];?>" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" value="<?=$_GET['email'];?>" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> |<input name="Submit" type="submit" value="Save Changes" /></td></tr>
<input type="hidden" name="mode" value="edited">
<input type="hidden" name="id" value="<?=$_GET['id'];?>">
</table>
</form>
<?php
break;

EDITED:

This uses the id we planted inside of the form, in a text field named id, to show this click on a record to edit, look at the code source, (view source), you shold see an input named "id". That was used in the update sql to update that record ONLY.

case 'edited':
//again clarify the vars
$name = $_POST['name'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$id = $_POST['id'];
//do the query
$sql = "UPDATE address SET name = '" . $name ."', phone = '" . $phone . "', email = '" . $email . "' WHERE id = '" . $id . "'";
mysql_query($sql);
//echo $sql;
//return;
//below you can either redirect show a message or put a link, and if you think harder you can probably do alot more
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

REMOVE:

Does what is says deletes the record, once and for all, there is no coming back after this.

case 'remove':
$id = $_GET['id'];
//lets remove the record this one is easy
$sql ="delete from address where id= '" . $id ."'";
//run the query
mysql_query($sql);
//echo $sql;
//return;
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

DEFAULT:

//this will show the table scructure by default (ie, no actions)
default:
//opening query
$sql ="SELECT * FROM address ORDER BY name ASC";
$data = mysql_query($sql);
//you can put in an error statement if no records or just display, just do what makes sense to you, the rest will come
?>
<h2>Phone Book Example</h2>
<table class="tableStyleClass">
<tr>
<th width="100">Name</th>
<th width="100">Phone</th>
<th width="200">Email</th>
<th width="100" colspan="2">Admin</th>
</tr>
<td colspan="5" align="right"><?php if($disable!=1){?><div align="right"><a href="<?=$_SERVER['PHP_SELF'];?>?mode=add"?mode=add>Add Contact</a><?php }else{?>Contact Book is Full<?php } ?></div></td>
<?php
//lets set a variable for offest coloered rows
$rowColor = 0;
//here is the loop using the statement above
while($info = mysql_fetch_array( $data )){
if($rowColor==0){
?>
<tr class="oddClassStyle">
<?php
$rowColor =1;
}elseif($rowColor==1){
?>
<tr class="evenClassStyle">
<?php
$rowColor = 0;
}
?>
<td><?=$info['name'];?></td>
<td><?=$info['phone'];?></td>
<td><a href=mailto:"<?=$info['email'];?>"><?=$info['email'];?></a></td>
<td><a href="<?=$_SERVER['PHP_SELF'];?>?id=<?=$info['id'];?>&name=<?=$info['name'];?>&phone=<?=$info['phone'];?>&email=<?=$info['email'];?>&mode=edit" >Edit </a></td>
<td><a href="<?=$_SERVER['PHP_SELF'];?>?id=<?=$info['id'];?>&mode=remove">Remove</a></td>
</tr>
<?php
}
?>
</table>
<?php
break;

This is a special case that defaults and show what we want to display in case we are not trying to edit or add, etc.

The entire code follows:

<?php
ob_start();//this just buffers the header so that you dont recieve an error for returning to the same page
if(isset($_GET['id']) && $_GET['mode'] == 'edit'){
//lets get the details for the paage title
$title = "We are editing: " . $_GET['name'] . " are you sure!!!";
}
?>
<html>
<head>
<title><?php if(!$title){?>Address Book<?php }else{ echo $title; }//end if?></title>
<style>
body{font-family:Arial, Helvetica, sans-serif;font-size:10px;}
table.tableStyleClass{border-collapse:collapse;border:1px solid #cccccc;background-color:#f1f1f1;width:650px;font-family:Arial, Helvetica, sans-serif;font-size:11px;}
table.tableStyleClassTwo{border-collapse:collapse;border:1px solid #cccccc;background-color:#f1f1f1;width:350px;font-family:Arial, Helvetica, sans-serif;font-size:11px;}
th{background-color:#999999;color:#ffffff;margin:1px;}
td{border-right:1px solid #cccccc;padding:2px;text-align:center;}
.oddClassStyle{background-color:#ffffff;border-bottom:1px solid #cccccc;}
.evenClassStyle{background-color:#f1f1f1;border-bottom:1px solid #cccccc;}
</style>
</head>
<body>

<?php
// Connects to your Database
mysql_connect("#", "#", "#") or die(mysql_error());
mysql_select_db("address") or die(mysql_error());
//we will use a case switch to look for the variable to make the decisions on what to show
//this is the variable that will control the switch case
//first lets set it looking for a query string or a post version of it
/*if(isset($_GET['id'])){
$mode = $_GET['mode'];//address bar version
$id = $_GET['id'];
}else{
$mode = $_POST['mode'];//form based version
$id = $_POST['id'];
}// now we know yay*/
//begin the switch
$mode = $_GET['mode'];
//look to see if the book is full
$checkSql="select count(id) as eCount from address";
$result = mysql_query($checkSql);
$row = mysql_fetch_assoc($result);
if($row['eCount'] == 6){
$disable = 1;
}
switch($mode){
//add a record
case 'add':
?>
<h2>Add Contact</h2>
<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=added" method="post">
<table class="tableStyleClassTwo">
<tr><td>Name:</td><td><div align="left">
<input type="text" name="name" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> | <input name="Submit" type="submit" id="Submit" value="Add New Contact" <?php if($disable ==1){?>disabled<?php } ?>/></td></tr>
<input type="hidden" name="mode" value="added">
</table>
</form>
<?php
break;
//added a record
case 'added':
//first setup the vars
$name = $_POST['name'];
$phone = $_POST['phone'];
$email = $_POST['email'];
//then lets use'em
$sql = "INSERT INTO address (name, phone, email) VALUES ('" . $name . "','" . $phone . "','" . $email . "')";
//echo $sql;
//return;
mysql_query($sql);
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

case 'edit':
?>
<h2>Editing: <?=$_GET['name'];?></h2>
<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=edited" method="post">
<table width="399" class="tableStyleClassTwo">
<tr><td width="87">Name:</td>
<td width="551"><div align="left">
<input type="text" value="<?=$_GET['name'];?>" name="name" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" value="<?=$_GET['phone'];?>" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" value="<?=$_GET['email'];?>" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> |<input name="Submit" type="submit" value="Save Changes" /></td></tr>
<input type="hidden" name="mode" value="edited">
<input type="hidden" name="id" value="<?=$_GET['id'];?>">
</table>
</form>
<?php
break;

case 'edited':
//again clarify the vars
$name = $_POST['name'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$id = $_POST['id'];
//do the query
$sql = "UPDATE address SET name = '" . $name ."', phone = '" . $phone . "', email = '" . $email . "' WHERE id = '" . $id . "'";
mysql_query($sql);
//echo $sql;
//return;
//below you can either redirect show a message or put a link, and if you think harder you can probably do alot more
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

case 'remove':
$id = $_GET['id'];
//lets remove the record this one is easy
$sql ="delete from address where id= '" . $id ."'";
//run the query
mysql_query($sql);
//echo $sql;
//return;
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

//this will show the table scructure by default (ie, no actions)
default:
//opening query
$sql ="SELECT * FROM address ORDER BY name ASC";
$data = mysql_query($sql);
//you can put in an error statement if no records or just display, just do what makes sense to you, the rest will come
?>
<h2>Phone Book Example</h2>
<table class="tableStyleClass">
<tr>
<th width="100">Name</th>
<th width="100">Phone</th>
<th width="200">Email</th>
<th width="100" colspan="2">Admin</th>
</tr>
<td colspan="5" align="right"><?php if($disable!=1){?><div align="right"><a href="<?=$_SERVER['PHP_SELF'];?>?mode=add"?mode=add>Add Contact</a><?php }else{?>Contact Book is Full<?php } ?></div></td>
<?php
//lets set a variable for offest coloered rows
$rowColor = 0;
//here is the loop using the statement above
while($info = mysql_fetch_array( $data )){
if($rowColor==0){
?>
<tr class="oddClassStyle">
<?php
$rowColor =1;
}elseif($rowColor==1){
?>
<tr class="evenClassStyle">
<?php
$rowColor = 0;
}
?>
<td><?=$info['name'];?></td>
<td><?=$info['phone'];?></td>
<td><a href=mailto:"<?=$info['email'];?>"><?=$info['email'];?></a></td>
<td><a href="<?=$_SERVER['PHP_SELF'];?>?id=<?=$info['id'];?>&name=<?=$info['name'];?>&phone=<?=$info['phone'];?>&email=<?=$info['email'];?>&mode=edit" >Edit </a></td>
<td><a href="<?=$_SERVER['PHP_SELF'];?>?id=<?=$info['id'];?>&mode=remove">Remove</a></td>
</tr>
<?php
}
?>
</table>
<?php
break;

}//end the switch
?>
</body>
</html>
<?php ob_flush();?>

You may notice some other functions like ob_start(), etc that I have included in the tut, these I will be happy to explain if you have any comments or questions.

Recap: There are more elegant and secure ways to capture and display the data, but the point of the article is give new users something to build on and develop their own techniques and habits.

There are alot of features that could be used in this, we could archive the addresses, instead of deleting, use some nice images to redecorate the whole app, even incorporate this into something that could bulk imprt address, maybe some API, yahoo anyone, skies the limit. Anyway take it and run with it.

Once again thank you and ask if any questions or concerns.