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();
}
?>
