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();
}
?>
On my localhost, it loads but does not add, what can it be?
ReplyDeletesame here it does not work in local host ....not able to add and no display for editing records
Deletesir please upload angularjs project
ReplyDeleteYou ve helped me a lot!
ReplyDeleteno problem
Deletehi delete function is no working
ReplyDeleteThis is the easiest to follow example I have seen, and it all works well for me. It's great, many thanks.
ReplyDeleteThanks very much.
ReplyDeleteHi, I try this and work for text data. But for datepicker it cannot save to mysql. Do you have any solution?
ReplyDeleteHello, 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?
ReplyDeleteHow to query and output information from two tables? And how to make some of the fields static/ not editable?
ReplyDeleteGreat Job sir. Helped me a lot.
ReplyDeletehi, how to integrate with laravel?
ReplyDeletehi, how to convert your coding into laravel? especially for fetch_data.php
ReplyDeletethis is so helpful to me
ReplyDeletebut i want to know how to set (id name) and (class name) for html input
values wont show from my db
ReplyDeletehello sir, when i update or delete, the data do not cahage in mysql. help me
ReplyDeletewhat is "Content-Type: application/json"
ReplyDeleteThis is great, thanks. Any way to disable the 'delete' option?
ReplyDeleteThe filtering doesn´t work for me
ReplyDeletePlease tell me if there's any way to use utf8 support for this gird?
ReplyDeleteHi to change name of columns?
ReplyDeleteHi, I'm try it but controller with url: "fetch_data.php" not working? how to run it?
ReplyDeletehow to integrate with Codeigniter.
ReplyDeleteGreat Tutorial.. Thank you very much
ReplyDeletexyz.dummy67@gmail.com
ReplyDeletexyz.dummy67@gmail.com
ReplyDeleteThank you. This is a great post in term of explanation and the code works first time !!! Nice.
ReplyDeleteThanks 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.
ReplyDeletesame here ....what is the solution
DeleteHOW TO ENTER A DATE COULUMN IN THIS
ReplyDelete