Sunday, October 28, 2012

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>&copy; 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&amp;pid='.$id.'">Edit</a> | <a href="'.$_SERVER['PHP_SELF'].'?action=Delete&amp;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

No comments:

Post a Comment