Mehedi Hasan's BLog

Sunday, April 21, 2019

Creative code : Jquery Ajax CRUD Operations in PHP

Hi Guys,

In this tutorial, i would like to show you how to create add edit delete and pagination operations using jquery ajax in php. You have to just follow 4 step to create php crud operation using ajax/jquery with bootstrap. I also added jquery validation using validatorjs plugin.

In this post, i crud with i also created pagination, notification and also validation in crud operation. When you will add new item then show you "item created successfully" notification. So just follow listed few step and you will get crud app like as below screen shot:

Content Overview

Step 1: Create Database and Table Configuration

Step 2: Create Index Page

Step 3: Create CRUD JS File

Step 4: Create AJAX File

you can implement crud application from scratch, so no worry if you can implement through bellow simple step. After create successful example, you will find layout as bellow:

Step 1: Create Database and Table Configuration

first of all, we should create database and items table. so let's create database i did create "h_blog" database and "items" table inside that database. so you can create database as you want but you have to create "items" table if you are doing from scratch. so create "items" table using following mysql query:

Items Table Query:


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,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=63 ;

Ok, let's proceed this way, we are doing from scratch so we require to create database configuration file that way we can use that file in many other file. so let's create api directory and create db_config.php file in api directory and put bellow code:

api/db_config.php


<?php

  

    $hostName = "localhost";

    $username = "root";

    $password = "root";

    $dbname = "blog2";

 

    $mysqli = new mysqli($hostName, $username, $password, $dbname);

 

?>

In Above file please make sure to check your database configuration because could problem you find somewhere. that's way i tell you check it two times. It was just for your kind information.

Step 2: Create Index Page

Ok, now we also require to create index.php file in our root directory. In this file i added "url" variable in js for site root URL. You can update also with your site URL. so let's create 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" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">

    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.0/jquery.js"></script>

    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.0.0-alpha/js/bootstrap.min.js"></script>

    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/twbs-pagination/1.3.1/jquery.twbsPagination.min.js"></script>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/1000hz-bootstrap-validator/0.11.5/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">



    <script type="text/javascript">

        var url = "http://localhost:8000/";

    </script>



    <script src="/js/item-ajax.js"></script>

</head>

<body>



    <div class="container">

        <div class="row">

            <div class="col-lg-12 margin-tb">                    

                <div class="pull-left">

                    <h2>PHP Jquery Ajax CRUD Example</h2>

                </div>

                <div class="pull-right">

                <button type="button" class="btn btn-success" data-toggle="modal" data-target="#create-item">

                      Create Item

                </button>

                </div>

            </div>

        </div>



        <table class="table table-bordered">

            <thead>

                <tr>

                <th>Title</th>

                <th>Description</th>

                <th width="200px">Action</th>

                </tr>

            </thead>

            <tbody>

            </tbody>

        </table>



        <ul id="pagination" class="pagination-sm"></ul>



        <!-- 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">

                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>

                <h4 class="modal-title" id="myModalLabel">Create Item</h4>

              </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">

                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>

                <h4 class="modal-title" id="myModalLabel">Edit Item</h4>

              </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 3: Create CRUD JS File

In this step we will create jquery file and write ajax request code on it. So first create js folder on your root directory and then create item-ajax.js file on it.

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">Edit</button> ';

        rows = rows + '<button class="btn btn-danger remove-item">Delete</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(){

    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 4: Create AJAX File

In this step we require 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.

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



?>

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



?>

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



?>

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



?>

reference : https://itsolutionstuff.com/post/jquery-ajax-crud-operations-in-phpexample.html

Creative code : jQuery Ajax X-editable bootstrap plugin to update records in PHP Example

X-editable is jquery powerful plugin and it's allows you to create editable elements on your page using jquery ajax. X-editable library you can use with only bootstrap, jquery-ui and jquery. X-editable provide us edit inline element using text box, textarea, select, date, datetime, dateui, wysihtml5, select2, typeahead etc. X-editable plugin also provide client side validation. X-editable is giving you good layout if you are using with bootstrap. In this example we will use X-editable with bootstrap plugin.

In this post, i will share with you how to update records using jquery ajax with PHP MySQL. We will create "users" table and display "users" table records. we can simply modify that records using X-editable plugin. It seems little bit complected but it's more amazing.

So you have to simple follow few step to do this, after you will get bellow preview:


CREATE TABLE `users` (

  `id` int(10) UNSIGNED NOT NULL,

  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


<?php



define (DB_USER, "root");

define (DB_PASSWORD, "root");

define (DB_DATABASE, "test");

define (DB_HOST, "localhost");



$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);


<!DOCTYPE html>

<html>

<head>

    <title>jQuery Ajax X-editable bootstrap plugin to update records in PHP Example</title>



    <!-- Latest compiled and minified CSS -->

    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">

    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>

    <link href="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.0/bootstrap3-editable/css/bootstrap-editable.css" rel="stylesheet"/>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.0/bootstrap3-editable/js/bootstrap-editable.min.js"></script>



</head>

<body>



<div class="container">

    <h3>jQuery Ajax X-editable bootstrap plugin to update records in PHP Example</h3>

    <table class="table table-bordered">

        <tr>

            <th>Name</th>

            <th>Email</th>

            <th width="100px">Action</th>

        </tr>



        <?php

        require('config.php');



        $sql = "SELECT * FROM users";

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



        while($user = $users->fetch_assoc()){

        ?>

            <tr>

                <td><a href="" class="update" data-name="name" data-type="text" data-pk="<?php echo $user['id'] ?>" data-title="Enter name"><?php echo $user['name'] ?></a></td>

                <td><a href="" class="update" data-name="email" data-type="email" data-pk="<?php echo $user['id'] ?>" data-title="Enter email"><?php echo $user['email'] ?></a></td>

                <td><button class="btn btn-danger btn-sm">Delete</button></td>

            </tr>

        <?php } ?>

    </table>

</div> <!-- container / end -->



</body>

<script type="text/javascript">

    $('.update').editable({

           url: '/update.php',

           type: 'text',

           pk: 1,

           name: 'name',

           title: 'Enter name'

    });

</script>

</html>


<?php



require('config.php');



if(isset($_POST)){

 $sql = "UPDATE users SET ".$_POST['name']."='".$_POST['value']."' WHERE id=".$_POST['pk'];

 $mysqli->query($sql);

 echo 'Updated successfully.';

}



?>

reference : https://itsolutionstuff.com/post/jquery-ajax-x-editable-bootstrap-plugin-to-update-records-in-php-exampleexample.html

Creative code : Delete Multiple Rows using Checkbox.


<!DOCTYPE html>

<html>

<head>

    <title>how to delete multiple records using checkbox in codeigniter - itsolutionstuff.com</title>

    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />

    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

</head>

<body>

<div class="container">

<div class="row">

    <div class="col-lg-12 margin-tb">

        <div class="pull-left">

            <h2>how to delete multiple records using checkbox in codeigniter - itsolutionstuff.com</h2>

        </div>

    </div>

</div>

<button style="margin-bottom: 10px" class="btn btn-primary delete_all" data-url="/itemDelete">Delete All Selected</button>

 

<table class="table table-bordered" style="margin-top:20px">

 

  <thead>

      <tr>

          <th width="50px"><input type="checkbox" id="master"></th>

          <th>Title</th>

          <th>Description</th>

      </tr>

  </thead>

 

  <tbody>

   <?php foreach ($data as $item) { ?>      

      <tr>

          <td><input type="checkbox" class="sub_chk" data-id="<?php echo $item->id; ?>"></td>

          <td><?php echo $item->title; ?></td>

          <td><?php echo $item->description; ?></td>

      </tr>

   <?php } ?>

  </tbody>



</table>

</div>

<script type="text/javascript">

    $(document).ready(function () {

 

        $('#master').on('click', function(e) {

         if($(this).is(':checked',true))  

         {

            $(".sub_chk").prop('checked', true);  

         } else {  

            $(".sub_chk").prop('checked',false);  

         }  

        });

        $('.delete_all').on('click', function(e) {

 

            var allVals = [];  

            $(".sub_chk:checked").each(function() {  

                allVals.push($(this).attr('data-id'));

            });  

 

            if(allVals.length <=0)  

            {  

                alert("Please select row.");  

            }  else {  

 

                var check = confirm("Are you sure you want to delete this row?");  

                if(check == true){  

 

                    var join_selected_values = allVals.join(","); 

 

                    $.ajax({

                        url: $(this).data('url'),

                        type: 'POST',

                        data: 'ids='+join_selected_values,

                        success: function (data) {

                          console.log(data);

                          $(".sub_chk:checked").each(function() {  

                              $(this).parents("tr").remove();

                          });

                          alert("Item Deleted successfully.");

                        },

                        error: function (data) {

                            alert(data.responseText);

                        }

                    });

 

                  $.each(allVals, function( index, value ) {

                      $('table tr').filter("[data-row-id='" + value + "']").remove();

                  });

                }  

            }  

        });

    });

</script>

 

</body>

</html>

Mehedi Hasan's BLog

Sunday, April 21, 2019

Creative code : Jquery Ajax CRUD Operations in PHP

Creative code : jQuery Ajax X-editable bootstrap plugin to update records in PHP Example

Creative code : Delete Multiple Rows using Checkbox.

PostgreSQL (postgre) Setup on macOS 12 (MacBook Pro 2016) Without Homebrew setup process

Search This Blog