How to use ajax.reload()
How to use ajax.reload()
shihoru
Posts: 6Questions: 2Answers: 0
Hello, I have the following code which I want to allow the user to filter the results of the data table based on 2 user inputs, start date and end date. These values will then be sent in ajax.data which then I will take it from the serverScript.php file and create a query for it to be sent to the ssp.class.php file.
Currently, the table loads with no issue however, I would like this button to be reused by the user each time they wish to key in a different value but I am unable to make it work, forcing me to reload the page each time I want a different start and end date. The following contains the codes for the files that are used.
This is the page where the table will be viewed from as well as allowing user input to query for data.
tableview.php
<span>Start date:<input type="text" id="sdate" name="sdate" placeholder="YYYY-MM-DD hh:mm:ss"></span>
<span>End date:<input type="text" id="edate" name="edate" placeholder="YYYY-MM-DD hh:mm:ss"></span>
<span><button type="button" id="loadTable" name="loadTable">Submit</button></span>
<table id="data_table" class="table table-hover display nowrap row-border cell-border hidden">
<thead>
<tr>
<th>Column 1</th>
<th>Column 2</th>
<th>Column 3</th>
<th>Column 4</th>
<th>Column 5</th>
<th>Column 6</th>
<th>Column 7</th>
<th>Column 8</th>
<th>Column 9</th>
<th>Column 10</th>
<th>Column 11</th>
<th>Column 12</th>
<th>Column 13</th>
<th>Column 14</th>
<th>Column 15</th>
<th>Column 16</th>
<th>Column 17</th>
<th>Column 18</th>
<th>Column 19</th>
<th>Column 20</th>
<th>Column 21</th>
<th>Column 22</th>
<th>Column 23</th>
<th>Column 24</th>
<th>Column 25</th>
<th>Column 26</th>
<th>Column 27</th>
<th>Column 28</th>
<th>Column 29</th>
<th>Column 30</th>
<th>Column 31</th>
<th>Column 32</th>
<th>Column 33</th>
<th>Column 34</th>
<th>Column 35</th>
<th>Column 36</th>
<th>Column 37</th>
</tr>
</thead>
</table>
The jQuery one function was used because I was trying to avoid the error of initialising another DataTable. The toggle visibility function is used for other purposes which I hope does not cause an issue to solve this problem.
script.js
$(document).ready(function(){
$("#loadTable").one("click", function()
{
var sdate = $("#sdate").val();
var edate = $("#edate").val();
$("#data_table").removeClass("hidden");
var table = $('#data_table').DataTable({
"scrollX": true,
"fixedHeader": true,
"processing": true,
"serverSide": true,
"bFilter": false,
"ajax": {
"url": "serverScript.php",
"type": "POST",
"data": function(d){
d.sdate = sdate;
d.edate = edate;
}
}
});
$('input.toggle-vis').on( 'click', function (e) {
e.preventDefault();
// Get the column API object
var column = table.column( $(this).attr('data-column') );
// Toggle the visibility
column.visible( ! column.visible() );
} );
});
$("#loadTable").click(function()
{
$('#data_table').DataTable().ajax.reload();
sdate = $("#sdate").val()
edate = $("#edate").val();
table = $('#data_table').DataTable().ajax.reload();
$('input.toggle-vis').on( 'click', function (e) {
e.preventDefault();
// Get the column API object
var column = table.column( $(this).attr('data-column') );
// Toggle the visibility
column.visible( ! column.visible() );
} );
});
});
I have only edited the sample serverScript.php from https://datatables.net/examples/data_sources/server_side to take in values from the POST request and then use these values for the whereAll variable.
serverScript.php
<?php
/*
* DataTables example server-side processing script.
*
* Please note that this script is intentionally extremely simple to show how
* server-side processing can be implemented, and probably shouldn't be used as
* the basis for a large complex system. It is suitable for simple use cases as
* for learning.
*
* See http://datatables.net/usage/server-side for full details on the server-
* side processing requirements of DataTables.
*
* @license MIT - http://datatables.net/license_mit
*/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
if (isset($_POST['sdate']))
{
if ($_POST['sdate'] != '')
{
$sdate = $_POST['sdate'];
}
else
{
$sdate = "2021-10-26 00:00:00";
}
}
else
{
$sdate = "2021-10-26 00:00:00";
}
if (isset($_POST['edate']))
{
if ($_POST['edate'] != '')
{
$edate = $_POST['edate'];
}
else
{
$edate = date("Y-m-d") . " 23:59:59";
}
}
else
{
$edate = date("Y-m-d") . " 23:59:59";
}
// DB table to use
$table = 'data_real';
// Table's primary key
$primaryKey = 'id';
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'c1', 'dt' => 0 ),
array( 'db' => 'c2', 'dt' => 1 ),
array( 'db' => 'c3', 'dt' => 2 ),
array( 'db' => 'c4', 'dt' => 3 ),
array( 'db' => 'c5', 'dt' => 4 ),
array( 'db' => 'c6', 'dt' => 5 ),
array( 'db' => 'c7', 'dt' => 6 ),
array( 'db' => 'c8', 'dt' => 7 ),
array( 'db' => 'c9', 'dt' => 8 ),
array( 'db' => 'c10', 'dt' => 9 ),
array( 'db' => 'c11', 'dt' => 10 ),
array( 'db' => 'c12', 'dt' => 11 ),
array( 'db' => 'c13', 'dt' => 12 ),
array( 'db' => 'c14', 'dt' => 13 ),
array( 'db' => 'c15', 'dt' => 14 ),
array( 'db' => 'c16', 'dt' => 15 ),
array( 'db' => 'c17', 'dt' => 16 ),
array( 'db' => 'c18', 'dt' => 17 ),
array( 'db' => 'c19', 'dt' => 18 ),
array( 'db' => 'c20', 'dt' => 19 ),
array( 'db' => 'c21', 'dt' => 20 ),
array( 'db' => 'c22', 'dt' => 21 ),
array( 'db' => 'c23', 'dt' => 22 ),
array( 'db' => 'c24', 'dt' => 23 ),
array( 'db' => 'c25', 'dt' => 24 ),
array( 'db' => 'c26', 'dt' => 25 ),
array( 'db' => 'c27', 'dt' => 26 ),
array( 'db' => 'c28', 'dt' => 27 ),
array( 'db' => 'c29' => 28 ),
array( 'db' => 'c30', 'dt' => 29 ),
array( 'db' => 'c31', 'dt' => 30 ),
array( 'db' => 'c32', 'dt' => 31 ),
array( 'db' => 'c33', 'dt' => 32 ),
array( 'db' => 'c34', 'dt' => 33 ),
array( 'db' => 'c35', 'dt' => 34 ),
array( 'db' => 'c36', 'dt' => 35 ),
array( 'db' => 'c37', 'dt' => 36 ),
);
// SQL server connection information
$sql_details = array(
'user' => 'root',
'pass' => '',
'db' => 'datacolumns',
'host' => 'localhost'
);
// /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
// * If you just want to use the basic configuration for DataTables with PHP
// * server-side, there is no need to edit below this line.
// */
require( 'ssp.class.php' );
$whereAll = " time >= '" . $sdate . "' AND" . " time <= '" . $edate . "'";
unset($_POST['sdate']);
unset($_POST['edate']);
echo json_encode(
SSP::complex( $_POST, $sql_details, $table, $primaryKey, $columns, null, $whereAll)
);
ssp.class.php remains unchanged as copied from https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php
Thanks for the help.
This question has an accepted answers - jump to answer
Answers
I think you will want to rearrange your code a bit. Something like this:
First use
ajax.datato fetch the values directly from the inputs. Second create the click event to reload the Datatable inside the.one()event handler. This way its create after the first table load.I'm not familiar with PHP so won't be much help there. If you need help with the PHP script please post specific questions.
Kevin
This code is absolutely perfect, thanks.