Insert Update Delete in PHP on Same Page

Insert Update Delete in PHP on Same Page

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);

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());
      }
?>

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);

    }
 
?>

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

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

3 replies to Insert Update Delete in PHP on Same Page

  1. Hi Devendra,

    Please how can l add all the libraries in your tutorial (above) to my project locally, and practice the tutorial on my PC without using Internet.

    Thank you in advance for your assistance

    Regards,
    Richard.

    • You can download all these libraries from Google and include the path of these libraries into your project.

  2. Thank you so much Devendra dode for this tutorial, This tutorials have helped me a lot.

Leave a Reply

Your email address will not be published. Required fields are marked *