Thursday 16 August 2018

Live Table Add Edit Delete using PHP with jsGrid Plugin



Live Inline CRUD operation of Create, Read, Update and Delete within a area of grid view. Live inline CRUD will add feature like adding new data and make changes in existing data dynamically using jquery with Ajax. Now here is one question how can we perform this all things, for this here we have use jsGrid jquery plugin which is lightweight jQuery grid plugin in which we can perform all CRUD operation like insert update delete and Read data using Ajax with PHP and Mysql database.

In our some previous tutorial we have discuss this things but in that we have do every things manually and we have converted table data field into editable by using contenteditable attribute and in that tutorial we have only use textbox field only but here by using jsGrid plugin we can also use most of all html input field.

jsGrid is a very popular lightweight jquery grid plugin and it is especially design for add edit delete inline mysql table data using Ajax and it is compatible with all programming language because it has use json data for sending and receiving. So, here we have use this plugin with PHP script and make simple inline insert update delete mysql data using Ajax. This tutorial will help us to add, edit and delete records into grid using PHP and Mysql. This plugin has use Ajax request for add, edit and delete data using PHP with mysql. jsGrid is a simple and very lightweight client side data grid plugin based on jQuery and by using this plugin we can perform all operation like insert new records, filter existing records, edit existing records, delete records, pagination of data and sorting of data. By using this plugin in short code you can perform very big task.

In below step by step source code you can find how can we integrate jsGrid plugin with PHP system and How can we fetch data from Mysql database and load into jsGrid plugin. How can we add or insert new records into Mysql by using jsGrid plugin with PHP. How can we edit existing mysql data by using jsGrid with PHP. How can we delete single mysql records in jsGrid using PHP. And lastly how can we make single page inline table add edit delete PHP application using this jsGrid plugin.











Source Code



--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `sample_data`
--

CREATE TABLE `sample_data` (
  `id` int(10) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `age` int(3) NOT NULL,
  `gender` enum('male','female') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `sample_data`
--

INSERT INTO `sample_data` (`id`, `first_name`, `last_name`, `age`, `gender`) VALUES
(3, 'Tiny', 'Marry', 19, 'female'),
(4, 'Dolores', 'Brooks', 29, 'female'),
(5, 'Cindy', 'Dahl', 24, 'female'),
(6, 'George', 'Fagan', 30, 'male'),
(7, 'Chelsea', 'Mendoza', 18, 'female'),
(8, 'Wayne', 'Hodges', 27, 'male'),
(9, 'Keith', 'Watkin', 26, 'male'),
(10, 'Eric', 'Smith', 31, 'male'),
(11, 'Robert', 'Owens', 42, 'male'),
(12, 'Candace', 'Hand', 27, 'female'),
(13, 'Hortencia', 'Bell', 30, 'female'),
(14, 'William', 'Sosa', 36, 'male'),
(15, 'Patricia', 'Davis', 23, 'female'),
(17, 'Nancy', 'Sedlacek', 21, 'female');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `sample_data`
--
ALTER TABLE `sample_data`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `sample_data`
--
ALTER TABLE `sample_data`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;


index.php



<html>  
    <head>  
        <title>Inline Table Insert Update Delete in PHP using jsGrid</title>  
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link type="text/css" rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.css" />
  <link type="text/css" rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid-theme.min.css" />
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.js"></script>
  <style>
  .hide
  {
     display:none;
  }
  </style>
    </head>  
    <body>  
        <div class="container">  
   <br />
   <div class="table-responsive">  
    <h3 align="center">Inline Table Insert Update Delete in PHP using jsGrid</h3><br />
    <div id="grid_table"></div>
   </div>  
  </div>
    </body>  
</html>  
<script>
 
    $('#grid_table').jsGrid({

     width: "100%",
     height: "600px",

     filtering: true,
     inserting:true,
     editing: true,
     sorting: true,
     paging: true,
     autoload: true,
     pageSize: 10,
     pageButtonCount: 5,
     deleteConfirm: "Do you really want to delete data?",

     controller: {
      loadData: function(filter){
       return $.ajax({
        type: "GET",
        url: "fetch_data.php",
        data: filter
       });
      },
      insertItem: function(item){
       return $.ajax({
        type: "POST",
        url: "fetch_data.php",
        data:item
       });
      },
      updateItem: function(item){
       return $.ajax({
        type: "PUT",
        url: "fetch_data.php",
        data: item
       });
      },
      deleteItem: function(item){
       return $.ajax({
        type: "DELETE",
        url: "fetch_data.php",
        data: item
       });
      },
     },

     fields: [
      {
       name: "id",
    type: "hidden",
    css: 'hide'
      },
      {
       name: "first_name", 
    type: "text", 
    width: 150, 
    validate: "required"
      },
      {
       name: "last_name", 
    type: "text", 
    width: 150, 
    validate: "required"
      },
      {
       name: "age", 
    type: "text", 
    width: 50, 
    validate: function(value)
    {
     if(value > 0)
     {
      return true;
     }
    }
      },
      {
       name: "gender", 
    type: "select", 
    items: [
     { Name: "", Id: '' },
     { Name: "Male", Id: 'male' },
     { Name: "Female", Id: 'female' }
    ], 
    valueField: "Id", 
    textField: "Name", 
    validate: "required"
      },
      {
       type: "control"
      }
     ]

    });

</script>



<?php

//fetch_data.php

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

$method = $_SERVER['REQUEST_METHOD'];

if($method == 'GET')
{
 $data = array(
  ':first_name'   => "%" . $_GET['first_name'] . "%",
  ':last_name'   => "%" . $_GET['last_name'] . "%",
  ':age'     => "%" . $_GET['age'] . "%",
  ':gender'    => "%" . $_GET['gender'] . "%"
 );
 $query = "SELECT * FROM sample_data WHERE first_name LIKE :first_name AND last_name LIKE :last_name AND age LIKE :age AND gender LIKE :gender ORDER BY id DESC";

 $statement = $connect->prepare($query);
 $statement->execute($data);
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  $output[] = array(
   'id'    => $row['id'],   
   'first_name'  => $row['first_name'],
   'last_name'   => $row['last_name'],
   'age'    => $row['age'],
   'gender'   => $row['gender']
  );
 }
 header("Content-Type: application/json");
 echo json_encode($output);
}

if($method == "POST")
{
 $data = array(
  ':first_name'  => $_POST['first_name'],
  ':last_name'  => $_POST["last_name"],
  ':age'    => $_POST["age"],
  ':gender'   => $_POST["gender"]
 );

 $query = "INSERT INTO sample_data (first_name, last_name, age, gender) VALUES (:first_name, :last_name, :age, :gender)";
 $statement = $connect->prepare($query);
 $statement->execute($data);
}

if($method == 'PUT')
{
 parse_str(file_get_contents("php://input"), $_PUT);
 $data = array(
  ':id'   => $_PUT['id'],
  ':first_name' => $_PUT['first_name'],
  ':last_name' => $_PUT['last_name'],
  ':age'   => $_PUT['age'],
  ':gender'  => $_PUT['gender']
 );
 $query = "
 UPDATE sample_data 
 SET first_name = :first_name, 
 last_name = :last_name, 
 age = :age, 
 gender = :gender 
 WHERE id = :id
 ";
 $statement = $connect->prepare($query);
 $statement->execute($data);
}

if($method == "DELETE")
{
 parse_str(file_get_contents("php://input"), $_DELETE);
 $query = "DELETE FROM sample_data WHERE id = '".$_DELETE["id"]."'";
 $statement = $connect->prepare($query);
 $statement->execute();
}

?>

28 comments:

  1. On my localhost, it loads but does not add, what can it be?

    ReplyDelete
  2. sir please upload angularjs project

    ReplyDelete
  3. hi delete function is no working

    ReplyDelete
  4. This is the easiest to follow example I have seen, and it all works well for me. It's great, many thanks.

    ReplyDelete
  5. Hi, I try this and work for text data. But for datepicker it cannot save to mysql. Do you have any solution?

    ReplyDelete
  6. Hello, this is very helpful, however I want to modify it for my use and I would like to use it as a Vehicle, Make and Model picker. For instance the first one will have a series of Makes, eg: Acura, Honda, Toyota. Then upon selecting the first Make, the models for that make are generated eg: After selecting Honda, the Model list would generate Civic, CRV, Pilot .. etc. Can you how me to achieve this?

    ReplyDelete
  7. How to query and output information from two tables? And how to make some of the fields static/ not editable?

    ReplyDelete
  8. Great Job sir. Helped me a lot.

    ReplyDelete
  9. hi, how to integrate with laravel?

    ReplyDelete
  10. hi, how to convert your coding into laravel? especially for fetch_data.php

    ReplyDelete
  11. this is so helpful to me

    but i want to know how to set (id name) and (class name) for html input

    ReplyDelete
  12. values wont show from my db

    ReplyDelete
  13. hello sir, when i update or delete, the data do not cahage in mysql. help me

    ReplyDelete
  14. what is "Content-Type: application/json"

    ReplyDelete
  15. This is great, thanks. Any way to disable the 'delete' option?

    ReplyDelete
  16. The filtering doesn´t work for me

    ReplyDelete
  17. Please tell me if there's any way to use utf8 support for this gird?

    ReplyDelete
  18. Hi to change name of columns?

    ReplyDelete
  19. Hi, I'm try it but controller with url: "fetch_data.php" not working? how to run it?

    ReplyDelete
  20. how to integrate with Codeigniter.

    ReplyDelete
  21. Great Tutorial.. Thank you very much

    ReplyDelete
  22. Thank you. This is a great post in term of explanation and the code works first time !!! Nice.

    ReplyDelete
  23. Thanks its nice simple example. but unfortunately i have not been able to load data or carryout any CRUD function on it. it seems to load the index page but without any data. seems to let me add or search but doesnt add or finds any data.

    ReplyDelete