Create CRUD using Ajax with Example in PHP

653 views 11 months ago PHP

In this tutorial, we learn to insert update efface in PHP MySQL with example in one form in PHP.

We learn how to insert update efface in PHP on the same page. we will show you how to fetch insert update and efface data in PHP MySQL utilizing Ajax. This time learn to insert expunge update data utilizing ajax in PHP.

In this example, we will utilize jQuery, ajax, MySQL, and Bootstrap with PHP to engender insert update efface in PHP on one page.

This time Utilizing jQuery and ajax insert, edit, and update expunge data from MySQL database in PHP on the same page.

How To Insert Update Delete In Php On Same Page Using Ajax And Update Table In PHP.

  • 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 Database

In this step, Open your browser and connect 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, 'ajay', '27', '[email protected]', NULL), (NULL, 'abhay', '23', '[email protected]', NULL);

Step 2: Connecting To Database Using PHP

This step creates one file name db.php, which is used to connect the project from the database: Then add the following code in below:

<?php
    $hName='localhost';
    $uName='root';
    $password='';
    $dbName = "demo";
    $dbCon = mysqli_connect($hName,$uName,$password,"$dbName");
      if(!$dbCon){
          die('Could not Connect MySql Server:' .mysql_error());
      }
?>

Step 3: Retrieve All Data From Database And Display In HTML Table

In this step, you need to create an Index.php file and add the following code below:

index.php file code will display all user's lists from the database. And as well as insert and edit data from MySQL database using the bootstrap model and ajax.

<!DOCTYPE html>
<html lang="en">
   <head>
      <meta charset="UTF-8">
      <title>Ajax CRUD in PHP.</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">How To Insert Update Delete In Php On Same Page Using Ajax.</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);
                    }
                });
            }
        });
        $('#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) {
                }
            });
        });
    }); <
</script>
   </body>
</html>

Step 4: Edit Data From Database

In this step, Create an edit.php file to get single record data from the 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);
$data= mysqli_fetch_array($result);
if($data) {
echo json_encode($data);
} else {
echo "Error: " . $sql . "" . mysqli_error($dbCon);
}
?>

Step 5: Insert And Update Data Into Database

In this step, insert and add data from the MySQL database update table in php. So, Create an insert-update.php file to insert and update record data from the 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

In This Final step, PHP inserts update deletes data from the MySQL database. So, create delete.php file, which is delete data from mysql database. Now you need to add the following code into the 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);
}
?>

i hope you like this article.

Author : Harsukh Makwana
Harsukh Makwana

Hi, My name is Harsukh Makwana. i have been work with many programming language like php, python, javascript, node, react, anguler, etc.. since last 5 year. if you have any issue or want me hire then contact me on [email protected]