Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
CRUD opration in PHP with Ajax and Jqury
#1
Wink 
DataTables is a jQuery JavaScript library to convert simple HTML table to dynamic feature rich table. The jQuery DataTables are very user friendly to list records with live add, edit, delete records without page refresh. Due to this, DataTables used widely in web application to list records.
So if you’re thinking to use jQuery DataTables in your project, then its very easy. You can easily implement jQuery DataTables in your project with PHP and Ajax. In this tutorial you will learn how to implement [b]Live Add, Edit and Delete DataTables Records with Ajax PHP and MySQL[/b].
We will cover this tutorial in easy steps to create live example to jQuery DataTables to list records with live add, edit and delete record functionality. You can also download complete project. The download link is located at the end of tutorial.

[Image: Live-Add-Edit-Delete-datatables-Records-...QL-min.png]
[img=678x0]http://www.phpzag.com/wp-content/uploads/2019/06/Live-Add-Edit-Delete-datatables-Records-with-PHP-and-MySQL-min.png[/img]
As we will cover this tutorial with live example to Live Add Edit Delete DataTables Records with Ajax, PHP & MySQL, so the major files for this example is following.


  • [b]index.php[/b]

  • [b]ajax.js[/b]

  • [b]ajax_action.php[/b]

  • [b]Records.php[/b]
[b]Step1: Create MySQL Database Tables[/b]
First we will create MySQL database tables [b]live_records[/b] to add, edit and delete records.





PHP Code:
CREATE TABLE `live_records` (
`
idint(11NOT NULL,
`
namevarchar(255NOT NULL,
`
skillsvarchar(255NOT NULL,
`
addressvarchar(255NOT NULL,
`
designationvarchar(255NOT NULL,
`
ageint(11NOT NULL
ENGINE=InnoDB DEFAULT CHARSET=latin1

[b]Step2: Listing Records in DataTables[/b]

In [b]index.php[/b] file, we will create Table to list records using jQuery DataTables.


Code:
<table id="recordListing" class="table table-bordered table-striped">
<thead>
<tr>
<th>#</th>
<th>Name</th>
<th>Age</th>
<th>Skills</th>
<th>Address</th>
<th>Designation</th>
<th></th>
<th></th>
</tr>
</thead>
</table>

We will initialize jQuery DataTables in [b]ajax.js[/b] file and make ajax request to action [b]listRecords[/b] to make server side request to fetch records to load in DataTables.


Code:
var dataRecords = $('#recordListing').DataTable({
"lengthChange": false,
"processing":true,
"serverSide":true,
"order":[],
"ajax":{
url:"ajax_action.php",
type:"POST",
data:{action:'listRecords'},
dataType:"json"
},
"columnDefs":[
{
"targets":[0, 6, 7],
"orderable":false,
},
],
"pageLength": 10
});


We will call method [b]listRecords()[/b] on action [b]listRecords[/b] to list records.

PHP Code:
$record = new Records();
if(!empty(
$_POST['action']) && $_POST['action'] == 'listRecords') {
$record->listRecords();



We will create method [b]listRecords()[/b] in class [b]Records.php[/b] to fetch records from MySQL database and return as JSON data.


PHP Code:
public function listRecords(){
$sqlQuery "SELECT * FROM ".$this->recordsTable." ";
if(!empty(
$_POST["search"]["value"])){
$sqlQuery .= 'where(id LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= ' OR name LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= ' OR designation LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= ' OR address LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= ' OR skills LIKE "%'.$_POST["search"]["value"].'%") ';
}
if(!empty(
$_POST["order"])){
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY id DESC ';
}
if(
$_POST["length"] != -1){
$sqlQuery .= 'LIMIT ' $_POST['start'] . ', ' $_POST['length'];
}
$result mysqli_query($this->dbConnect$sqlQuery);
$numRows mysqli_num_rows($result);
$records = array();
while( 
$record mysqli_fetch_assoc($result) ) {
$rows = array();
$rows[] = $record['id'];
$rows[] = ucfirst($record['name']);
$rows[] = $record['age'];
$rows[] = $record['skills'];
$rows[] = $record['address'];
$rows[] = $record['designation'];
$rows[] = '<button type="button" name="update" id="'.$record["id"].'" class="btn btn-warning btn-xs update">Update</button>';
$rows[] = '<button type="button" name="delete" id="'.$record["id"].'" class="btn btn-danger btn-xs delete" >Delete</button>';
$records[] = $rows;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $numRows,
"recordsFiltered" => $numRows,
"data" => $records
);
echo 
json_encode($output); 

[b]Step3: Add New Record[/b]

In [b]index.php[/b] file, we will create Bootstrap modal to add new records to jQuery DataTables.



Code:
<div id="recordModal" class="modal fade">
<div class="modal-dialog">
<form method="post" id="recordForm">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title"><i class="fa fa-plus"></i> Add Record</h4>
</div>
<div class="modal-body">
<div class="form-group"
<label for="name" class="control-label">Name</label>
<input type="text" class="form-control" id="name" name="name" placeholder="Name" required>
</div>
<div class="form-group">
<label for="age" class="control-label">Age</label>
<input type="number" class="form-control" id="age" name="age" placeholder="Age">
</div>
<div class="form-group">
<label for="lastname" class="control-label">Skills</label>
<input type="text" class="form-control" id="skills" name="skills" placeholder="Skills" required>
</div>
<div class="form-group">
<label for="address" class="control-label">Address</label>
<textarea class="form-control" rows="5" id="address" name="address"></textarea>
</div>
<div class="form-group">
<label for="lastname" class="control-label">Designation</label>
<input type="text" class="form-control" id="designation" name="designation" placeholder="Designation">
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="id" id="id" />
<input type="hidden" name="action" id="action" value="" />
<input type="submit" name="save" id="save" class="btn btn-info" value="Save" />
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</form>
</div>
</div>


We will handle modal form submit using jQuery and make Ajax request with action [b]addRecord[/b] to add new records.

PHP Code:
$("#recordModal").on('submit','#recordForm', function(event){
event.preventDefault();
$(
'#save').attr('disabled','disabled');
var 
formData = $(this).serialize();
$.
ajax({
url:"ajax_action.php",
method:"POST",
data:formData,
success:function(data){
$(
'#recordForm')[0].reset();
$(
'#recordModal').modal('hide');
$(
'#save').attr('disabled'false);
dataRecords.ajax.reload();
}
})
}); 

We will call method [b]addRecord()[/b] on action [b]addRecord[/b] to add new records.

PHP Code:
$record = new Records();
if(!empty(
$_POST['action']) && $_POST['action'] == 'addRecord') {
$record->addRecord();


We will create method [b]addRecord()[/b] in class [b]Records.php[/b] to add new records into MySQL database.

PHP Code:
public function addRecord(){
if(
$_POST["name"]) {
$insertQuery "INSERT INTO ".$this->recordsTable." (name, age, skills, address, designation)
VALUES ('"
.$_POST["name"]."', '".$_POST["age"]."', '".$_POST["skills"]."', '".$_POST["address"]."', '".$_POST["designation"]."')";
mysqli_query($this->dbConnect$insertQuery);
}



[b]Step4: Update Record[/b]

We will handle records update functionality by populating records values to update modal form by make Ajax request to action [b]getRecord[/b] to load values to modal form input.



PHP Code:
$("#recordListing").on('click''.update', function(){
var 
id = $(this).attr("id");
var 
action 'getRecord';
$.
ajax({
url:'ajax_action.php',
method:"POST",
data:{id:idaction:action},
dataType:"json",
success:function(data){
$(
'#recordModal').modal('show');
$(
'#id').val(data.id);
$(
'#name').val(data.name);
$(
'#age').val(data.age);
$(
'#skills').val(data.skills);
$(
'#address').val(data.address);
$(
'#designation').val(data.designation);
$(
'.modal-title').html(" Edit Records");
$(
'#action').val('updateRecord');
$(
'#save').val('Save');
}
})
}); 


We will call method [b]updateRecord()[/b] from class [b]Records.php[/b] to update records.

PHP Code:
$record = new Records();
if(!empty(
$_POST['action']) && $_POST['action'] == 'updateRecord') {
$record->updateRecord();



We will create method [b]updateRecord()[/b] in class [b]Records.php[/b] to update records into MySQL database table.

PHP Code:
public function updateRecord(){
if(
$_POST['id']) {
$updateQuery "UPDATE ".$this->recordsTable."
SET name = '"
.$_POST["name"]."', age = '".$_POST["age"]."', skills = '".$_POST["skills"]."', address = '".$_POST["address"]."' , designation = '".$_POST["designation"]."'
WHERE id ='"
.$_POST["id"]."'";
mysqli_query($this->dbConnect$updateQuery);
}


[b]Step5: Deletet Record[/b]

We will handle records delete functionality by making ajax request with action [b]deleteRecord[/b] to delete record from MySQL database table.


PHP Code:
$(#recordListing”).on(‘click’, ‘.delete’, function(){
var id = $(this).attr(“id”);
var 
action “deleteRecord”;
if(
confirm(“Are you sure you want to delete this record?)) {
$.
ajax({
url:”ajax_action.php”,
method:”POST”,
data:{id:idaction:action},
success:function(data) {
dataRecords.ajax.reload();
}
})
} else {
return 
false;
}
}); 


We will call method [b]deleteRecord()[/b] from class [b]Records.php[/b] on action [b]deleteRecord[/b] to delete records.

PHP Code:
include('Records.php');
$record = new Records();
if(!empty(
$_POST['action']) && $_POST['action'] == 'deleteRecord') {
$record->deleteRecord();



We will create method [b]deleteRecord()[/b] in class [b]Records.php[/b] to delete records into MySQL database table.

PHP Code:
public function deleteRecord(){
if(
$_POST["id"]) {
$sqlDelete "
DELETE FROM "
.$this->recordsTable."
WHERE id = '"
.$_POST["id"]."'";
mysqli_query($this->dbConnect$sqlDelete);
}

Reply




Users browsing this thread: 1 Guest(s)