To fetch insert update delete data in PHP MySQL database with source code. In this tutorial, we will show you how to fetch/select insert update delete data in PHP from MySQL database using jQuery ajax with source code.
In this example, we will use jQuery, ajax, MySQL, and Bootstrap with PHP to fetch/select insert update delete on same page or one-page app with source code.
How to Insert Update Delete in PHP on Same Page with Ajax jQuery
Steps to implement fetch/select insert update delete in PHP MySQL on same page with Ajax jQuery & source code download:
- Step 1 – Create a Database
- Step 2 – Connecting To Database using PHP
- Step 3 – Retrieve All Data From Database and Display in HTML Table
- Step 4 – Edit Data From Database
- Step 5 – Insert and Update Data Into Database
- Step 6 – Delete Data From Database
Step 1 – Create a Database
In step 1, Open your browser and navigate to your phpmyadmin. Then run the following query to create database and table:
CREATE DATABASE demo;
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NULL DEFAULT NULL,
`age` VARCHAR(255) NULL DEFAULT NULL,
`email` VARCHAR(255) NULL DEFAULT NULL,
`created` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
INSERT INTO `users` (`id`, `name`, `age`, `email`, `created`) VALUES (NULL, 'Tiago', '26', '[email protected]', NULL), (NULL, 'Anil', '28', '[email protected]', NULL);
Recommended:-Simple Registration Form in PHP with Validation
Step 2 – Connecting To Database using PHP
Then create one file name db.php, which is used to connect app from database:
Then add the following code into it:
<?php
$hName='localhost'; // host name
$uName='root'; // database user name
$password=''; // database password
$dbName = "demo"; // database name
$dbCon = mysqli_connect($hName,$uName,$password,"$dbName");
if(!$dbCon){
die('Could not Connect MySql Server:' .mysql_error());
}
?>
Recommended:-PHP Contact Form with jQuery Validation Example
Step 3 – Retrieve All Data From Database and Display in HTML Table
Now, you need to create Index.php file and add the following code into it:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Insert Update Delete in PHP On Same Page</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" >
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container mt-2">
<div class="row">
<div class="col-md-8 mt-1 mb-1"><h2 class="text-white bg-dark">Insert Update Delete in PHP On Same Page</h2></div>
<div class="col-md-8 mt-1 mb-2"><button type="button" id="addNewUser" class="btn btn-success">Add</button></div>
<div class="col-md-8">
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">Name</th>
<th scope="col">Age</th>
<th scope="col">Email</th>
<th scope="col">Action</th>
</tr>
</thead>
<tbody>
<?php
include 'db.php';
$query="select * from users limit 150";
$result=mysqli_query($dbCon,$query);
?>
<?php if ($result->num_rows > 0): ?>
<?php while($array=mysqli_fetch_row($result)): ?>
<tr>
<th scope="row"><?php echo $array[0];?></th>
<td><?php echo $array[1];?></td>
<td><?php echo $array[2];?></td>
<td><?php echo $array[3];?></td>
<td>
<a href="javascript:void(0)" class="btn btn-primary edit" data-id="<?php echo $array[0];?>">Edit</a>
<a href="javascript:void(0)" class="btn btn-primary delete" data-id="<?php echo $array[0];?>">Delete</a>
</tr>
<?php endwhile; ?>
<?php else: ?>
<tr>
<td colspan="3" rowspan="1" headers="">No Data Found</td>
</tr>
<?php endif; ?>
<?php mysqli_free_result($result); ?>
</tbody>
</table>
</div>
</div>
</div>
<!-- boostrap model -->
<div class="modal fade" id="user-model" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title" id="userModel"></h4>
</div>
<div class="modal-body">
<form action="javascript:void(0)" id="userInserUpdateForm" name="userInserUpdateForm" class="form-horizontal" method="POST">
<input type="hidden" name="id" id="id">
<div class="form-group">
<label for="name" class="col-sm-2 control-label">First Name</label>
<div class="col-sm-12">
<input type="text" class="form-control" id="name" name="name" placeholder="Enter Name" value="" maxlength="50" required="">
</div>
</div>
<div class="form-group">
<label for="name" class="col-sm-2 control-label">Age</label>
<div class="col-sm-12">
<input type="text" class="form-control" id="age" name="age" placeholder="Enter Age" value="" maxlength="50" required="">
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">Email</label>
<div class="col-sm-12">
<input type="email" class="form-control" id="email" name="email" placeholder="Enter Email" value="" required="">
</div>
</div>
<div class="col-sm-offset-2 col-sm-10">
<button type="submit" class="btn btn-primary" id="btn-save" value="addNewUser">Save changes
</button>
</div>
</form>
</div>
<div class="modal-footer">
</div>
</div>
</div>
</div>
<!-- end bootstrap model -->
<script type="text/javascript">
$(document).ready(function($){
$('#addNewUser').click(function () {
$('#userInserUpdateForm').trigger("reset");
$('#userModel').html("Add New User");
$('#user-model').modal('show');
});
$('body').on('click', '.edit', function () {
var id = $(this).data('id');
// ajax
$.ajax({
type:"POST",
url: "edit.php",
data: { id: id },
dataType: 'json',
success: function(res){
$('#userModel').html("Edit User");
$('#user-model').modal('show');
$('#id').val(res.id);
$('#name').val(res.name);
$('#age').val(res.age);
$('#email').val(res.email);
}
});
});
$('body').on('click', '.delete', function () {
if (confirm("Delete Record?") == true) {
var id = $(this).data('id');
// ajax
$.ajax({
type:"POST",
url: "delete.php",
data: { id: id },
dataType: 'json',
success: function(res){
$('#name').html(res.name);
$('#age').html(res.age);
$('#email').html(res.email);
window.location.reload();
}
});
}
});
$('#userInserUpdateForm').submit(function() {
// ajax
$.ajax({
type:"POST",
url: "insert-update.php",
data: $(this).serialize(), // get all form field value in
dataType: 'json',
success: function(res){
window.location.reload();
}
});
});
});
</script>
</body>
</html>
Note that, index.php file code will display all users list from database. And as well as insert and edit data from mysql database using bootstrap model and ajax.
Step 4 – Edit Data From Database
In this step, Create edit.php file to get single record data from mysql database. So, add the following code into edit.php:
<?php
include "db.php";
$id = $_POST['id'];
$query="SELECT * from users WHERE id = '" . $id . "'";
$result = mysqli_query($dbCon,$query);
$cust = mysqli_fetch_array($result);
if($cust) {
echo json_encode($cust);
} else {
echo "Error: " . $sql . "" . mysqli_error($dbCon);
}
?>
Recommended:-How to Fetch Data From Database in PHP using Ajax
Step 5 – Insert and Update Data Into Database
In this step, insert and add data from mysql database. So, Create insert-update.php file to insert and update record data from mysql database. So, add the following code into insert-update.php:
<?php
if(count($_POST)>0)
{
include 'db.php';
$name = $_POST['name'];
$age = $_POST['age'];
$email = $_POST['email'];
if(empty($_POST['id'])){
$query = "INSERT INTO users (name,age,email)
VALUES ('$name','$age','$email')";
}else{
$query = "UPDATE users set id='" . $_POST['id'] . "', name='" . $_POST['name'] . "', age='" . $_POST['age'] . "', email='" . $_POST['email'] . "' WHERE id='" . $_POST['id'] . "'";
}
$res = mysqli_query($dbCon, $query);
if($res) {
echo json_encode($res);
} else {
echo "Error: " . $sql . "" . mysqli_error($dbCon);
}
}
?>
Step 6 – Delete Data From Database
Final step, delete data from mysql database. So, create delete.php file, which is delete data from mysql database. Now you need to add the following code into delete.php file:
<?php
include 'db.php';
$id = $_POST['id'];
$query = "DELETE FROM users WHERE id='" . $id . "'";
$res = mysqli_query($dbCon, $query);
if($res) {
echo json_encode($res);
} else {
echo "Error: " . $sql . "" . mysqli_error($dbCon);
}
?>
Conclusion
That’s it, In this tutorial, we have learned how to fetch insert update delete data in PHP from MySQL database using jQuery ajax.
Recommended PHP Tutorials
Recommended:-jQuery Ajax Form Submit with FormData Example
Recommended:-PHP 8 Pagination with Bootstrap Example
Recommended:-User Registration with Email Verification in PHP
Recommended:-How to Add Captcha in PHP Registration Form
Recommended:-PHP Cookie Set, Get And Delete Example
Recommended:-PHP | Implode and Explode Function
Recommended:-Send Reset Password Link Email PHP
Recommended:-Crop and Save Image Using jQuery Croppie and PHP
Recommended:-PHP Display All Errors Example
Recommended:-PHP Google Places Autocomplete Example
Recommended:-PHP Move and Copy File From One Folder to Another
Recommended:-PHP Dropzone File Upload Tutorial Example
Recommended:-PHP Signature Pad using Jquery Ajax Example
Recommended:-PHP 8 MySQL Ajax Live Search Autocomplete Example
Recommended:-PHP Google Firebase CRUD Example Tutorial
Recommended:-Replace First and Last Character From String PHP
Recommended:-How to Install PHP 8 on Ubuntu 20.04 using Terminal
