Hello, if you used the jQuery Datatable plug-in to display your dynamic data in tabular form on the website, you must display the entire column in the Datatable footer. Then at this point you have the question of how to get SUM or the whole column in Datatable with server-side processing using PHP and Ajax script. In this publication you will find the server-side processing solution Datatable, with which you can call up the sum or the sum of the column data and display it on the website with PHP Ajax and jQuery. You can do this in client-side processing using various callback functions that were used to manipulate Datatable's header data. If you use this type of callback function, you have to make several changes to see the dynamic total of the column.
Here, however, we use Datatable server-side processing to determine the sum or total of the column. For server-side data processing, we calculate the column total in the server-side PHP script and, when using jQuery and the Ajax request, we display the total or total of the columns in the datable footer. The content of the header was displayed in the datatable tag. The tag was used to display the data obtained from the Ajax request in json format and to display the content of the DataTable footer. Here we have the tag of use. This tag was used to display the content of the footer. Then we also show the total or the total of the column that is displayed under the name. Below is the source code of the sum of the columns in DataTable that use server-side processing with PHP Ajax and jQuery.
Here, however, we use Datatable server-side processing to determine the sum or total of the column. For server-side data processing, we calculate the column total in the server-side PHP script and, when using jQuery and the Ajax request, we display the total or total of the columns in the datable footer. The content of the header was displayed in the datatable tag. The tag was used to display the data obtained from the Ajax request in json format and to display the content of the DataTable footer. Here we have the tag of use. This tag was used to display the content of the footer. Then we also show the total or the total of the column that is displayed under the name. Below is the source code of the sum of the columns in DataTable that use server-side processing with PHP Ajax and jQuery.
index.php
This is the main file of this tutorial. In this file we used the jquery Javascript library, the bootstrap library and the jQuery DataTable library. Below this page we have created in the table with id = "order_data". We will initialize jQuery Datatable in the table with the ID attribute value. To display the sum or sum of the column in the column of the footer table, and in this column we have defined an id = "total_order". The sum or total of the column in this column is displayed with the jQuery code.
This file also contains the JQuery code for initializing the JQuery DataTable plug-in. In the JQuery code you see to get dynamic data, we used the Ajax request that was sent to the fetch.php file. To display the total or total of the column, we used the drawCallback function. This function received data from the Ajax request, which we can access via the variable json. Below is the source code of this file.
This file also contains the JQuery code for initializing the JQuery DataTable plug-in. In the JQuery code you see to get dynamic data, we used the Ajax request that was sent to the fetch.php file. To display the total or total of the column, we used the drawCallback function. This function received data from the Ajax request, which we can access via the variable json. Below is the source code of this file.
<html>
<head>
<title>How to Get Sum of Column in Datatable using PHP with Ajax</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 of Column in Datatable using PHP with Ajax</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 received a request from Ajax to get data from the job table. In this file we first have to establish the database connection. After establishing the connection to the database, we defined the column to be sorted in the table. Below this file, we performed a query on selected data to retrieve data from the mysql table. Here we have to split the selection into two parts, the first part of the query is to adjust the number of rows and the full query is used to get filter data from the MySQL database. Here we also calculated the total column data of the order_value table. To send all of the column data to the Ajax request, here we added the master key in the array that was sent to the Ajax request using the json_encode () function in json data. Below is the 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 another publication in DataTable. Here we have explained how the total or total of the column is displayed in the DataTable footer by processing the server side with PHP Script, Ajax and jQuery.
i like it
ReplyDelete