03-Apr-2023
.
Admin
Hi Dev,
This tutorial is focused on how to insert update delete in PHP on the same page. you'll learn how to fetch insert update delete data in PHP MySQL. We will use how to fetch insert update delete data using PHP MySQL. if you have a question about how to insert update delete using PHP on the same page then I will give a simple example with a solution.
Now, let's see the article on how to fetch insert update delete data using PHP MySQL. it's a simple example of how to insert update delete using PHP on the same page. you can understand the concept of how to insert update delete in PHP on the same page. if you have a question about how to fetch insert update delete data in PHP MySQL then I will give a simple example with a solution.
Step 1: Create a Database
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, 'tiger', '26', 'tiger@gmail.com', NULL), (NULL, 'john', '28', 'john@gmail.com', NULL);
Step 2: Create a Database Connection File
<?php
$servername='localhost';
$username='root';
$password='';
$dbname = "my_db";
$conn=mysqli_connect($servername,$username,$password,$dbname);
if(!$conn){
die('Could not Connect MySql Server:' .mysql_error());
}
?>
Step 3: Retrieve All Data From Database and Display in HTML Table
<!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>
</td>
</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>
</div>
</div>
</body>
<!-- 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>
</html>
Step 4: Edit Data From Database
<?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);
}
?>
Step 5: Insert and Update Data Into Database
<?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
<?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);
}
?>
I hope it could help you...
#PHP
