Click here to Skip to main content
15,903,175 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
<?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 != ''  )//&& ten != ''
   {
    $.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");
   }
  });
  
  //click delete
  $(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
<?php
//fetch.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);

?>
Posted
Comments
Richard Deeming 22-Dec-22 6:13am    
<th>21th</th>

NB: Your suffixes for 21, 22, 23, and 31 are wrong. 21 and 31 should be st, not th; 22 should be nd; and 23 should be rd.
Richard MacCutchan 22-Dec-22 6:15am    
You just need a where clause like: WHERE month = ?. And add the month value as a parameter to the execute statement.
Thang Đai 22-Dec-22 20:29pm    
Can you show me to add Where month = ? where is it, i have added a few places but no success
Richard MacCutchan 23-Dec-22 4:39am    
Thang Đai 23-Dec-22 4:55am    
This one i know , but i mean when i add the where statement to the select statement i get the error : DataTables warning: table id=user_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900