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.
- PHP PDO CRUD with Ajax Jquery and Bootstrap
- PHP Ajax Crud using OOPS - Select or Fetch Mysql Data
- PHP Ajax Crud using OOPS - Insert or Add Mysql Data
- PHP Ajax Crud using OOPS - Update or Edit Mysql Data
- PHP Mysql Ajax Crud using OOPS - Delete or Remove Data
- PHP Mysql Ajax Crud using OOPS - Live Data Search
- PHP Mysql Ajax Crud using OOPS - Pagination
- DataTables - Server-side Processing in Codeigniter using Ajax
- Codeigniter Ajax Crud using DataTables - Insert / Add Data
- Codeigniter Ajax Crud using DataTables - Update / Edit Data
- Codeigniter Ajax Crud using DataTables - Delete - Remove Mysql Data
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.
See 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">×</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');
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?!?
ReplyDeletesorry 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.
ReplyDeletecan i get the mysqli version of this tutorial
ReplyDeletetry to add some security check for file upload.
ReplyDeletewhere clause in query not working in fetch.php
ReplyDeleteHi, this good, but when i update the photo, old photo in the upload file, no remve it, how can auto remove it? thanks.
ReplyDeleteuse unlink() on the old file. Therefore in insert.php
Deleteif($_POST["operation"] == "Edit")
{
$image = '';
if($_FILES["user_image"]["name"] != '')
{
unlink($_POST["hidden_user_image"]);//here
$image = upload_image();
}
else
{
$image = $_POST["hidden_user_image"];
}
Nice tutorial, but how can I post three select option to filter data from mysql using datatables and php. Thanks
ReplyDeletethank you very much
ReplyDeleteThanks for nice tutorial. but I am facing problem in inserting data to database.
ReplyDeletePlease resolve.
bueno
ReplyDeletei have been reading all of the instructions but i can't make it work with my own sql database ???
ReplyDeletethe code is not working......... if you make a complete similar mysql db. and copy the code...
ReplyDeletethis is a great tutorial. This is one of the bests site i have visited since I started programming. Thanks to you
ReplyDeleteHola 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.
ReplyDeleteHello,
ReplyDeleteThanks 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.
You are great, I'll be your tagging behind your tutorials and posts
ReplyDeleteI will be tagging behind your tutorials and posts, great to you guys, god bless you and keep doing what you're doing
ReplyDeletehow can add some other fields as well? trying to add more but did'nt worked
ReplyDeleteteach show details by clicking on the table row, please
ReplyDeletePorque me manda error de JSON, tengo 28 registros cargados, y al filtrar 25 o 100 me manda ese error o al buscar
ReplyDeleteError de JSON response :(
ReplyDeleteMe marca error cuando filtro a 25 registros :(
ReplyDeleteError JSON response :(
ReplyDeleteHola, mi duda es como cambio el "SHOWING" o "SHOW 10 ENTRIES" a ESPAÑOL....
ReplyDeleteSe puede??
Hello, when i run this its shown as no data available, do you know why?
ReplyDeleteBeautiful, thanks a lot for this...
ReplyDeleteNice! Thanks!
ReplyDeleteAwesome..sir
ReplyDeleteYou make my day...
Thank you.
Thank man
ReplyDeleteHello Sir,
ReplyDeletewhen we tried to delete single image, its delete all the images.. pls help
Hi guy,
ReplyDeleteCan you reproduce this tutorial using laravel 5.5
We can't see the image into this demo page
ReplyDeleteWhat is the problem if my data is not showing into dataTables?
ReplyDeletemuito Obrigado por tuas aulas, ajudou bastante
ReplyDeleteHow Can I manage more images?
ReplyDeleteHow Can I manage more images?
ReplyDeleteHallo Could u add viw image on this tutorial I need it
ReplyDeleteI 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.
ReplyDeletequery .='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
Hagale con bootstrap 4 porfa para ver como cambia la interfaz
ReplyDeletehello,
ReplyDeleteColumn Sorting Sorts Wrong Columns
how to fix ?
Thanks for sharing this tutorial...It,s very helpful
ReplyDeleteVery nice work, with a small note is the need to delete the image modified after it will become unused.
ReplyDeleteHello.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.
ReplyDeletethank's
ReplyDeleteits really admirable post. thaaaanks. But
ReplyDeletehow to sort int value or numbers 1, 25, 52, 3, 20 etc ??? please help
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......
ReplyDeleteArigatou na.. Dude..
ReplyDeleteThis AWESOME, thanks!
ReplyDeleteHello,
ReplyDeletegreat 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
Same problem here. I cant sorting correctly. What could it be?
DeleteThis comment has been removed by the author.
ReplyDeleteHello,
ReplyDeleteThanks 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.
Hai,
DeleteI 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
Add space in each query like
Delete$query .= ' LIMIT ' . $_POST["start"] . ', ' . $_POST["length"];
I get this error:
ReplyDeleteDataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7
Literally i like your website from heart and hope that keep on sharing this kind of stuff in the future also.
ReplyDeleteHi, 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
ReplyDeleteAbout problem with sorting...
DeletePut 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
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
ReplyDeleteHello,
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
thank you so much sir..
ReplyDeleteJust 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.
ReplyDeletePlease advise, where to download this sort icons from and I guess i can store locally on root folder under images.
https://github.com/DataTables/DataTables/tree/master/media/images
Deletei user where condition and also search
ReplyDeletethen 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"].'%" ';
}
soved
ReplyDeletewhere 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"].'%") ';
Seem problem:
ReplyDeleteDataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7
Good Example to my project.. thanks a lot..
ReplyDeleteupdate button don't function
ReplyDeleteHi.
ReplyDeleteI am trying to update the code to latest bootstrap, jquery, datatables but I get errors.
Can you please update the code?
Hi,
ReplyDeleteI 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
it showing error ""DataTables warning: table id={id} - Invalid JSON response.
ReplyDeleteplease help me
thank you so much this will help me for my project in school.
ReplyDeleteI want to make fancybox plugin for pictures.
ReplyDeleteCould you help
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?
ReplyDeletehello sir can i use it as free. and one more question how to count rows?
ReplyDeleteGood afternoon, I really liked this example, but it doesn't show the database records.
ReplyDeleteShows 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?
Hello, I added the Bootstrap 4.5 navbar:
ReplyDeletenav 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?
Hi,
ReplyDeleteIt 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!
anyone has figure out why update button won't show modal?
ReplyDeleteon update it only filtered with the first and the second is empty or like insert how can i makes it as it filtered with both of them?
ReplyDeleteJust wanted to say a big THANK YOU!!!!
ReplyDeletethis is the better tutorial for the new programmer. thanks a lot for publish the flexible tutorial. overall the best for practice.
ReplyDeletebest
ReplyDeleteHi, I don't understand why, in fetch.php you are writing code to handle the "Search" box and the "Column Ordering" when DataTables handles that automatically. Wouldn't it be better to let DataTables handle this operation?
ReplyDelete