I have a table that stores people's data by month and I want to display the data by the current month and when I enter it in the search box, it will still display the information of the entered month.
What I have tried:
I have tried several ways but no success.I tried thinking of 2 methods:
- The first is to add the variable 'mony'(Month-Year) and add the where statement to the SQL statement.
- The second is to add a date variable and use the CURDATE() function. But since I'm new to datatable, I don't know how to write it. We wish everyone help.Thank you everyone
index.php
<?php
$connection = new PDO("mysql:host=localhost;dbname=note_at", "root", "");
$month = '';
$query = "SELECT DISTINCT mony FROM alternate ORDER BY mony ASC";
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
$month .= '<option value="'.$row['mony'].'">'.$row['mony'].'</option>';
}
session_start();
if(isset($_SESSION['id']) && isset($_SESSION['user_name'])){
?>
<!DOCTYPE html>
<html>
<head>
<title>ALTERNATE</title>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<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:#1b2a49;
}
.box
{
width:1270px;
padding:20px;
background-color:#fff;
border:1px solid #ccc;
border-radius:5px;
margin-top:25px;
box-sizing:border-box;
}
.nav-tabs .nav-item .nav-link {
background-color: #0080FF;
color: #FFF;
}
.nav-tabs .nav-item .nav-link.active {
color: #0080FF;
background-color:white;
}
</style>
</head>
<body>
<div class="container box">
<div>
<ul class="nav nav-tabs">
<li class="nav-item">
<a class="nav-link " aria-current="page" href="../index.php">HANDOVER</a>
</li>
<li class="nav-item">
<a class="nav-link active" href="">ALTERNATE</a>
</li>
<li class="nav-item">
<a class="nav-link" href="../record/index3.php">RECORD</a>
</li>
<li class="nav-item">
<a class="nav-link " href="../duty/index4.php">DUTY</a>
</li>
<li class="nav-item">
<a class="nav-link " href="../logout.php">LOGOUT</a>
</li>
</ul>
</div>
<div class="alert alert-secondary" role="alert">
<h1 class="text-center">LIST OF ALTERNATE <p id="hvn"></P></h1>
<script>
var today = new Date();
var date =(today.getMonth()+1)+'-'+today.getFullYear();
document.getElementById("hvn").innerHTML = date;
</script>
</div>
<br />
<div class="table-responsive">
<br />
<div>
<button type="button" name="add" id="add" class="btn btn-info">Add</button>
</div>
<br />
<div id="alert_message"></div>
<table id="user_data" class="table table-bordered table-striped">
<thead>
<tr style="background: #1E90FF">
<th>No</th>
<th>Mã NV</th>
<th>Tên/Name</th>
<th>Shift</th>
<th>Phone Number</th>
<th>中文名</th>
<th>Month-Year</th>
<th>1st</th>
<th>2nd</th>
<th>3rd</th>
<th>4th</th>
<th>5th</th>
<th>6th</th>
<th>7th</th>
<th>8th</th>
<th>9th</th>
<th>10th</th>
<th>11th</th>
<th>12th</th>
<th>13th</th>
<th>14th</th>
<th>15th</th>
<th>16th</th>
<th>17th</th>
<th>18th</th>
<th>19th</th>
<th>20th</th>
<th>21th</th>
<th>22th</th>
<th>23th</th>
<th>24th</th>
<th>25th</th>
<th>26th</th>
<th>27th</th>
<th>28th</th>
<th>29th</th>
<th>30th</th>
<th>31th</th>
<th>#</th>
</thead>
</table>
</div>
</div>
</body>
</html>
<?php
}else{
header("location: ../login.php");
exit();
}
?>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
fetch_data();
function fetch_data()
{
var dataTable = $('#user_data').DataTable({
"processing" : true,
"serverSide" : true,
"order" : [],
"ajax" : {
url:"fetch.php",
type:"POST"
}
});
}
function update_data(id, column_name, value)
{
$.ajax({
url:"update.php",
method:"POST",
data:{id:id, column_name:column_name, value:value},
success:function(data)
{
$('#alert_message').html('<div class="alert alert-success">'+data+'</div>');
$('#user_data').DataTable().destroy();
fetch_data();
}
});
setInterval(function(){
$('#alert_message').html('');
}, 5000);
}
$(document).on('blur', '.update', function(){
var id = $(this).data("id");
var column_name = $(this).data("column");
var value = $(this).text();
update_data(id, column_name, value);
});
$('#add').click(function(){
var html = '<tr>';
html += '<td contenteditable id="data0"></td>';
html += '<td contenteditable id="data1"></td>';
html += '<td contenteditable id="data2"></td>';
html += '<td contenteditable id="data3"></td>';
html += '<td contenteditable id="data4"></td>';
html += '<td contenteditable id="data5"></td>';
html += '<td contenteditable id="data37"></td>';
html += '<td contenteditable id="data6"></td>';
html += '<td contenteditable id="data7"></td>';
html += '<td contenteditable id="data8"></td>';
html += '<td contenteditable id="data9"></td>';
html += '<td contenteditable id="data10"></td>';
html += '<td contenteditable id="data11"></td>';
html += '<td contenteditable id="data12"></td>';
html += '<td contenteditable id="data13"></td>';
html += '<td contenteditable id="data14"></td>';
html += '<td contenteditable id="data15"></td>';
html += '<td contenteditable id="data16"></td>';
html += '<td contenteditable id="data17"></td>';
html += '<td contenteditable id="data18"></td>';
html += '<td contenteditable id="data19"></td>';
html += '<td contenteditable id="data20"></td>';
html += '<td contenteditable id="data21"></td>';
html += '<td contenteditable id="data22"></td>';
html += '<td contenteditable id="data23"></td>';
html += '<td contenteditable id="data24"></td>';
html += '<td contenteditable id="data25"></td>';
html += '<td contenteditable id="data26"></td>';
html += '<td contenteditable id="data27"></td>';
html += '<td contenteditable id="data28"></td>';
html += '<td contenteditable id="data29"></td>';
html += '<td contenteditable id="data30"></td>';
html += '<td contenteditable id="data31"></td>';
html += '<td contenteditable id="data32"></td>';
html += '<td contenteditable id="data33"></td>';
html += '<td contenteditable id="data34"></td>';
html += '<td contenteditable id="data35"></td>';
html += '<td contenteditable id="data36"></td>';
html += '<td><button type="button" name="insert" id="insert" class="btn btn-success btn-xs">Insert</button></td>';
html += '</tr>';
$('#user_data tbody').prepend(html);
});
$(document).on('click', '#insert', function(){
var nost = $('#data0').text();
var manv = $('#data1').text();
var ten = $('#data2').text();
var shift = $('#data3').text();
var phone = $('#data4').text();
var namechina = $('#data5').text();
var mony = $('#data37').text();
var st1 = $('#data6').text();
var st2 = $('#data7').text();
var st3 = $('#data8').text();
var st4 = $('#data9').text();
var st5 = $('#data10').text();
var st6 = $('#data11').text();
var st7 = $('#data12').text();
var st8 = $('#data13').text();
var st9 = $('#data14').text();
var st10 = $('#data15').text();
var st11 = $('#data16').text();
var st12 = $('#data17').text();
var st13 = $('#data18').text();
var st14 = $('#data19').text();
var st15 = $('#data20').text();
var st16 = $('#data21').text();
var st17 = $('#data22').text();
var st18 = $('#data23').text();
var st19 = $('#data24').text();
var st20 = $('#data25').text();
var st21 = $('#data26').text();
var st22 = $('#data27').text();
var st23 = $('#data28').text();
var st24 = $('#data29').text();
var st25 = $('#data30').text();
var st26 = $('#data31').text();
var st27 = $('#data32').text();
var st28 = $('#data33').text();
var st29 = $('#data34').text();
var st30 = $('#data35').text();
var st31 = $('#data36').text();
if(mony != '' )
{
$.ajax({
url:"insert.php",
method:"POST",
data:{nost:nost, manv:manv, ten:ten, shift:shift, phone:phone, namechina:namechina, mony:mony, st1:st1, st2:st2, st3:st3, st4:st4, st5:st5,
st6:st6, st7:st7, st8:st8, st9:st9, st10:st10, st11:st11, st12:st12, st13:st13, st14:st14, st15:st15, st16:st16,
st17:st17, st18:st18, st19:st19, st20:st20, st21:st21, st22:st22, st23:st23, st24:st24, st25:st25, st26:st26, st27:st27,
st28:st28, st29:st29, st30:st30, st31:st31},
success:function(data)
{
$('#alert_message').html('<div class="alert alert-success">'+data+'</div>');
$('#user_data').DataTable().destroy();
fetch_data();
}
});
setInterval(function(){
$('#alert_message').html('');
}, 5000);
}
else
{
alert("Both Fields is required");
}
});
$(document).on('click', '.delete', function(){
var id = $(this).attr("id");
if(confirm("Are you sure you want to remove this?"))
{
$.ajax({
url:"delete.php",
method:"POST",
data:{id:id},
success:function(data){
$('#alert_message').html('<div class="alert alert-success">'+data+'</div>');
$('#user_data').DataTable().destroy();
fetch_data();
}
});
setInterval(function(){
$('#alert_message').html('');
}, 5000);
}
});
});
</script>
fetch.php
<?php
include ("../includes/dbconnect.php");
$columns = array('nost', 'manv', 'ten','shift', 'phone','namechina','mony','st1','st2','st3','st4',
'st5','st6','st7','st8','st9','st10','st11','st12','st13','st14','st15','st16','st17','st18','st19','st20','st21','st22','st23',
'st24','st25','st26','st27','st28','st29','st30','st31');
$query = "SELECT * FROM alternate ";
if(isset($_POST["search"]["value"]))
{
$query .= '
WHERE manv LIKE "%'.$_POST["search"]["value"].'%"
OR ten LIKE "%'.$_POST["search"]["value"].'%" OR shift LIKE "%'.$_POST["search"]["value"].'%" OR mony LIKE "%'.$_POST["search"]["value"].'%"
';
}
if(isset($_POST["order"]))
{
$query .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].'
';
}
else
{
$query .= 'ORDER BY id ASC ';
}
$query1 = '';
if($_POST["length"] != -1)
{
$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$number_filter_row = mysqli_num_rows(mysqli_query($connection, $query));
$result = mysqli_query($connection, $query . $query1);
$data = array();
while($row = mysqli_fetch_array($result))
{
$sub_array = array();
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="nost">' . $row["nost"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="manv">' . $row["manv"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="ten">' . $row["ten"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="shift">' . $row["shift"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="phone">' . $row["phone"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="namechina">' . $row["namechina"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="mony">' . $row["mony"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st1">' . $row["st1"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st2">' . $row["st2"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st3">' . $row["st3"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st4">' . $row["st4"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st5">' . $row["st5"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st6">' . $row["st6"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st7">' . $row["st7"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st8">' . $row["st8"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st9">' . $row["st9"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st10">' . $row["st10"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st11">' . $row["st11"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st12">' . $row["st12"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st13">' . $row["st13"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st14">' . $row["st14"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st15">' . $row["st15"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st16">' . $row["st16"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st17">' . $row["st17"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st18">' . $row["st18"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st19">' . $row["st19"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st20">' . $row["st20"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st21">' . $row["st21"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st22">' . $row["st22"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st23">' . $row["st23"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st24">' . $row["st24"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st25">' . $row["st25"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st26">' . $row["st26"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st27">' . $row["st27"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st28">' . $row["st28"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st29">' . $row["st29"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st30">' . $row["st30"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="st31">' . $row["st31"] . '</div>';
$sub_array[] = '<button type="button" name="delete" class="btn btn-danger btn-xs delete" id="'.$row["id"].'">Delete</button>';
$data[] = $sub_array;
}
function get_all_data($connection)
{
$query = "SELECT * FROM alternate";
$result = mysqli_query($connection, $query);
return mysqli_num_rows($result);
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => get_all_data($connection),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>