PHP Jquery Ajax CRUD Example Tutorial

Feb 08, 2022 . Admin

Hi Dev,

In this tutorial, I will share with you how to integrate PHP Jquery Ajax CRUD stands for Create, Read, Update and Delete database data. So today I will show you a very simple way to crud using the bootstrap model.

Here I will show you a full example for PHP jquery ajax crud using Bootstrap Modal, so let's follow bellow step by step.

In this tutorial i used several jquery Plugins for fire Ajax, Ajax pagination, Bootstrap, Bootstrap Validation, notification as listed below.

  1. Jquery
  2. Bootstrap
  3. twbsPagination js
  4. Validator JS
  5. toastr JS

In this example we are manage CRUD operation like..

  1. Item Listing
  2. Item Create
  3. Item Edit
  4. Item Delete
Step 1 : Create Table
CREATE TABLE IF NOT EXISTS `items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=63 ;

Let's proceed this way, we are doing from scratch so we require to engender database configration file that way we can utilize that file in many other file. so let's engender api directory and engender db_config.php file in api directory and put bellow code

Step 2 : Configuration

In this step we will Create a db_config.php file for the database configuration.

Path : api/db_config.php
<?php

    define (DB_USER,'root');
    define (DB_PASSWORD,'root');
    define (DB_DATABASE,'example');
    define (DB_HOST,'localhost');
    $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
    
?>
Step 3 : Create index.php File

In this step now we withal require to engender index.php file in our root directory. In this file i integrated "url" variable in js for site root URL. You can update withal with your site URL. so let's engender index.php file and put bellow content in that file.

index.php
<!DOCTYPE html>
<html>
    <head>
        <title>PHP Jquery Ajax CRUD Example</title>
        <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css">
        <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/4.6.1/js/bootstrap.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/twbs-pagination/1.4.2/jquery.twbsPagination.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/1000hz-bootstrap-validator/0.11.9/validator.min.js"></script>
        <script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/toastr.js/latest/js/toastr.min.js"></script>
        <link href="//cdnjs.cloudflare.com/ajax/libs/toastr.js/latest/css/toastr.min.css" rel="stylesheet">
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"/>
        <script type="text/javascript">
        var url = "http://localhost:8000/";
        </script>
        <script src="/js/item-ajax.js"></script>
    </head>
    <body class="bg-light">
        <div class="container mt-5">
            <div class="row">
                <div class="col-md-12">
                    <div class="card">
                        <div class="card-header">
                            <div class="col-lg-12 margin-tb">
                                <div class="pull-left">
                                    <h4>PHP Jquery Ajax CRUD Example - <a href="https://www.mywebtuts.com/" title="https://www.mywebtuts.com/">Mywebtuts.com</a></h4>
                                </div>
                                <div class="pull-right">
                                    <button type="button" class="btn btn-success btn-sm" data-toggle="modal" data-target="#create-item">
                                    <i class="fa fa-plus"></i>
                                    </button>
                                </div>
                            </div>
                        </div>
                        <div class="card-body">
                            <table class="table table-bordered">
                                <thead>
                                    <tr>
                                        <th width="380px">Title</th>
                                        <th>Description</th>
                                        <th width="100px">Action</th>
                                    </tr>
                                </thead>
                                <tbody>
                                </tbody>
                            </table>
                        </div>
                    </div>
                    <ul id="pagination" class="pagination-sm mt-3"></ul>
                </div>
            </div>
        <!-- Create Item Modal -->
        <div class="modal fade" id="create-item" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
            <div class="modal-dialog" role="document">
                <div class="modal-content">
                    <div class="modal-header">
                        <h4 class="modal-title" id="myModalLabel">Create Item</h4>
                        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
                    </div>
                    <div class="modal-body">
                        <form data-toggle="validator" action="api/create.php" method="POST">
                            <div class="form-group">
                                <label class="control-label" for="title">Title:</label>
                                <input type="text" name="title" class="form-control" data-error="Please enter title." required />
                                <div class="help-block with-errors"></div>
                            </div>
                            <div class="form-group">
                                <label class="control-label" for="title">Description:</label>
                                <textarea name="description" class="form-control" data-error="Please enter description." required></textarea>
                                <div class="help-block with-errors"></div>
                            </div>
                            <div class="form-group">
                                <button type="submit" class="btn crud-submit btn-success">Submit</button>
                            </div>
                        </form>
                    </div>
                </div>
            </div>
        </div>
        <!-- Edit Item Modal -->
        <div class="modal fade" id="edit-item" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
            <div class="modal-dialog" role="document">
                <div class="modal-content">
                    <div class="modal-header">
                        <h4 class="modal-title" id="myModalLabel">Edit Item</h4>
                        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
                    </div>
                    <div class="modal-body">
                        <form data-toggle="validator" action="api/update.php" method="put">
                            <input type="hidden" name="id" class="edit-id">
                            <div class="form-group">
                                <label class="control-label" for="title">Title:</label>
                                <input type="text" name="title" class="form-control" data-error="Please enter title." required />
                                <div class="help-block with-errors"></div>
                            </div>
                            <div class="form-group">
                                <label class="control-label" for="title">Description:</label>
                                <textarea name="description" class="form-control" data-error="Please enter description." required></textarea>
                                <div class="help-block with-errors"></div>
                            </div>
                            <div class="form-group">
                                <button type="submit" class="btn btn-success crud-submit-edit">Submit</button>
                            </div>
                        </form>
                    </div>
                </div>
            </div>
        </div>
    </div>
</body>
</html>
Step 4 : Create Js File

So, In this step we will create custom js File for ajax request for CRUD application.

Path : js/item-ajax.js
$( document ).ready(function() {


var page = 1;
var current_page = 1;
var total_page = 0;
var is_ajax_fire = 0;


manageData();


    /* manage data list */
    function manageData() {
        $.ajax({
            dataType: 'json',
            url: url+'api/getData.php',
            data: {page:page}
        }).done(function(data){
            total_page = Math.ceil(data.total/10);
            current_page = page;

            $('#pagination').twbsPagination({
                totalPages: total_page,
                visiblePages: current_page,
                onPageClick: function (event, pageL) {
                    page = pageL;
                    if(is_ajax_fire != 0){
                      getPageData();
                    }
                }
            });

            manageRow(data.data);
            is_ajax_fire = 1;

        });
    }


    /* Get Page Data*/
    function getPageData() {
        $.ajax({
            dataType: 'json',
            url: url+'api/getData.php',
            data: {page:page}
        }).done(function(data){
            manageRow(data.data);
        });
    }

    /* Add new Item table row */
    function manageRow(data) {
        var rows = '';
        $.each( data, function( key, value ) {
            rows = rows + '<tr>';
            rows = rows + '<td>'+value.title+'</td>';
            rows = rows + '<td>'+value.description+'</td>';
            rows = rows + '<td data-id="'+value.id+'">';
            rows = rows + '<button data-toggle="modal" data-target="#edit-item" class="btn btn-primary edit-item btn-sm"><i class="fa fa-pencil"></i></button> ';
            rows = rows + '<button class="btn btn-danger remove-item btn-sm"><i class="fa fa-trash"></i></button>';
            rows = rows + '</td>';
            rows = rows + '</tr>';
        });

        $("tbody").html(rows);
    }


    /* Create new Item */
    $(".crud-submit").click(function(e){
        e.preventDefault();
        var form_action = $("#create-item").find("form").attr("action");
        var title = $("#create-item").find("input[name='title']").val();
        var description = $("#create-item").find("textarea[name='description']").val();

        if(title != '' && description != ''){
            $.ajax({
                dataType: 'json',
                type:'POST',
                url: url + form_action,
                data:{title:title, description:description}
            }).done(function(data){
                $("#create-item").find("input[name='title']").val('');
                $("#create-item").find("textarea[name='description']").val('');
                getPageData();
                $(".modal").modal('hide');
                toastr.success('Item Created Successfully.', 'Success Alert', {timeOut: 5000});
            });
        }else{
            alert('You are missing title or description.')
        }

    });


    /* Remove Item */
    $("body").on("click",".remove-item",function(){

        if (confirm("Are you sure want to delete?") == true) {
            var id = $(this).parent("td").data('id');
            var c_obj = $(this).parents("tr");

            $.ajax({
                dataType: 'json',
                type:'POST',
                url: url + 'api/delete.php',
                data:{id:id}
            }).done(function(data){
                c_obj.remove();
                toastr.success('Item Deleted Successfully.', 'Success Alert', {timeOut: 5000});
                getPageData();
            });
        };

    });


    /* Edit Item */
    $("body").on("click",".edit-item",function(){

        var id = $(this).parent("td").data('id');
        var title = $(this).parent("td").prev("td").prev("td").text();
        var description = $(this).parent("td").prev("td").text();


        $("#edit-item").find("input[name='title']").val(title);
        $("#edit-item").find("textarea[name='description']").val(description);
        $("#edit-item").find(".edit-id").val(id);

    });


    /* Updated new Item */
    $(".crud-submit-edit").click(function(e){

        e.preventDefault();
        var form_action = $("#edit-item").find("form").attr("action");
        var title = $("#edit-item").find("input[name='title']").val();
        var description = $("#edit-item").find("textarea[name='description']").val();
        var id = $("#edit-item").find(".edit-id").val();

        if(title != '' && description != ''){
            $.ajax({
                dataType: 'json',
                type:'POST',
                url: url + form_action,
                data:{title:title, description:description,id:id}
            }).done(function(data){
                getPageData();
                $(".modal").modal('hide');
                toastr.success('Item Updated Successfully.', 'Success Alert', {timeOut: 5000});
            });
        }else{
            alert('You are missing title or description.')
        }

    });
});
Step 5 : Create API File

In this step we need to create api file for getting item Data, Add item Data, update item Data and delete item Data. So let's create api file one by one.

Path : api/getData.php
<?php
require 'db_config.php';

$num_rec_per_page = 5;

if (isset($_GET["page"])) { 
  $page  = $_GET["page"]; 
} else {
  $page=1;
};

$start_from = ($page-1) * $num_rec_per_page;

$sqlTotal = "SELECT * FROM items";
$sql = "SELECT * FROM items Order By id desc LIMIT $start_from, $num_rec_per_page"; 

$result = $mysqli->query($sql);

  while($row = $result->fetch_assoc()){

     $json[] = $row;

  }

  $data['data'] = $json;

$result =  mysqli_query($mysqli,$sqlTotal);

$data['total'] = mysqli_num_rows($result);

echo json_encode($data);

?>
Path : api/create.php
<?php

require 'db_config.php';

  $post = $_POST;

  $sql = "INSERT INTO items (title,description) 

    VALUES ('".$post['title']."','".$post['description']."')";

  $result = $mysqli->query($sql);

  $sql = "SELECT * FROM items Order by id desc LIMIT 1"; 

  $result = $mysqli->query($sql);

  $data = $result->fetch_assoc();

echo json_encode($data);

?>
Path : api/update.php
<?php

  require 'db_config.php';

  $id  = $_POST["id"];
  $post = $_POST;

  $sql = "UPDATE items SET title = '".$post['title']."'
    ,description = '".$post['description']."' 
    WHERE id = '".$id."'";

  $result = $mysqli->query($sql);

  $sql = "SELECT * FROM items WHERE id = '".$id."'"; 

  $result = $mysqli->query($sql);

  $data = $result->fetch_assoc();

  echo json_encode($data);

?>
Path : api/delete.php
<?php

 require 'db_config.php';

 $id  = $_POST["id"];

 $sql = "DELETE FROM items WHERE id = '".$id."'";

 $result = $mysqli->query($sql);

 echo json_encode([$id]);

?>

Now we are ready to run our example so run bellow command for quick run:

php -S localhost:8000

Now you can open bellow URL on your browser:

http://localhost:8000

It will help you...

#PHP