Bootstrap Datatable With Add Edit Remove option In Php Mysql and Ajax

This tutorial will explain about Bootstrap Datatable with Add, edit and remove record. Here we will explain Mysql CURD operation in Bootstrap Datatable. This Add, edit and remove action happens with the use of AJAX. Here we will use a simple Bootstrap Modal Popup to create add and edit form.

(Also Read: Bootstrap DataTable Using PHP, Mysql, Ajax, Json with server side script )

WHAT ARE WE DOING TODAY

Few months back, we have published an article on Bootstrap with php, mysl server side script. Based on the previous article of Bootstrap datatable, we are going to create a simple datatable with add, edit and remove options. The table will be modified by jQuery via AJaX and PHP with MySQL on the backend. We using the code from the last article and change.

Explanation:

EXTEND HTML & JAVASCRIPT & PHP

First of all, we need 2 new modal window for add new entry and edit existing entries:

For Add and edit, we use below Bootstrap Popup Modal

<div class="modal fade" id="edit-modal" tabindex="-1" role="dialog" aria-labelledby="edit-modal-label">
<div class="modal-dialog" role="document">
<div class="modal-content">
<form class="form-horizontal" id="edit-form">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span
aria-hidden="true">&times;</span></button>
<h4 class="modal-title" id="edit-modal-label">Edit selected row</h4>
</div>
<div class="modal-body">
<input type="hidden" id="edit-id" value="" class="hidden">
<div class="form-group">
<label for="firstname" class="col-sm-2 control-label">Firstname</
label>
<div class="col-sm-10">
<input type="text" class="form-control" id="firstname"
name="firstname" placeholder="Firstname" required>
</div>
</div>
<div class="form-group">
<label for="email" class="col-sm-2 control-label">E-mail</label>
<div class="col-sm-10">
<input type="email" class="form-control" id="email"
name="email" placeholder="E-mail address" required>
</div>
</div>
<div class="form-group">
<label for="mobile" class="col-sm-2 control-label">Mobile</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="mobile"
name="mobile" placeholder="Mobile" required>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
<button type="submit" class="btn btn-primary">Save changes</button>
</div>
</form>
</div>
</div>
</div>
<div class="modal fade" id="add-modal" tabindex="-1" role="dialog" aria-labelledby="add-modallabel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<form class="form-horizontal" id="add-form">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span
aria-hidden="true">&times;</span></button>
<h4 class="modal-title" id="add-modal-label">Add new row</h4>
</div>
<div class="modal-body">
<div class="form-group">
<label for="add-firstname" class="col-sm-2 controllabel">
Firstname</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="add-firstname"
name="firstname" placeholder="Firstname" required>
</div>
</div>
<div class="form-group">
<label for="add-email" class="col-sm-2 control-label">E-mail</
label>
<div class="col-sm-10">
<input type="email" class="form-control" id="add-email"
name="email" placeholder="E-mail address" required>
</div>
</div>
<div class="form-group">
<label for="add-mobile" class="col-sm-2 control-label">Mobile</
label>
<div class="col-sm-10">
<input type="text" class="form-control" id="add-mobile"
name="mobile" placeholder="Mobile" required>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
<button type="submit" class="btn btn-primary">Save changes</button>
</div>
</form>
</div>
</div>
</div>

Then place an add button to the upper right corner, which is going to open the add modal window. The new entry form will save the data via jQuery and then add the new row to the table. After that, we need to write some PHP:

<?php
if ( isset($_GET['add']) && isset($_POST) ) {
dbinit(&$gaSql);
$p = $_POST;
foreach ( $p as &$val ) $val = mysql_real_escape_string($val);
if ( !empty($p['firstname']) && !empty($p['email']) && !empty($p['mobile']) ) {
@mysql_query(" INSERT INTO $sTable (name, email, mobile) VALUES ('" . $p['firstname'] . "',
'" . $p['email'] . "', '" . $p['mobile'] . "')");
$id = mysql_insert_id();
$query = mysql_query(" SELECT * FROM $sTable WHERE $sIndexColumn = " . $id,
$gaSql['link']);
die(json_encode(mysql_fetch_assoc($query)));
}
}
?>

Next step is to edit the current rows. We placed an edit button to the end of each row. When the user clicks on this button, the editRow(id) will be called:

function editRow(id) {
if ( 'undefined' != typeof id ) {
$.getJSON('datatable.php?edit=' + id, function(obj) {
$('#edit-id').val(obj.id);
$('#firstname').val(obj.name);
$('#email').val(obj.email);
$('#mobile').val(obj.mobile);
$('#edit-modal').modal('show')
}).fail(function() { alert('Unable to fetch data, please try again later.') });
} else alert('Unknown row id.');
}

This function also need some PHP script on the server side, which is the following:

<?php
if ( isset($_GET['edit']) && 0 < intval($_GET['edit']) ) {
dbinit(&$gaSql);
// SAVE DATA
if ( isset($_POST) ) {
$p = $_POST;
foreach ( $p as &$val ) $val = mysql_real_escape_string($val);
if ( !empty($p['firstname']) && !empty($p['email']) && !empty($p['mobile']) )
@mysql_query(" UPDATE $sTable SET name = '" . $p['firstname'] . "', email = '" .
$p['email'] . "', mobile = '" . $p['mobile'] . "' WHERE id = " . intval($_GET['edit']));
}
// GET DATA
$query = mysql_query(" SELECT * FROM $sTable WHERE $sIndexColumn = " . intval($_GET['edit']),
$gaSql['link']);
die(json_encode(mysql_fetch_assoc($query)));
}
?>

Now we can add new entries and edit the existing ones. But what if we need to remove an entry? The answer is easy: create a remove function! Next to the edit buttons, create a remove button which is going to call the removeRow(id) function:

function removeRow(id) {
if ( 'undefined' != typeof id ) {
$.get('datatable.php?remove=' + id, function() {
$('a[data-id="row-' + id + '"]').parent().parent().remove();
}).fail(function() { alert('Unable to fetch data, please try again later.') });
} else alert('Unknown row id.');
}

And as you guess it, we need some server-side script also:

<?php
if ( isset($_GET['remove']) && 0 < intval($_GET['remove']) ) {
dbinit(&$gaSql);
// REMOVE DATA
@mysql_query(" DELETE FROM $sTable WHERE id = " . intval($_GET['remove']));
}
?>

SUMMARY

So we continued the previous article and extended it. Now we are able to create new entries, edit the existing ones or remove them. These function processed via AJaX, so you don't have to reload the site every time a form is submitted.

Share this Article on Social Media

All of my Scripts are ready to customized as per your requirement. Feel free to contact for script customization.

Contact me at discussdesk@gmail.com

"Note : It will be charged as per your customization requirement :)"

Get Updates, Scripts & Other Tutorials to Directly to your Email

Over 20000+ Happy Readers already subscribed. (We don't send spam email). Every email subscriber can get our latest updates and download our 100+ scripts.

Comments