php CRUD Operation ( 2 Layer Architecture)

What Will I Learn?

In This Tutorial , we will learn the php CRUD Operation . First of All, We have to know What is CRUD .
CRUD means Create , Retrieve, Update and Delete.

• You will learn from this Tutorial, How to Create, Update, delete a Person Information on database.
• Also Learn How to search a Person Information retrieve from the database.
• See the details information of a person.

Requirements

There are Some requirements to learn this tutorial in the given below :

• basic php programming concepts.
• Establish database connection.

Difficulty

There is no difficulty in this tutorial. It is easy to learn

• Intermediate

Tutorial Contents

Actually, In This Tutorial, php CRUD Operation ( 2 layer Architecture ) that means php CRUD Operation Performs on App Layer and Service Layer .

In App Layer - Create, Delete, Update, Retrieve,Detail php files are included .

In Service Layer- data_access,person_service, .htaccess files are included.

Here See the person_db structure given below-

In Create php file , we can easily create a person Information and store on the database.

Let's see the example -

<?php include "../service/person_service.php"; ?>
<hr/>
<a href="retrieve.php">HOME</a>
<hr/>
<?php
    if($_SERVER['REQUEST_METHOD']=="POST"){
        $person['name']=$_POST['name'];
        $person['email']=$_POST['email'];
        
        if(addPerson($person)==true){
            echo "Record Added<hr/>";
            die();
        }
    }
?>
<fieldset>
    <legend>CREATE</legend>
    <form method="post">
        <table border="0" cellspacing="0" cellpadding="3">
            <tr>
                <td>NAME:</td>
                <td>
                    <input name="name" />
                </td>
            </tr>
            <tr>
                <td>EMAIL:</td>
                <td>
                    <input name="email" />
                </td>
            </tr> 
        </table>
        <hr/>
        <input type="submit" value="SAVE" />
    </form>
</fieldset>

After Running the program, we get the following results-

If We Click the Save Button , We can get the following results-

In Update php file , we can easily Update a person Information on the database.

Let's See the Example -

<?php include "../service/person_service.php"; ?>
<hr/>
<a href="retrieve.php">HOME</a>
<hr/>
<?php
    if($_SERVER['REQUEST_METHOD']=="POST"){
        $person['id']=$_POST['id'];
        $person['name']=$_POST['name'];
        $person['email']=$_POST['email'];
        
        if(editPerson($person)==true){
            echo "Record Updated<hr/>";
        }
    }
    
    $personId = $_REQUEST['id'];
    $person = getPersonById($personId);
?>
<fieldset>
    <legend>UPDATE</legend>
    <form method="post">
        <input type="hidden" name="id" value="<?= $person['id'] ?>" />
        <table border="0" cellspacing="0" cellpadding="3">
            <tr>
                <td>NAME:</td>
                <td>
                    <input name="name" value="<?= $person['name'] ?>" />
                </td>
            </tr>
            <tr>
                <td>EMAIL:</td>
                <td>
                    <input name="email" value="<?= $person['email'] ?>" />
                </td>
            </tr> 
        </table>
        <hr/>
        <input type="submit" value="SAVE" />
    </form>
</fieldset>

After running the program, We get the following results-

If We Click the Save Button , It Will be updated on the database-

In Delete php file , we can easily Delete a person Information from the database.

Let's See the Example -

<?php include "../service/person_service.php"; ?>
<?php
  $personId =$_GET['id'];
?>
<hr/>
<a href="retrieve.php">HOME</a>
<hr/>
<fieldset>
  <legend>DELETE</legend>
  <?php
      if(removePerson($personId)==true){
          echo "Record Deleted";
      }
  ?>
</fieldset>

After running the following program- We can delete Name : Bob Kent Information from the database :

There is no name Bob Kent on the database-

In Detail php file , we can easily get the details of a person Information from the database.

Let's See the Example -

<?php include "../service/person_service.php"; ?>
<?php
    $personId =$_GET['id'];
    $person = getPersonById($personId);
?>

<hr/>
<a href="retrieve.php">HOME</a>
<a href="update.php?id=<?= $person['id'] ?>">EDIT</a>
<a href="delete.php?id=<?= $person['id'] ?>">DELETE</a>
<hr/>
<fieldset>
    <legend>DETAIL</legend>
    <table border="0" cellspacing="0" cellpadding="3">
        <tr>
            <td>NAME:</td>
            <td><?= $person['name'] ?></td>
        </tr>
        <tr>
            <td>EMAIL:</td>
            <td><?= $person['email'] ?></td>
        </tr> 
    </table>
</fieldset>

After running the program, we get the following reults of the Name : mcplexer details from the database-

In Retrieve php file , we can easily get the person_db table Information from the database through the search.

Let's See the Example -

<?php include "../service/person_service.php"; ?>
<?php
    if ($_SERVER['REQUEST_METHOD'] == "POST") {
        $searchKey = $_POST['search'];
        $persons = getPersonsByName($searchKey);
    } else {
        $persons = getAllPersons();
    }
?>
<html>
    <head>
        <title></title>
    </head>
    <body>
        <hr/>
        <a href="create.php">NEW</a>
        <hr/>
        
        <fieldset>
            <legend>RETRIEVE</legend>
            
            <form method="post">                
                <input name="search"/>
                <input type="submit" value="SEARCH"/>                
            </form>

            <table border="1" cellspacing="0" cellpadding="5">
                <?php if (count($persons) == 0) { ?>
                    <tr>
                        <td>NO RECORD FOUND</td>
                    </tr>
                <?php } ?>

                <?php foreach ($persons as $person) { ?>
                    <tr>
                        <td><?= $person['name'] ?></td>
                        <td><a href="detail.php?id=<?= $person['id'] ?>">detail</a></td>
                        <td><a href="update.php?id=<?= $person['id'] ?>">edit</a></td>
                        <td><a href="delete.php?id=<?= $person['id'] ?>">delete</a></td>
                    </tr>
                <?php } ?>
            </table>
        </fieldset>
    </body>
</html>

After running the following program- we search specific person from the database and we can see the detail of the person , Also Update and Delete their Information.

In Service Layer , We see the following codes-

Database Connection Code :

<?php
    $host="127.0.0.1";
    $user="root";
    $pass="";
    $dbname="person_db";
    $port=3306;
   
    function executeSQL($sql){
        global $host, $user, $pass, $dbname, $port;
        
        $link=mysqli_connect($host, $user, $pass, $dbname, $port);
        $result = mysqli_query($link, $sql);
        mysqli_close($link);
        
        return $result;
    }
?>

Person Service code with person_db connection :

<?php include("data_access.php"); ?>
<?php
    function addPerson($person){
        $sql = "INSERT INTO person(id, name, email) VALUES(NULL, '$person[name]', '$person[email]')";
        $result = executeSQL($sql);
        return $result;
    }
    
    function editPerson($person){
        $sql = "UPDATE person SET name='$person[name]', email='$person[email]' WHERE id=$person[id]";
        $result = executeSQL($sql);
        return $result;
    }
    
    function removePerson($personId){
        $sql = "DELETE FROM person WHERE id=$personId";        
        $result = executeSQL($sql);
        return $result;
    }
    
    function getAllPersons(){
        $sql = "SELECT * FROM person";        
        $result = executeSQL($sql);
        
        $person = array();
        for($i=0; $row=mysqli_fetch_assoc($result); ++$i){
            $person[$i] = $row;
        }
        
        return $person;
    }
    
    function getPersonById($personId){
        $sql = "SELECT * FROM person WHERE id=$personId";        
        $result = executeSQL($sql);
        
        $person = mysqli_fetch_assoc($result);
        
        return $person;
    }
    
    function getPersonsByName($personName){
        $sql = "SELECT * FROM person WHERE name LIKE '%$personName%'";
        $result = executeSQL($sql);
        
        $person = array();
        for($i=0; $row=mysqli_fetch_assoc($result); ++$i){
            $person[$i] = $row;
        }
        
        return $person;
    }
?>

In The Last part , Index.php - we can run the whole program through this code :

<?php
    header("location: app/retrieve.php");    
?>

---

Posted on Utopian.io - Rewarding Open Source Contributors

---