Phonebook Application with PHP and MySQL CRUD Operation
Definition
It is a simple Web-based phonebook application which stores and displays ID, Name, Contact No, and Address of a person. It is also capable of updating/editing and deleting phonebook entries.
Database
CREATE TABLE `phonebook` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`phone_no` VARCHAR(15) NOT NULL,
`address` VARCHAR(50) NOT NULL,
`date_created DATETIME` NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
PRIMARY KEY(`id`)
);
Codes
- phonebook.php
<?php
$newForm = 0;
$messages = array();
include './inc/newPhonebook.inc.php';
include './inc/deletePhonebook.inc.php';
include './inc/editPhonebook.inc.php';
if(isset($_POST['submit']) && $_POST['submit'] == 'Cancel'){
echo '<META http-equiv="REFRESH" content="0; url=index.php?action=Cancelled" />';
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Jupiter's Notebook - Phonebook Application</title>
<link rel="stylesheet" href="./css/phonebook.css" />
<script type="text/javascript" src="./js/phonebook.js"></script>
</head>
<body>
<div id="wrapper">
<header>
<hgroup>
<h2><a href="phonebook.php">Phonebook Application</a></h2>
<h4>A phonebook application with PHP & MySQL CRUD operation.</h4>
</hgroup>
</header>
<div id="main">
<section>
<form id="action" name="newPhonebookForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="GET">
<input type="submit" value="New" name="action" />
</form>
<noscript>
<p>Oppps! Your browser doesn't support or has disabled JavaScript. Please enable JavaScript.</p>
</noscript>
</section>
<?php
if(count($messages) > 0) {
echo '<section id="message"><p>';
foreach($messages as $message) {
echo $message.'<br />';
}
echo '</p></section>';
}
if($newForm == 1) include './inc/newPhonebookForm.inc.php';
if($editForm == 1) include './inc/editPhonebookForm.inc.php';
?>
<?php
if(isset($_GET['action']) && $_GET['action'] == 'New') {
include './inc/newPhonebookForm.inc.php';
}
if(isset($_GET['action']) && $_GET['action'] == 'Edit') {
include './inc/editPhonebookForm.inc.php';
}
?>
<section>
<?php include './inc/viewPhonebook.inc.php';?>
</section>
</div>
<footer>
<hgroup>
<h4>© 2012 <a href="http://www.jupitersnotebook.co.cc">Jupiter's Notebook</a></h4>
<h5>Go beyond the universe!</h5>
</hgroup>
</footer>
</div>
</body>
</html>
- css/phonebook.css
body {
background-color: #dedede;
font-family: Arial, Helvetica, sans-serif;
font-size: .8em;
}
header, #main, footer {
border: 5px #9d9fa0 solid;
margin: 4px auto;
padding: 5px 5px 5px 5px;
width: 600px;
-moz-border-radius:4px;
-webkit-border-radius:4px;
border-radius:4px;
-moz-box-shadow:2px 2px 5px #ccc;
-webkit-box-shadow:2px 2px 5px #ccc;
box-shadow:3px 3px 10px #ddd;
}
#main {
background: #dedede
}
header, footer {
background: #9d9fa0;
text-align: center;
}
section {
margin: 4px auto
}
section p {
padding-left: 15px
}
section#message {
background: #ddd;
border: 1px #9d9fa0 solid;
margin: 0 auto;
padding: 3px 3px 3px 3px;
width: 400px;
-moz-border-radius:4px;
-webkit-border-radius:4px;
border-radius:4px;
}
form label {
display: block;
font-weight: bolder;
padding-bottom: .1em;
}
form ul {
list-style-type: none
}
form li {
padding-bottom: .5em;
}
form#action ul{
display: inherit
}
form input[type=text] {
height: 26px;
background-color: #bbb;
border-radius: 5px;
-webkit-border-radius: 5px;
-moz-border-radius: 5px;
}
form select {
border:1px solid #aaa;
color:#333333;
padding:3px;
background-color: #bbb;
border-radius:2px;
-moz-border-radius:2px;
-webkit-border-radius:2px;
box-shadow:0 1px 2px #ddd;
-moz-box-shadow:0 1px 2px #ddd;
-webkit-box-shadow:0 1px 2px #ddd;
}
fieldset {
border: 2px #9d9fa0 solid;
border-radius:4px;
-moz-border-radius:4px;
-webkit-border-radius:4px;
}
input[type=submit]{
font-weight:bold;
margin-left:14px;
border: 1px solid #aaa;
padding: 3px 7px;
color: #111;
text-decoration: none;
background: #ddd;
border-radius: 12px;
-webkit-border-radius: 12px;
-moz-border-radius: 12px;
text-shadow: 0px 1px 0px #fff;
background-size: 100% 100%;
}
input[type=submit]:hover{
position: relative;
background-size: 100% 100%;
background: -webkit-gradient(linear, left top, left bottom, from(#cccccc), to(#dddddd));
background: -moz-linear-gradient(top, #cccccc, #dddddd);
background: -o-linear-gradient(top, #cccccc, #dddddd);
cursor:pointer;
}
input[type=submit]:active{
position: relative;
top: 1px;
left: 1px;
}
table {
margin: 0 auto;
width: 550px;
border: 1px #000 solid;
border-collapse: collapse;
}
th {
border: 1px #000 solid;
background-color: #9d9fa0;
}
td {
border: 1px #000 solid;
background-color: #ddd;
font-size: 12px;
}
td#action {
text-align: center;
}
a {
color: #000;
text-decoration: none
}
a:hover {
text-decoration: underline
}
- js/phonebook.js
function checkForm() {
if(document.phonebookForm.name.value == "" || document.phonebookForm.phoneNo.value == "" || document.phonebookForm.address.value == "") {
alert("Oppps! Please complete the form.");
return false;
}
}
function confirmToDelete(id, id2, id3) {
var option=confirm("Delete contact with ID " + id + "?");
if (option == true) return true
else return false;
}
- inc/dbConfig.inc.php
<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', 'admin123');
define('DB_DATABASE', 'mydb');
?>
- inc/newPhonebookForm.inc.php
<section>
<form name="phonebookForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
<fieldset>
<legend>New Phonebook</legend>
<ul>
<li>
<label>Name:</label>
<input type="text" name="name" id="name"value="<?php echo $name; ?>" size="25" />
</li>
<li>
<label>Phone No.:</label>
<input type="text" name="phoneNo" value="<?php echo $phoneNo; ?>" size="25" />
</li>
<li>
<label>Address:</label>
<input type="text" name="address" value="<?php echo $address; ?>" size="25" />
</li>
<li>
<input type="submit" value="Save" name="submit" onclick="return checkForm()" />
<input type="submit" value="Cancel" name="submit" />
</li>
</ul>
</fieldset>
</form>
</section>
- inc/newPhonebook.inc.php
<?php
include_once './inc/dbConfig.inc.php';
$connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
if(!$connection) die('Database connection failed!');
$name = (isset($_POST['name'])) ? (trim($_POST['name'])) : '';
$phoneNo = (isset($_POST['phoneNo'])) ? (trim($_POST['phoneNo'])) : '';
$address = (isset($_POST['address'])) ? trim($_POST['address']) : '';
if(isset($_POST['submit']) && $_POST['submit'] == 'Save') {
if(empty($name)) $messages[] = 'Oppps! Name is required.';
if(empty($phoneNo)) $messages[] = 'Oppps! Phone No. is required.';
if(empty($address)) $messages[] = 'Oppps! Address is required.';
if(count($messages) < 1) {
$name = mysqli_real_escape_string($connection, $name);
$phoneNo = mysqli_real_escape_string($connection, $phoneNo);
$address = mysqli_real_escape_string($connection, $address);
$dateCreated = date('Y-m-d H:i:s');
$query = sprintf("INSERT INTO phonebook(id, name, phone_no, address, date_created)
VALUES(NULL, '%s', '%s', '%s', '%s')", $name, $phoneNo, $address, $dateCreated);
$result = mysqli_query($connection, $query);
if(!$result) $messages[] = "Unable to add phonebook!";
else {
$messages[] = "Success! Phonebook has been saved.";
$name = '';
$phoneNo = '';
$address = '';
}
} else {
$newForm = 1;
}
}
mysqli_close($connection);
?>
- inc/viewPhonebook.inc.php
<?php
$connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
if(!$connection) die('Database connection failed!');
$query = sprintf("SELECT * FROM phonebook ORDER BY id ASC");
$result = mysqli_query($connection, $query);
if(!$result) $messages[] = "Unable to view phonebook!";
else {
if(mysqli_num_rows($result) > 0) {
echo '<table border="0">';
echo '<thead>';
echo '<tr>';
echo '<th>ID</th>';
echo '<th>Name</th>';
echo '<th>Phone No.</th>';
echo '<th>Address</th>';
echo '<th>Edit | Delete</th>';
echo '</tr>';
echo '</thead>';
echo '<tbody>';
while($rows = mysqli_fetch_array($result, MYSQL_BOTH)) {
$id = $rows['id'];
$name = $rows['name'];
$phoneNo = $rows['phone_no'];
$address = $rows['address'];
echo '<tr>';
echo '<td>'.$id.'</td>';
echo '<td>'.$name.'</td>';
echo '<td>'.$phoneNo.'</td>';
echo '<td>'.$address.'</td>';
echo '<td id="action"><a href="'.$_SERVER['PHP_SELF'].'?action=Edit&pid='.$id.'">Edit</a> | <a href="'.$_SERVER['PHP_SELF'].'?action=Delete&pid='.$id.'" onclick="return confirmToDelete('.$id.');" >Delete</a><br /></td>';
echo '</tr>';
}
echo '</tbody>';
echo '</table>';
} else echo '<p>Phonebook is empty.</p>';
}
mysqli_free_result($result);
mysqli_close($connection);
?>
- inc/editPhonebookForm.inc.php
<?php
include_once './inc/dbConfig.inc.php';
$connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
if(!$connection) die('Database connection failed!');
if(isset($_GET['pid']))
$pid = (isset($_GET['pid'])) ? (trim($_GET['pid'])) : '';
if(isset($_POST['id']))
$pid = (isset($_POST['id'])) ? (trim($_POST['id'])) : '';
$query = sprintf("SELECT * FROM phonebook WHERE id = '%d'", $pid);
$result = mysqli_query($connection, $query);
if(!$result) $messages[] = "Unable to retrieve phonebook information!";
else {
if(mysqli_num_rows($result) > 0) {
$rows = mysqli_fetch_array($result, MYSQL_BOTH);
$id = $rows['id'];
$name = $rows['name'];
$phoneNo = $rows['phone_no'];
$address = $rows['address'];
?>
<section>
<form name="phonebookForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
<fieldset>
<legend>Edit Phonebook</legend>
<ul>
<li>
<label>ID:</label>
<input type="text" name="id" id="id" value="<?php echo $id; ?>" size="5" disabled="disabled" />
</li>
<li>
<label>Name:</label>
<input type="text" name="name" id="name"value="<?php echo $name; ?>" size="25" />
</li>
<li>
<label>Phone No.:</label>
<input type="text" name="phoneNo" value="<?php echo $phoneNo; ?>" size="25" />
</li>
<li>
<label>Address:</label>
<input type="text" name="address" value="<?php echo $address; ?>" size="25" />
</li>
<li>
<input type="hidden" value="<?php echo $pid;?>" name="id" />
<input type="submit" value="Update" name="submit" onclick="return checkForm()" />
<input type="submit" value="Cancel" name="submit" />
</li>
</ul>
</fieldset>
</form>
</section>
<?php
}
}
mysqli_free_result($result);
mysqli_close($connection);
?>
- inc/editPhonebook.inc.php
<?php
$editForm = 0;
include_once './inc/dbConfig.inc.php';
$connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
if(!$connection) die('Database connection failed!');
$id = (isset($_POST['id'])) ? (trim($_POST['id'])) : '';
$name = (isset($_POST['name'])) ? (trim($_POST['name'])) : '';
$phoneNo = (isset($_POST['phoneNo'])) ? (trim($_POST['phoneNo'])) : '';
$address = (isset($_POST['address'])) ? trim($_POST['address']) : '';
if(isset($_POST['submit']) && $_POST['submit'] == 'Update') {
if(empty($name)) $messages[] = 'Oppps! Name is required.';
if(empty($phoneNo)) $messages[] = 'Oppps! Phone No. is required.';
if(empty($address)) $messages[] = 'Oppps! Address is required.';
if(count($messages) < 1) {
$name = mysqli_real_escape_string($connection, $name);
$phoneNo = mysqli_real_escape_string($connection, $phoneNo);
$address = mysqli_real_escape_string($connection, $address);
$query = sprintf("UPDATE phonebook SET name = '%s', phone_no = '%s', address = '%s' WHERE id = '%d'", $name, $phoneNo, $address, $id);
$result = mysqli_query($connection, $query);
if(!$result) $messages[] = "Unable to update phonebook!";
else {
$messages[] = "Success! Phonebook with ID ".$id.' has been updated.';
$name = '';
$phoneNo = '';
$address = '';
}
} else {
$editForm = 1;
}
}
mysqli_close($connection);
?>
- inc/deletePhonebook.inc.php
<?php
include_once './inc/dbConfig.inc.php';
$connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
if(!$connection) die('Database connection failed!');
$id = (isset($_GET['pid'])) ? (trim($_GET['pid'])) : '';
if(isset($_GET['action']) && $_GET['action'] == 'Delete') {
if(empty($id)) $messages[] = 'Oppps! There seems to have a problem.';
if(count($messages) < 1) {
$id = mysqli_real_escape_string($connection, $id);
$query = sprintf("DELETE FROM phonebook WHERE id = '%d'", $id);
$result = mysqli_query($connection, $query);
if(!$result) $messages[] = "Unable to delete phonebook with ID ".$id;
else {
$messages[] = "Success! Phonebook with ID ".$id.' has been deleted.';
}
mysqli_close($connection);
}
}
?>
Download Files
Comments
Post a Comment