Saturday, 21 January 2017

PHP PDO Ajax CRUD with Data Tables and Bootstrap Modals





Hi, This is one more post on CRUD system in PHP. But in this tutorial We have made CRUD system by using Jquery Datatables plugin and Bootstrap Modals as front end tools and as back end script we have use PHP PDO Modal with Jquery and Ajax. In this post we have describe how to use Jquery Datatables plugin for Server-Side processing with PHP and Ajax. We have already made different CRUD Operation System in PHP. Here is some post in which we have already CRUD system by using PHP. You can also check demo online. We have also provide demo link with this post.

We have already make CRUD Operation system in Codeigniter Framework also which you can find from above link. In this system User can Create or Insert new Data with Upload Image, Read or Fetch Data from table, Update or Edit data and Delete or Remove data. So in this System user can perform all CRUD Operation on single page without going to another page. So we can also called this Crud system to Single page CRUD System also.


In our previous post we have already make CRUD system by using PHP PDO modal with Ajax JQuery. In that system for display data we have mainualy create table and display data in that table but in this system we have use Jquery Datatables plugin for displaying data on web page. In this system we have also add one more feature like user can also upload image with Inserting or updating of data. In this system we have use Ajax Jquery method with PHP PDO modal, So we can perform all CRUD Operation without refreshing of page and we can also upload image without refreshing of page also. For sending form data to server we have use Ajax FormData() Object, By using FormData() Object we have send form data to server via Ajax request.

For making this system we have use JQuery Datatables plugin so we do want to write code for Searching table data, table column ordering, pagination and many more other features. JQuery Datatables plugin will automatically write code for this type of operation. We have use JQuery Datatables Server-Side Ajax processing for Insert Update Delete and Select of Data. Jquery Datatables plugin is light weight plugin for table grid system. So we have use this plugin in our system. I hope this tutorial will help you to learning this system. If you have any query please comment your query in comment box.












Source Code


index.php


    
<html>
 <head>
  <title>Webslesson Demo - PHP PDO Ajax CRUD with Data Tables and Bootstrap Modals</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  <style>
   body
   {
    margin:0;
    padding:0;
    background-color:#f1f1f1;
   }
   .box
   {
    width:1270px;
    padding:20px;
    background-color:#fff;
    border:1px solid #ccc;
    border-radius:5px;
    margin-top:25px;
   }
  </style>
 </head>
 <body>
  <div class="container box">
   <h1 align="center">PHP PDO Ajax CRUD with Data Tables and Bootstrap Modals</h1>
   <br />
   <div class="table-responsive">
    <br />
    <div align="right">
     <button type="button" id="add_button" data-toggle="modal" data-target="#userModal" class="btn btn-info btn-lg">Add</button>
    </div>
    <br /><br />
    <table id="user_data" class="table table-bordered table-striped">
     <thead>
      <tr>
       <th width="10%">Image</th>
       <th width="35%">First Name</th>
       <th width="35%">Last Name</th>
       <th width="10%">Edit</th>
       <th width="10%">Delete</th>
      </tr>
     </thead>
    </table>
    
   </div>
  </div>
 </body>
</html>

<div id="userModal" class="modal fade">
 <div class="modal-dialog">
  <form method="post" id="user_form" enctype="multipart/form-data">
   <div class="modal-content">
    <div class="modal-header">
     <button type="button" class="close" data-dismiss="modal">&times;</button>
     <h4 class="modal-title">Add User</h4>
    </div>
    <div class="modal-body">
     <label>Enter First Name</label>
     <input type="text" name="first_name" id="first_name" class="form-control" />
     <br />
     <label>Enter Last Name</label>
     <input type="text" name="last_name" id="last_name" class="form-control" />
     <br />
     <label>Select User Image</label>
     <input type="file" name="user_image" id="user_image" />
     <span id="user_uploaded_image"></span>
    </div>
    <div class="modal-footer">
     <input type="hidden" name="user_id" id="user_id" />
     <input type="hidden" name="operation" id="operation" />
     <input type="submit" name="action" id="action" class="btn btn-success" value="Add" />
     <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
    </div>
   </div>
  </form>
 </div>
</div>

<script type="text/javascript" language="javascript" >
$(document).ready(function(){
 $('#add_button').click(function(){
  $('#user_form')[0].reset();
  $('.modal-title').text("Add User");
  $('#action').val("Add");
  $('#operation').val("Add");
  $('#user_uploaded_image').html('');
 });
 
 var dataTable = $('#user_data').DataTable({
  "processing":true,
  "serverSide":true,
  "order":[],
  "ajax":{
   url:"fetch.php",
   type:"POST"
  },
  "columnDefs":[
   {
    "targets":[0, 3, 4],
    "orderable":false,
   },
  ],

 });

 $(document).on('submit', '#user_form', function(event){
  event.preventDefault();
  var firstName = $('#first_name').val();
  var lastName = $('#last_name').val();
  var extension = $('#user_image').val().split('.').pop().toLowerCase();
  if(extension != '')
  {
   if(jQuery.inArray(extension, ['gif','png','jpg','jpeg']) == -1)
   {
    alert("Invalid Image File");
    $('#user_image').val('');
    return false;
   }
  } 
  if(firstName != '' && lastName != '')
  {
   $.ajax({
    url:"insert.php",
    method:'POST',
    data:new FormData(this),
    contentType:false,
    processData:false,
    success:function(data)
    {
     alert(data);
     $('#user_form')[0].reset();
     $('#userModal').modal('hide');
     dataTable.ajax.reload();
    }
   });
  }
  else
  {
   alert("Both Fields are Required");
  }
 });
 
 $(document).on('click', '.update', function(){
  var user_id = $(this).attr("id");
  $.ajax({
   url:"fetch_single.php",
   method:"POST",
   data:{user_id:user_id},
   dataType:"json",
   success:function(data)
   {
    $('#userModal').modal('show');
    $('#first_name').val(data.first_name);
    $('#last_name').val(data.last_name);
    $('.modal-title').text("Edit User");
    $('#user_id').val(user_id);
    $('#user_uploaded_image').html(data.user_image);
    $('#action').val("Edit");
    $('#operation').val("Edit");
   }
  })
 });
 
 $(document).on('click', '.delete', function(){
  var user_id = $(this).attr("id");
  if(confirm("Are you sure you want to delete this?"))
  {
   $.ajax({
    url:"delete.php",
    method:"POST",
    data:{user_id:user_id},
    success:function(data)
    {
     alert(data);
     dataTable.ajax.reload();
    }
   });
  }
  else
  {
   return false; 
  }
 });
 
 
});
</script>
   

db.php


    
<?php

$username = 'root';
$password = '';
$connection = new PDO( 'mysql:host=localhost;dbname=crud', $username, $password );

?>
   

function.php


    
<?php

function upload_image()
{
 if(isset($_FILES["user_image"]))
 {
  $extension = explode('.', $_FILES['user_image']['name']);
  $new_name = rand() . '.' . $extension[1];
  $destination = './upload/' . $new_name;
  move_uploaded_file($_FILES['user_image']['tmp_name'], $destination);
  return $new_name;
 }
}

function get_image_name($user_id)
{
 include('db.php');
 $statement = $connection->prepare("SELECT image FROM users WHERE id = '$user_id'");
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  return $row["image"];
 }
}

function get_total_all_records()
{
 include('db.php');
 $statement = $connection->prepare("SELECT * FROM users");
 $statement->execute();
 $result = $statement->fetchAll();
 return $statement->rowCount();
}

?>
   

fetch.php


    
<?php
include('db.php');
include('function.php');
$query = '';
$output = array();
$query .= "SELECT * FROM users ";
if(isset($_POST["search"]["value"]))
{
 $query .= 'WHERE first_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR last_name LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY id DESC ';
}
if($_POST["length"] != -1)
{
 $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
 $image = '';
 if($row["image"] != '')
 {
  $image = '<img src="upload/'.$row["image"].'" class="img-thumbnail" width="50" height="35" />';
 }
 else
 {
  $image = '';
 }
 $sub_array = array();
 $sub_array[] = $image;
 $sub_array[] = $row["first_name"];
 $sub_array[] = $row["last_name"];
 $sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button>';
 $sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">Delete</button>';
 $data[] = $sub_array;
}
$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  $filtered_rows,
 "recordsFiltered" => get_total_all_records(),
 "data"    => $data
);
echo json_encode($output);
?>
   

insert.php


    
<?php
include('db.php');
include('function.php');
if(isset($_POST["operation"]))
{
 if($_POST["operation"] == "Add")
 {
  $image = '';
  if($_FILES["user_image"]["name"] != '')
  {
   $image = upload_image();
  }
  $statement = $connection->prepare("
   INSERT INTO users (first_name, last_name, image) 
   VALUES (:first_name, :last_name, :image)
  ");
  $result = $statement->execute(
   array(
    ':first_name' => $_POST["first_name"],
    ':last_name' => $_POST["last_name"],
    ':image'  => $image
   )
  );
  if(!empty($result))
  {
   echo 'Data Inserted';
  }
 }
 if($_POST["operation"] == "Edit")
 {
  $image = '';
  if($_FILES["user_image"]["name"] != '')
  {
   $image = upload_image();
  }
  else
  {
   $image = $_POST["hidden_user_image"];
  }
  $statement = $connection->prepare(
   "UPDATE users 
   SET first_name = :first_name, last_name = :last_name, image = :image  
   WHERE id = :id
   "
  );
  $result = $statement->execute(
   array(
    ':first_name' => $_POST["first_name"],
    ':last_name' => $_POST["last_name"],
    ':image'  => $image,
    ':id'   => $_POST["user_id"]
   )
  );
  if(!empty($result))
  {
   echo 'Data Updated';
  }
 }
}

?>
   

fetch_single.php


    
<?php
include('db.php');
include('function.php');
if(isset($_POST["user_id"]))
{
 $output = array();
 $statement = $connection->prepare(
  "SELECT * FROM users 
  WHERE id = '".$_POST["user_id"]."' 
  LIMIT 1"
 );
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  $output["first_name"] = $row["first_name"];
  $output["last_name"] = $row["last_name"];
  if($row["image"] != '')
  {
   $output['user_image'] = '<img src="upload/'.$row["image"].'" class="img-thumbnail" width="50" height="35" /><input type="hidden" name="hidden_user_image" value="'.$row["image"].'" />';
  }
  else
  {
   $output['user_image'] = '<input type="hidden" name="hidden_user_image" value="" />';
  }
 }
 echo json_encode($output);
}
?>
   

delete.php


    
<?php

include('db.php');
include("function.php");

if(isset($_POST["user_id"]))
{
 $image = get_image_name($_POST["user_id"]);
 if($image != '')
 {
  unlink("upload/" . $image);
 }
 $statement = $connection->prepare(
  "DELETE FROM users WHERE id = :id"
 );
 $result = $statement->execute(
  array(
   ':id' => $_POST["user_id"]
  )
 );
 
 if(!empty($result))
 {
  echo 'Data Deleted';
 }
}



?>
   

Database


    
--
-- Database: `crud`
--

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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(150) NOT NULL,
  `last_name` varchar(150) NOT NULL,
  `image` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=74 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `first_name`, `last_name`, `image`) VALUES
(18, 'Joseph', 'Harman', '1.jpg'),
(19, 'John', 'Moss', '4.jpg'),
(20, 'Lillie', 'Ferrarium', '3.jpg'),
(21, 'Yolanda', 'Green', '5.jpg'),
(22, 'Cara', 'Gariepy', '7.jpg'),
(23, 'Christine', 'Johnson', '11.jpg'),
(24, 'Alana', 'Decruze', '12.jpg'),
(25, 'Krista', 'Correa', '13.jpg'),
(26, 'Charles', 'Martin', '14.jpg'),
(70, 'Cindy', 'Canady', '18211.jpg'),
(73, 'Daphne', 'Frost', '8288.jpg'),
(69, 'Frank', 'Lemons', '22610.jpg'),
(66, 'Margaret', 'Ault', '14365.jpg'),
(71, 'Christina', 'Wilke', '9248.jpg'),
(68, 'Roy', 'Newton', '27282.jpg');
   





79 comments:

  1. everyting seems to work but I can't see the images or when I create a user and specify an image to upload it creates a new user but still can't see image of the user?!?

    ReplyDelete
  2. sorry I can't see the images when I add a new user ... is there a php file, folder or database that needs to be set up to see the images? Thank you.

    ReplyDelete
  3. can i get the mysqli version of this tutorial

    ReplyDelete
  4. try to add some security check for file upload.

    ReplyDelete
  5. where clause in query not working in fetch.php

    ReplyDelete
  6. Hi, this good, but when i update the photo, old photo in the upload file, no remve it, how can auto remove it? thanks.

    ReplyDelete
  7. Nice tutorial, but how can I post three select option to filter data from mysql using datatables and php. Thanks

    ReplyDelete
  8. Thanks for nice tutorial. but I am facing problem in inserting data to database.
    Please resolve.

    ReplyDelete
  9. i have been reading all of the instructions but i can't make it work with my own sql database ???

    ReplyDelete
  10. the code is not working......... if you make a complete similar mysql db. and copy the code...

    ReplyDelete
  11. this is a great tutorial. This is one of the bests site i have visited since I started programming. Thanks to you

    ReplyDelete
  12. Hola amigo que tal, he estado trabajando con tu código, muy bueno por cierto. Pero si quisiera trabajar la impresión y salida a pdf con el datatable como lo harias? he intentado guiarme de las soluciones de la página de datatable.net pero cuando inserto el codigo y los enlaces css, no consigo resultado. Espero tu pronta respuesta. Saludos.

    ReplyDelete
  13. Hello,
    Thanks for sharing wonderful code for my need and I believe that thousands of bigners like me get help from your code, really great,

    I have one problem, or I can say I need to modify fetch.php, I need your help for that, In fetch.php i need to display particular users, like I added filed res_id if data was added to database, its goes under res_id, and res_id are different and belongs to loged in user, I create user with ID's and when user login with password and username, it stores ID of user in variable later on I want to use tis variable to display json data as coded in fectch.php I tried many different things with $query but can't get desired results. I need you help.

    ReplyDelete
  14. You are great, I'll be your tagging behind your tutorials and posts

    ReplyDelete
  15. I will be tagging behind your tutorials and posts, great to you guys, god bless you and keep doing what you're doing

    ReplyDelete
  16. how can add some other fields as well? trying to add more but did'nt worked

    ReplyDelete
  17. teach show details by clicking on the table row, please

    ReplyDelete
  18. Porque me manda error de JSON, tengo 28 registros cargados, y al filtrar 25 o 100 me manda ese error o al buscar

    ReplyDelete
  19. Me marca error cuando filtro a 25 registros :(

    ReplyDelete
  20. Hola, mi duda es como cambio el "SHOWING" o "SHOW 10 ENTRIES" a ESPAÑOL....

    Se puede??

    ReplyDelete
  21. Hello, when i run this its shown as no data available, do you know why?

    ReplyDelete
  22. Beautiful, thanks a lot for this...

    ReplyDelete
  23. Awesome..sir
    You make my day...
    Thank you.

    ReplyDelete
  24. Hello Sir,

    when we tried to delete single image, its delete all the images.. pls help

    ReplyDelete
  25. Hi guy,
    Can you reproduce this tutorial using laravel 5.5

    ReplyDelete
  26. We can't see the image into this demo page

    ReplyDelete
  27. What is the problem if my data is not showing into dataTables?

    ReplyDelete
  28. muito Obrigado por tuas aulas, ajudou bastante

    ReplyDelete
  29. How Can I manage more images?

    ReplyDelete
  30. How Can I manage more images?

    ReplyDelete
  31. Hallo Could u add viw image on this tutorial I need it

    ReplyDelete
  32. I have implemented this and its working perfectly. However I need to make some changes in the fetch.php file but it is not allowing me to do so. For instance if I need to filter the main query to do something like this.
    query .='select * from table where field like"somevalue" '; the filter doesnt work. Also, if i want to enable print and pdf in the datatable js code it does not appear on the table.

    Please can you help me with those. Thank you very much

    ReplyDelete
  33. Hagale con bootstrap 4 porfa para ver como cambia la interfaz

    ReplyDelete
  34. hello,

    Column Sorting Sorts Wrong Columns

    how to fix ?

    ReplyDelete
  35. Thanks for sharing this tutorial...It,s very helpful

    ReplyDelete
  36. Very nice work, with a small note is the need to delete the image modified after it will become unused.

    ReplyDelete
  37. Hello.Nice Tutorial. Could u give me a hand? I need to create a select field to display values in another table. How can i do this in that CRUD code? Thanks.

    ReplyDelete
  38. its really admirable post. thaaaanks. But
    how to sort int value or numbers 1, 25, 52, 3, 20 etc ??? please help

    ReplyDelete
  39. I love what your share and tested it well, but I have problem when I use text area with ckeditor….when button update clicked, data in text area display nothing...I hope you will help me to solve this......

    ReplyDelete
  40. Hello,
    great job that will help me a lot, there is just a problem with the sorting, you have to click on the conne "Last Name" to have the good alphabetical order in the column "first Name"
    How to correct this problem?

    Greetings

    ReplyDelete
    Replies
    1. Same problem here. I cant sorting correctly. What could it be?

      Delete
  41. This comment has been removed by the author.

    ReplyDelete
  42. Hello,
    Thanks for tutorial, I am tested my server.

    I have problem fetch.php

    this line if($_POST["length"] != -1 )

    qyery string syntax error for example query below

    SELECT * FROM user ORDER BY id DESC LIMIT ,


    Thank you for helping.

    ReplyDelete
    Replies
    1. Hai,

      I also have the same problem and also there is some error with this

      $query .= 'LIMIT ' . $_POST["start"] . ', ' . $_POST["length"]; and

      "draw" => intval($_POST["draw"])

      it shows invalid index for length, start and draw

      Also there is data in my database but it shows no data is found but the records filtered is getting the data and i don't understand why

      Please help

      Delete
    2. Add space in each query like

      $query .= ' LIMIT ' . $_POST["start"] . ', ' . $_POST["length"];

      Delete
  43. I get this error:

    DataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7

    ReplyDelete
  44. Literally i like your website from heart and hope that keep on sharing this kind of stuff in the future also.

    ReplyDelete
  45. Hi, i have tried this and the crud worked well thanks a lot. The only problems is with the sorting, you need to click other columns to make the right ordering for other columns and it gets pretty weird. Do you have a fix for it? I'd appreciate it if you can help me

    ReplyDelete
    Replies
    1. About problem with sorting...
      Put the columns in the right order in the sql statement.
      E.g If you want to be able to sort three columns, put those three columns first in the statement. Worked for me

      Delete
  46. I'm also getting this error. All your datatables code have DataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7

    ReplyDelete

  47. Hello,
    The only problems is with the sorting, you need to click other columns to make the right ordering for other columns and it gets pretty weird. Do you have a fix for it? I'd appreciate it if you can help me

    ReplyDelete
  48. Just a small issue i am facing, the column sort icons or images are missing with blank square shape, when i tried to run the script locally.

    Please advise, where to download this sort icons from and I guess i can store locally on root folder under images.

    ReplyDelete
    Replies
    1. https://github.com/DataTables/DataTables/tree/master/media/images

      Delete
  49. i user where condition and also search
    then record not proper given
    how to solve

    $query .= "SELECT * FROM employee ";

    if(!empty($_POST["search"]["value"]))
    {
    $query .= 'WHERE employee_name = "ram" ';

    $query .= 'AND employee_city LIKE "%'.$_POST["search"]["value"].'%" ';
    $query .= 'OR employee_salary LIKE "%'.$_POST["search"]["value"].'%" ';

    }

    ReplyDelete
  50. soved
    where condition after search record

    $query .= 'WHERE ';

    $query .= '(employee_name = "ram" AND employee_city LIKE "%'.$_POST["search"]["value"].'%") ';

    $query .= 'OR (employee_name = "ram" AND employee_salary LIKE "%'.$_POST["search"]["value"].'%") ';

    $query .= 'OR (employee_name = "ram" AND employee_name LIKE "%'.$_POST["search"]["value"].'%") ';

    ReplyDelete
  51. Seem problem:
    DataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7

    ReplyDelete
  52. Good Example to my project.. thanks a lot..

    ReplyDelete
  53. Hi.
    I am trying to update the code to latest bootstrap, jquery, datatables but I get errors.
    Can you please update the code?

    ReplyDelete
  54. Hi,
    I am getting an error
    DataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7

    ReplyDelete
  55. it showing error ""DataTables warning: table id={id} - Invalid JSON response.
    please help me

    ReplyDelete
  56. thank you so much this will help me for my project in school.

    ReplyDelete
  57. I want to make fancybox plugin for pictures.
    Could you help

    ReplyDelete
  58. Hi, please, How Can I increase the numbers of records to show, in the code you have 10, 25 and 100, how Can I change that to for example: 50, 100 and 200?

    ReplyDelete
  59. hello sir can i use it as free. and one more question how to count rows?

    ReplyDelete
  60. Good afternoon, I really liked this example, but it doesn't show the database records.
    Shows this message:
    DataTables warning: table id = user_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

    I made the modification for my database and I can't list the records because of this error.
    Can you tell me what the error might be?

    ReplyDelete
  61. Hello, I added the Bootstrap 4.5 navbar:
    nav class = navbar navbar-expand-md navbar-dark fixed-top bg-dark
    Using css: bootstrap.min.css

    The formatting of the responsive table, is all unconfigured with the div below:
    div class = table-responsive
    table id = user_data class = table table-striped table-bordered

    I'm looking to use this Navbar from this example:
    getbootstrap com docs 4.5 examples jumbotron

    How to use and not to unconfigure?

    ReplyDelete
  62. Hi,

    It does not work for me! I get the same error when working with datatables.net. Any solution would be appreciated. This the error: DataTables warning table id=user_data- Invalid JSON response. For mor information see- http:..datatables.net/tn/1

    Thanks!

    ReplyDelete
  63. anyone has figure out why update button won't show modal?

    ReplyDelete