Insert Update Delete in PHP on Same Page

3 Comments on 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);
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.

Recommended:-Ajax Image Upload Using PHP and jQuery Without Refreshing Page

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:-Select Insert Update Delete Record using PHP and MySQL
Recommended:-Get Country, City, latitude, longitude from IP address using PHP
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:-PHP Find Nearest Location using Latitude and Longitude Example
Recommended:-PHP Send Push Notification To Android & IOS using Google FCM
Recommended:-Replace First and Last Character From String PHP
Recommended:-How to Install PHP 8 on Ubuntu 20.04 using Terminal
Filed Under: Ajax, Bootstrap, Jquery, MySQL, PHP

AuthorDevendra Dode

Greetings, I'm Devendra Dode, a full-stack developer, entrepreneur, and the proud owner of Tutsmake.com. My passion lies in crafting informative tutorials and offering valuable tips to assist fellow developers on their coding journey. Within my content, I cover a spectrum of technologies, including 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. Whether you're starting out or looking for advanced examples, I provide step-by-step guides and practical demonstrations to make your learning experience seamless. Let's explore the diverse realms of coding together.

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.

    Reply

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

      Reply

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

    Reply

Leave a Reply

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