Wednesday 31 July 2019

How to Calculate Total of Column in Datatable using PHP with Ajax



Hi, If you have use jQuery Datatable plugin for display your dynamic data in tabular format on web page, then there are some you have to required to display total of column in footer of Datatable. So, at that time you have one question arise in your mind how to get SUM or total of column in Datatable with Server-side processing by using PHP script and Ajax. In this post, we you can find the solution of Datatable server-side processing for get the total or SUM of column data and display on web page by using PHP Ajax and jQuery. You can do this things on Client Side processing by using different callback function which has been used for manipulated header data of Datatable And by using these type of callback function you have to do various modification for display dynamic total of column.

But here we will use Datatable Server-side processing for make column sum or total. In Server-side processing of data, we will calculate the total of column at server side php script and by using jQuery and Ajax request we will display total or sum of column in footer of Datatable. In Datatable tag has been used for display content of header, tag has been used for display data which has been get from Ajax request in json format and for display DataTable footer content, here we have use tag. This tag has been used for display footer content. So, here also we will display total or sum of column will be display under tag. Below you can find Source code of Column Sum in DataTable by using Server-side processing with PHP Ajax and jQuery.





index.php


This is the main file of this tutorial. In this file we have use javascript library jquery, Bootstrap library and jQuery DataTable library. Under this page we have create on table with id="order_data". We will initialize jQuery Datatable on table by using id attribute value. For display total or sum of column in footer table column, and in that column we have define one id="total_order". We will display sum or column total under this column by using jQuery code.

Under this file you can also finde jquery code for initialize jQuery DataTable plugin. In jquery Code you can see for fetch dynamic data, we have use Ajax request which has send to fetch.php file. For display Sum or total of column we have used drawCallback function. This function has received data from Ajax request, which we can access by using json variable. Below you can find source code of this file below.




<html>
 <head>
  <title>How to Get SUM with Datatable Server-side-processing in PHP</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
 </head>
 <body>
  <div class="container box">
   <h3 align="center">How to Get SUM with Datatable Server-side-processing in PHP</h3>
   <br />
   <div class="table-responsive">
    <table id="order_data" class="table table-bordered table-striped">
     <thead>
      <tr>
       <th>Customer Name</th>
       <th>Order Item</th>
       <th>Order Date</th>
       <th>Order Value</th>
      </tr>
     </thead>
     <tbody></tbody>
     <tfoot>
      <tr>
       <th colspan="3">Total</th>
       <th id="total_order"></th>
      </tr>
     </tfoot>
    </table>
    <br />
    <br />
    <br />
   </div>
  </div>
 </body>
</html>

<script type="text/javascript" language="javascript" >
 $(document).ready(function(){
  
   var dataTable = $('#order_data').DataTable({
    "processing" : true,
    "serverSide" : true,
    "order" : [],
    "ajax" : {
     url:"fetch.php",
     type:"POST"
    },
    drawCallback:function(settings)
    {
     $('#total_order').html(settings.json.total);
    }
   });

    
  
 });
 
</script>


fetch.php


This file has receive Ajax request for fetch data from order table. In this file first we have make database connection. After making database connection we have define table column for sorting. Under this file we have make select data query for fetch data from mysql table. Here we have divide select in two part, first part query is for get number of fitered row and whole query will be used for fetch filter data from mysql database. Here we have also calculate the total of order_value table column data. For send column total data to Ajax request, here we have add total key in array which has been send to Ajax request in json data by using json_encode() function. Below you can find source code of this file.


<?php

//fetch.php

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

$column = array('order_customer_name', 'order_item', 'order_date', 'order_value');

$query = '
SELECT * FROM tbl_order 
WHERE order_customer_name LIKE "%'.$_POST["search"]["value"].'%" 
OR order_item LIKE "%'.$_POST["search"]["value"].'%" 
OR order_date LIKE "%'.$_POST["search"]["value"].'%" 
OR order_value LIKE "%'.$_POST["search"]["value"].'%" 

';

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY order_id DESC ';
}

$query1 = '';

if($_POST["length"] != -1)
{
 $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $connect->prepare($query);

$statement->execute();

$number_filter_row = $statement->rowCount();

$statement = $connect->prepare($query . $query1);

$statement->execute();

$result = $statement->fetchAll();

$data = array();

$total_order = 0;

foreach($result as $row)
{
 $sub_array = array();
 $sub_array[] = $row["order_customer_name"];
 $sub_array[] = $row["order_item"];
 $sub_array[] = $row["order_date"];
 $sub_array[] = $row["order_value"];

 $total_order = $total_order + floatval($row["order_value"]);
 $data[] = $sub_array;
}

function count_all_data($connect)
{
 $query = "SELECT * FROM tbl_order";
 $statement = $connect->prepare($query);
 $statement->execute();
 return $statement->rowCount();
}

$output = array(
 'draw'    => intval($_POST["draw"]),
 'recordsTotal'  => count_all_data($connect),
 'recordsFiltered' => $number_filter_row,
 'data'    => $data,
 'total'    => number_format($total_order, 2)
);

echo json_encode($output);


?>


This is one more post on DataTable and here we have discuss how to display Sum or Total of column in DataTable Footer by using Server-side processing with PHP Script, Ajax and jQuery.

12 comments:

  1. hi please learn to grab and scrap subtitle from subscene using pure php

    ReplyDelete
  2. hi, thanks for the efforts, my question is how to show the sum of all pages near the sum page?

    ReplyDelete
  3. Please provide code for adding more than one column.... Plzzz plzzz

    ReplyDelete
  4. Nice, what about the printing button, is possible to include the extra row of the total in the print or in the export (excel or pdf)?
    Thanks in advance.

    ReplyDelete
  5. Notice: Undefined index: search in C:\laptop\htdocs\hms\PatientView.php on line 270

    Notice: Trying to access array offset on value of type null in C:\laptop\htdocs\hms\PatientView.php on line 270

    Notice: Undefined index: search in C:\laptop\htdocs\hms\PatientView.php on line 271

    Notice: Trying to access array offset on value of type null in C:\laptop\htdocs\hms\PatientView.php on line 271

    Notice: Undefined index: search in C:\laptop\htdocs\hms\PatientView.php on line 272

    Notice: Trying to access array offset on value of type null in C:\laptop\htdocs\hms\PatientView.php on line 272

    Notice: Undefined index: search in C:\laptop\htdocs\hms\PatientView.php on line 273

    Notice: Trying to access array offset on value of type null in C:\laptop\htdocs\hms\PatientView.php on line 273

    Notice: Undefined index: search in C:\laptop\htdocs\hms\PatientView.php on line 274

    Notice: Trying to access array offset on value of type null in C:\laptop\htdocs\hms\PatientView.php on line 274

    Notice: Undefined index: search in C:\laptop\htdocs\hms\PatientView.php on line 275

    Notice: Trying to access array offset on value of type null in C:\laptop\htdocs\hms\PatientView.php on line 275

    Notice: Undefined index: search in C:\laptop\htdocs\hms\PatientView.php on line 276

    Notice: Trying to access array offset on value of type null in C:\laptop\htdocs\hms\PatientView.php on line 276

    Notice: Undefined index: search in C:\laptop\htdocs\hms\PatientView.php on line 277

    Notice: Trying to access array offset on value of type null in C:\laptop\htdocs\hms\PatientView.php on line 277

    Notice: Undefined index: search in C:\laptop\htdocs\hms\PatientView.php on line 278

    Notice: Trying to access array offset on value of type null in C:\laptop\htdocs\hms\PatientView.php on line 278

    Notice: Undefined index: length in C:\laptop\htdocs\hms\PatientView.php on line 297

    Notice: Undefined index: start in C:\laptop\htdocs\hms\PatientView.php on line 299

    Notice: Undefined index: length in C:\laptop\htdocs\hms\PatientView.php on line 299

    Notice: Undefined index: draw in C:\laptop\htdocs\hms\PatientView.php on line 345
    {"draw":0,"recordsTotal":15,"recordsFiltered":0,"data":[],"total":"0.00"}

    ReplyDelete
  6. sir can we add delete and edit button in this table

    ReplyDelete
  7. sir can we add delete and edit button in table?

    ReplyDelete
  8. Sir How can calculate column in date range table, please solve this.

    ReplyDelete
  9. Thanks for your efforts, How to calculate sum of many columns buy using symfony5 and ajax please someone can help me, I've broken in my project.

    ReplyDelete
  10. Thank you guys ! You make me smile

    ReplyDelete