Click here to Skip to main content
15,892,298 members
Search within:


Datatable PHP : display information by current month - year
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
Add a Solution
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    
See MySQL WHERE Clause[^].
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise
Privacy
Cookies
Terms of Use
Last Updated 22 Dec 2022
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web03 2.8:2024-04-02:1

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