Today we are going to see How to Ajax add, edit, delete records in the database using bootstrap modal with PHP and jquery. In this tutorial, I will explain about how to add and edit with single bootstrap form. For using this Script you can easily add and edit the user's information. Here I have used some jquery script for display dynamic record to the form. Let's see deep in the tutorial.
Database
Create a new table as users_information with below structure. insert some dumping data. This data will load our bootstrap model.CREATE TABLE `users_information` ( `id` int(11) NOT NULL, `name` varchar(50) NOT NULL, `email` varchar(100) NOT NULL, `country` varchar(50) NOT NULL, `mobile` varchar(50) NOT NULL, `about` varchar(300) NOT NULL, `dob` date NOT NULL, `last_update` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `users_information` -- INSERT INTO `users_information` (`id`, `name`, `email`, `country`, `mobile`, `about`, `dob`, `last_update`) VALUES (1, 'Karthick', 'mostlikers@gmail.com', 'India', '9876543210', 'Blogger', '1992-12-02', '2017-04-17 00:00:00'), (2, 'Arunkumar', 'arun@mostlikers.com', 'India', '8976543210', 'Author', '1991-05-21', '2017-04-17 00:00:00');
Database Connection
create a new file as config.php then paste the copied below code inside the file.<?php
$db = new mysqli('localhost','root','','mostlikers');
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error(); exit;
}
?>
Load Table Data
Create new file as index.php then follow the below steps
- Write a query to fetch the table users information
- Load the query information to Bootstrap table.
- Convert users information to Jquery data value.
- Load the ajax() and jquery functions.
Fetch the user's information
Fetch the query for all inserted records in the user's information table with name Ascending order.
<?php
include('config.php');
session_start();
$result = $db->query('SELECT * FROM users_information order by name ASC');
$data_record = $result->num_rows;
?>
Load bootstrap CDN Files.
Load Bootstrap CDN file to your <HEAD> tag<head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script> <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css"> <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> </head>
Load the query information to Bootstrap table.
var page_0 - load the users with JSON format this data will load bootstrap model. Here I have used the same form for add and edit. So, I have called same class .model_form in add /edit icon it will load the bootstrap model.
<div class="row"> <div class="col-md-4"></div> <div class="col-md-4"> <h3>Add/Edit Users information</h3></div> <div class="col-md-4"> <?php $apage = array('id'=>'','name'=>'');?> <script>var page_0 = <?php echo json_encode($apage)?></script> <h3><a data="page_0" class="model_form btn btn-sm btn-danger" href="#"> <span class="glyphicon glyphicon-plus"></span> Add new record</a></h3> </div> </div> <div class="row"> <div class="col-md-2"></div> <div class="col-md-8"> <table class="table table-bordered" cellspacing="0" width="100%"> <thead> <tr> <th>S.No</th> <th>Name</th> <th>Country</th> <th>Email</th> <th>Mobile</th> <th>About You</th> <th>Birthday</th> <th>Action</th> </tr> </thead> <tbody> <?php if(isset($result) && ($data_record) > 0) : $i=1; ?> <?php while ($users = mysqli_fetch_object($result)) { ?> <tr class="<?=$users->id?>_del"> <td><?=$i;?></td> <td><?=$users->name;?></td> <td><?=$users->country;?></td> <td><?=$users->email;?></td> <td><?=$users->mobile;?></td> <td><?=$users->about;?></td> <td><?=$users->dob;?></td> <script>var page_<?php echo $users->id ?> = <?php echo json_encode($users);?></script> <td><a data="<?php echo 'page_'.$users->id ?>" class="model_form btn btn-info btn-sm" href="#"> <span class="glyphicon glyphicon-pencil"></span></a> <a data="<?php echo $users->id ?>" title="Delete <?php echo $users->name;?>" class="tip delete_check btn btn-info btn-sm "> <span class="glyphicon glyphicon-remove"></span> </a> </td> </tr> <?php $i++; } ?> <?php else : echo '<tr><td colspan="8"><div align="center">-------No record found -----</div></td></tr>'; ?> <?php endif; ?> </tbody> </table> <?php if(isset($_SESSION['flash_msg'])) : $message = $_SESSION['flash_msg']; echo $error= '<div class="alert alert-success" role="alert"> <span class="glyphicon glyphicon-envelope"></span> <strong>'.$message.'</strong> </div>'; unset($_SESSION['flash_msg']); endif; ?> </div> <div class="col-md-2"></div> </div>
- After insert/update the record, Session message display success or failure message.
- delete_check - It's a class name of delete row ajax() call function
Bootstrap Model
Bootstrap model form model copy and paste this code to index.php file. This model design won't visible until to call the jquery function.<!-- Form modal --> <div id="form_modal" class="modal fade" tabindex="-1" role="dialog"> <div class="modal-dialog modal-md"> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button> <h4 class="modal-title"><i class="icon-paragraph-justify2"></i> <span id="pop_title">Add</span> Users information</h4> </div> <!-- Form inside modal --> <form method="post" action="add_edit.php" id="cat_form"> <div class="modal-body with-padding"> <div class="form-group"> <div class="row"> <div class="col-sm-12"> <label>Name :</label> <input type="text" name="name" id="name" class="form-control"> </div> </div> </div> <div class="form-group"> <div class="row"> <div class="col-sm-12"> <label>Country :</label> <input type="text" name="country" id="country" class="form-control"> </div> </div> </div> <div class="form-group"> <div class="row"> <div class="col-sm-12"> <label>Email :</label> <input type="email" name="email" id="email" class="form-control email"> </div> </div> </div> <div class="form-group"> <div class="row"> <div class="col-sm-12"> <label>Mobile :</label> <input type="text" name="mobile" id="mobile" class="form-control number"> </div> </div> </div> <div class="form-group"> <div class="row"> <div class="col-sm-12"> <label>About You :</label> <textarea name="about" id="about" class="form-control"></textarea> </div> </div> </div> <div class="form-group"> <div class="row"> <div class="col-sm-12"> <label>Birthday :</label> <input type="date" id="dob" class="form-control" name="dob"> </div> </div> </div> </div> <div class="modal-footer"> <button type="button" class="btn btn-warning" data-dismiss="modal">Cancel</button> <span id="add"> <input type="hidden" name="id" value="" id="id"> <button type="submit" name="form_data" class="btn btn-primary">Submit</button> </span> </div> </form> </div> </div> </div> <!-- /form modal -->
Ajax Script
Load the ajax script for add, update and delete the user information<script src="js/script.js"></script> <script type="text/javascript"> $(document).ready(function() { $(document).on('click','.model_form',function(){ $('#form_modal').modal({ keyboard: false, show:true, backdrop:'static' }); var data = eval($(this).attr('data')); $('#id').val(data.id); $('#name').val(data.name); $('#country').val(data.country); $('#email').val(data.email); $('#about').val(data.about); $('#mobile').val(data.mobile); $('#dob').val(data.dob); if(data.id!="") $('#pop_title').html('Edit'); else $('#pop_title').html('Add'); }); $(document).on('click','.delete_check',function(){ if(confirm("Are you sure to delete data")){ var current_element = $(this); url = "add_edit.php"; $.ajax({ type:"POST", url: url, data: {ct_id:$(current_element).attr('data')}, success: function(data) { //location.reload(); $('.'+$(current_element).attr('data')+'_del').animate({ backgroundColor: "#003" }, "slow").animate({ opacity: "hide" }, "slow"); } }); } }); }); </script>
Add and delete the user's information PHP code
Create a new file called as add_edit.php After the ajax() call users information form data post to this page.
<?php session_start(); include("config.php"); if(isset($_POST['form_data'])) : $user_name = $db->real_escape_string($_POST['name']); $user_email = $db->real_escape_string($_POST['email']); $user_country = $db->real_escape_string($_POST['country']); $user_about = $db->real_escape_string($_POST['about']); $date = new DateTime($db->real_escape_string($_POST['dob'])); $user_dob = $date->format('Y-m-d'); $user_mobile = $db->real_escape_string($_POST['mobile']); $id = ($_POST['id']!="") ? $db->real_escape_string($_POST['id']) : '';
if($id!="") : $query = " UPDATE `users_information` SET `name`= '$user_name', `email`='$user_email', `country`='$user_country', `mobile`='$user_mobile' , `about`='$user_about', `dob`='$user_dob' WHERE id=$id"; $msg = "Successfully Updated Your Record"; else: $query = " INSERT INTO `users_information` SET `name`= '$user_name', `email`='$user_email', `country`='$user_country', `mobile`='$user_mobile' , `about`='$user_about',`dob`='$user_dob'"; $msg = "Successfully Inserted Your Record"; endif; $sql = $db->query($query); $_SESSION['flash_msg'] = $msg; header("Location:index.php"); endif; if(isset($_POST['ct_id'])) : $id = ($_POST['ct_id']!="") ? $db->real_escape_string($_POST['ct_id']) : '';
if($id!="") : $query = "DELETE FROM users_information WHERE id =$id"; $sql = $db->query($query); echo 1; else : echo 0; endif; else : echo 0; endif; ?>
I have hope this tutorial helpful for you. Share your feedback and comments.
Hey Karthick, Now a you are a pro blogger, developer i think you should share your income reports on this blog ? like other bloggers do :)
ReplyDeleteDont use that
ReplyDeleteits not safe code.
Hi ------,
ReplyDeleteWe are missied used few lines. Now $db->real_escape_string($id), You can use this code it's safe. Other than any security issues tel with us, We will update ASAP.
if i need to use one more button to see all details (view button) ,need codings,because ,i have edited but gets more error.kindly share view button modal
ReplyDeletehow did you called the script.js you did not create the JS file plz help me with this
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteAll seems to work ok except new record doesn't add to the database. The page reports "Successfully Inserted Your Record" but it hasn't!
ReplyDeleteHelp please?