I have a webpage (php) that fetches data from mysql as in the figure
I managed to make it refresh but it reloads the whole page. But I just want it to refresh the database every second without reloading the whole page or a button. I understand that I have to use AJAX and JQuery, but I didn't understand how. Here is my php code for the two php files fetch.php and index.php.
If any body knows how that would be done I much appreciate it!
<?php
//fetch.php
$connect = mysqli_connect("localhost", "sid", "", "python");
$columns = array('timestamp', 'message', 'topic', 'start', 'End');
$query = "SELECT * FROM messages WHERE ";
if($_POST["is_date_search"] == "yes")
{
$query .= 'timestamp BETWEEN "'.$_POST["start_date"].'" AND "'.$_POST["end_date"].'" AND ';
}
if(isset($_POST["search"]["value"]))
{
$query .= '
(message LIKE "%'.$_POST["search"]["value"].'%"
OR topic LIKE "%'.$_POST["search"]["value"].'%")
';
}
if(isset($_POST["order"]))
{
$query .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].'
';
}
else
{
$query .= 'ORDER BY timestamp DESC ';
}
$query1 = '';
if($_POST["length"] != -1)
{
$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));
$result = mysqli_query($connect, $query . $query1);
$data = array();
while($row = mysqli_fetch_array($result))
{
$sub_array = array();
$sub_array[] = $row["timestamp"];
$sub_array[] = $row["topic"];
$sub_array[] = $row["message"];
$sub_array[] = $row["start"];
$sub_array[] = $row["End"];
$data[] = $sub_array;
}
function get_all_data($connect)
{
$query = "SELECT * FROM messages";
$result = mysqli_query($connect, $query);
return mysqli_num_rows($result);
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => get_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<html>
<head>
<!-- <meta http-equiv="refresh" content="10"> -->
<title> Automated System</title>
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.15/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/css/bootstrap-datepicker.css" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/js/bootstrap-datepicker.js"></script>
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:1270px;
padding:20px;
background-color:#fff;
border:1px solid #ccc;
border-radius:5px;
margin-top:25px;
}
</style>
</head>
<body>
<div class="container box">
<h1 align="center"> Automated System</h1>
<br />
<form method="post" action="export.php" align="center">
<input type="submit" name="export" value="CSV Export" class="btn btn-success" />
</form>
<br />
<div class="table-responsive">
<br />
<div class="row">
<div class="input-daterange">
<div class="col-md-4">
<input type="text" name="start_date" id="start_date" class="form-control" />
</div>
<div class="col-md-4">
<input type="text" name="end_date" id="end_date" class="form-control" />
</div>
</div>
<div class="col-md-4">
<input type="button" name="search" id="search" value="Search" class="btn btn-info" />
</div>
</div>
<br />
<table id="order_data" class="table table-bordered table-striped">
<thead>
<tr>
<th>Error Reported </th>
<th>Board No.</th>
<th>Status</th>
<th>Repairing Started</th>
<th>Finished Repairing</th>
</tr>
</thead>
</table>
</div>
</div>
</body>
</html>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
$('.input-daterange').datepicker({
todayBtn:'linked',
format: "yyyy-mm-dd",
autoclose: true
});
fetch_data('no');
function fetch_data(is_date_search, start_date='', end_date='')
{
var dataTable = $('#order_data').DataTable({
"processing" : true,
"serverSide" : true,
"order" : [],
"ajax" : {
url:"fetch.php",
type:"POST",
data:{
is_date_search:is_date_search, start_date:start_date, end_date:end_date
}
}
});
}
$('#search').click(function(){
var start_date = $('#start_date').val();
var end_date = $('#end_date').val();
if(start_date != '' && end_date !='')
{
$('#order_data').DataTable().destroy();
fetch_data('yes', start_date, end_date);
}
else
{
alert("Both Date is Required");
}
});
});
</script>
