Crud Operations In PHP Using Mysqli

By : PHPErrorCode June 30, 2017 1298 Views php

we are going to create CRUD (Read, Right , Update and Delete) operations using PHP and MySQL, CRUD operations are basic data manipulation for database.

If anyone of you ever worked with database, then you probably familiar with CRUD Operations.

Before starting, lets clear about What is CRUD? CRUD is acronym for "Create Read Update Delete". It is database-oriented primafunctions. CRUD also said as user interface that provides creating records, Viewing Records, Updating or Modifing Records and Deleting Records.

Since CRUD Operations are basic requirement in any programming language, it also required in PHP for creating forms,tables and reports as well as other operations.So in this post we will create form and then create function. You can copy and paste this code in your project and use CRUD operations in any form.

Stap 1:  First Create a Database :
  1. Open phpmyadmin
  2. Create database   as "tutorial"
  3. Click create table and name it as "user"
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `user_name` varchar(25) NOT NULL,
  `email` varchar(50) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Step 2: Front end code, Make HTML Structure of your insert update delete form for index.php

The following will be insert update and delete from the html page 

<!DOCTYPE html>
<html lang="en">
<head>
  <title>CRUD Tutorial</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <script src="custom.js"></script>
</head>
<body>
<div class="container">
  <div class="well">
    <?php if(isset($_SESSION['msg'])){ ?>
    <h2 class="text-center"><?php echo $_SESSION['msg']; ?></h2>
    <?php session_unset(); } ?>
    <h2 class="text-center h_title"><p class="p_title">Add User data<p></h2>
    <form class="form-horizontal form_data" action="add_user.php" method="post">
      <div class="form-group">
        <label class="control-label col-sm-2" for="email">name:</label>
        <div class="col-sm-10">
          <input type="text" name="name" class="form-control name" 
            placeholder="Enter name" required="">
        </div>
      </div>
      <div class="form-group">
        <label class="control-label col-sm-2" for="email">Email:</label>
        <div class="col-sm-10">
          <input type="email" name="email" class="form-control email" 
            placeholder="Enter email" required="">
        </div>
      </div>
      <div class="form-group">        
        <div class="col-sm-offset-2 col-sm-10">
          <button type="submit" name="submit_btn" value="Submit" 
            class="btn btn-default">Submit</button>
        </div>
      </div>
    </form>
  </div>
  <div class="well">
    <?php if(isset($_SESSION['massage'])){ ?>
    <h2><?php echo $_SESSION['massage']; ?></h2>
    <?php session_unset(); } ?>
    <p><h2>View User data</h2></p>
    <table class="table table-bordered">
      <thead>
        <tr>
          <th>name</th>
          <th>email</th>
          <th>Action</th>
        </tr>
      </thead>
      <tbody>
        <?php
          include('function.php');
          $result = $conn->query("SELECT * FROM user");
          if(!$result->num_rows > 0){ echo 'No Data Faund'; }
          while($row = $result->fetch_assoc())
          {
        ?>
        <tr>
          <td><?php echo $row['user_name']; ?></td>
          <td><?php echo $row['email']; ?></td>
          <td>
            <a class="btn btn-primary update_data" 
              data-id="<?php echo $row['id']; ?>" 
              data-name="<?php echo $row['user_name']; ?>" 
              data-email="<?php echo $row['email']; ?>"> 
              <i class="fa fa-pencil"></i>Edit
            </a>
            <a href="delete_user.php?user_id=<?php echo $row['id']; ?>" 
              class="btn btn-danger">
              <i class="fa fa-times"></i>Delete
            </a>
          </td>
        </tr>
        <?php } ?>
      </tbody>
    </table>
  </div>
</div>
</body>
</html>
Step 3:  Create A New File custom.js And Place The js Below Into It.
$(document).ready(function(){
    $(".update_data").click(function(){
        var user_id = $(this).attr('data-id');
        var user_name = $(this).attr('data-name');
        var user_email = $(this).attr('data-email');

        $('.p_title').remove();
        $('.form_data').append('<input type="hidden" name="id" value="'+user_id+'"%gt;');
        $('.h_title').append('<p class="p_title"%gt;Edit User data<p%gt;');
        $('.form_data').attr('action','update_user_data.php');
        $('.name').attr('value',user_name);
        $('.email').attr('value',user_email);
    });
});
Step 4:  Create A New File config.php And Place The Code Below Into It.
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "tutorial";

  // Create connection
  $conn = new mysqli($servername, $username, $password, $dbname);

  // Check connection
  if ($conn->connect_error) 
  {
      die("Connection failed: " . $conn->connect_error);
  }

Step 5: Create A New File function.Php And Place The dbRowInsert() dbRowUpdate and dbRowDelete function Below Into It.
function dbRowInsert($table_name, $data)
{
    require_once('config.php');
    // retrieve the keys of the array (column titles)
    $fields = array_keys($data);

    // build the query
    $sql = "INSERT INTO ".$table_name."(`".implode('`,`', $fields)."`)VALUES('".implode("','", $data)."')";
    
    // run and return the query result resource
    return $conn->query($sql);
}

function dbRowUpdate($table_name, $form_data, $where_clause='')
{
    require_once('config.php');
    // check for optional where clause
    $whereSQL = '';
    if(!empty($where_clause))
    {
        // check to see if the 'where' keyword exists
        if(substr(strtoupper(trim($where_clause)), 0, 5) != 'WHERE')
        {
            // not found, add key word
            $whereSQL = " WHERE ".$where_clause;
        } 
        else
        {
            $whereSQL = " ".trim($where_clause);
        }
    }
    // start the actual SQL statement
    $sql = "UPDATE ".$table_name." SET ";

    // loop and build the column /
    $sets = array();
    foreach($form_data as $column => $value)
    {
        $sets[] = "`".$column."` = '".$value."'";
    }
    $sql .= implode(', ', $sets);

    // append the where statement
    $sql .= $whereSQL;

    // run and return the query result
    return $conn->query($sql);
}

function dbRowDelete($table_name, $where_clause='')
{
    require_once('config.php');

    // check for optional where clause
    $whereSQL = '';
    if(!empty($where_clause))
    {
        // check to see if the 'where' keyword exists
        if(substr(strtoupper(trim($where_clause)), 0, 5) != 'WHERE')
        {
            // not found, add keyword
           $whereSQL = " WHERE ".$where_clause;
        } 
        else
        {
            $whereSQL = " ".trim($where_clause);
        }
    }
    // build the query
    $sql = "DELETE FROM ".$table_name.$whereSQL;
    // run and return the query result resource
    return $conn->query($sql);
}
Step 6: Create A New insert row from MySQL Table

Create A New File add_user.php And Place The Code Below Into It.

require_once 'function.php';
session_start();
if(isset($_POST['submit_btn']))
{
    //server side validation
    if($_POST['name'] =='' || $_POST['email']=='')
    {
        $_SESSION['msg']="Please Enter required field ....";
        header("Location:index.php");
        exit;
     }

     $data = array(
                      'user_name' => $_POST['name'],
                      'email' => $_POST['email'],
                  );

    //The following functions will be called as the data will be inserted
    dbRowInsert('user', $data);
    $_SESSION['msg']="Add record success....";
    header("Location:index.php");
}
Step 7: Update row from MySQL Table

Create A New File update_user_data.php And Place The Code Below Into It.

require_once 'function.php';
session_start();
if(isset($_POST['submit_btn']))
{
    //server side validation
    if($_POST['name'] =='' || $_POST['email']=='')
    {
        $_SESSION['massage']="Please Enter required field ....";
        header("Location:index.php");
        exit;
    }

    $data = array(
                    'user_name' => $_POST['name'],
                    'email' => $_POST['email'],
                 );
    $id=$_POST['id'];

    //The following functions will be called as the data will be inserted
    dbRowUpdate('user', $data, "WHERE `id` = '$id'");
    $_SESSION['massage']="Update record success....";
    header("Location:index.php");
}
Step 8: Delete Row from MySQL Table

Create A New File delete_user.php And Place The Code Below Into It.

require_once 'function.php';
    session_start(); 
    $id=$_GET['user_id'];
    dbRowDelete('user', "WHERE id = '$id'");
    $_SESSION['massage']="Deleet record success....";
    header("Location:index.php");