Update Datatable with Db records returned from AJAX Request

Question

I have a very basic HTML Table. The data is from the database. I have now set up a datepicker with a button and as soon I click the button I do a AJAX request to get specific data for a specific date. (To make it easier my query just looks for id=1 as an example). I now want to return the data abnd update the datatable, but unfortunately the data is returned, but not shown.

data    […]
0   {…}
 id 1
 category_id    1
 title  Technology Post One

This above is what gets returned. Am I doing a mistake in the format of the above or how do I update the datatable? I get the following error:

DataTables warning: table id=example - Requested unknown parameter '0' for row 0, column 0

  <?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "api_db";


if(isset($_SERVER['HTTP_X_REQUESTED_WITH']) 
  && !empty($_SERVER['HTTP_X_REQUESTED_WITH']) 
  && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest')
{

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM posts WHERE id = 1";
result = $conn->query($sql);

if ($result->num_rows > 0) {

  while($row = $result->fetch_assoc()) {
    $rows[] = $row;
  }
  } else {
    echo "0 results";
  }
  $conn->close();


$msg = ["data" => $rows];
// handle request as AJAX
echo json_encode($msg); 
exit;
} 
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Practise</title>
    <link href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.css">
   <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
   <script src="http://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

   <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
   <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.20 js/jquery.dataTables.js"></script>
    </head>
    <body>
     <?php
    $sql = "SELECT * FROM posts";
    $result = $conn->query($sql);

    while($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }

    $conn->close();

 ?>
    <table id="example" border="1"><thead>
    <th>id</th>
    <th>title</th> 
 <th>created at
     <p>Date: <input type="text" id="datepicker"><button>Click</button> </p>
 </th>
 </thead>

<?php
foreach ($rows as $value){
  echo '<tr>';
  echo '<td>'.$value['id'].'</td>';
  echo '<td>'.$value['title'].'</td>';
  echo '<td>'.$value['created_at'].'</td>';
  echo '</tr>';
}
echo '</table>'
?> 
 </body>

<script>
$(document).ready(function() {

      $('#example').DataTable({

             "columns": [
                    {"data": "id"},
                    {"data": "author"},
                    {"data": "created_at"}
                ],           
            },            
    );       


    $( "#datepicker" ).datepicker();

    $("button").click(function(e) {
        e.preventDefault();
        $.ajax({
            type: "POST",

            data: { 
                created_at: $("#datepicker").val() 
            },
            success: function(data) {
                $('#example').DataTable().ajax.reload(); 
            },
            error: function(result) {
                alert('error');
            }
        });
    });
} );

I don't really know DataTables, but looking at the code, I can see one potential problem. You call $('#example').DataTable().ajax.reload(); when the ajax request is done, but you haven't configured your DataTable to actually do anything or load any data? What should it `"reload"? Your ajax request/response and your DataTable aren't related in any way. — M. Eriksson
– M. Eriksson, Commented Dec 11, 2019 at 19:44
I think you are right and I figured out that I should name the columns so I have updated the below. It now works when I use the query id=1, so I did the next step, add the from and to date, but then I get the next error. I will update the Post above. — Luka
– Luka, Commented Dec 11, 2019 at 20:48

sagar · Accepted Answer · 2019-12-12 08:21:48Z

1

You can update the js script and create a new php file for ajax call which will return the new datatable. Try out this code it will work -

<script>
  var created_at = ''; 
$(document).ready(function() {

  var datatable = $('#example').DataTable({
        'processing': true,
        'scrollX': true,
        'serverSide': true,
        'serverMethod': 'post',
        'searching' : true,
        'ajax': {
            url:'new-file.php',     
            data: function(data){
                    data.created_at = created_at;
                }
        },
        "columns": [
                {"data": "id"},
                {"data": "author"},
                {"data": "created_at"}
            ],           
  });       


$( "#datepicker" ).datepicker();

$("button").click(function(e) {
    e.preventDefault();
    created_at = $("#datepicker").val();
    datatable.draw();
});
} );
</script>

edited Dec 12, 2019 at 8:21

answered Dec 12, 2019 at 7:06

sagar

946 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luka Over a year ago

Brilliant sagar, Thank you so much. I had to put 'serverSide': false, for it to work and had to instantiate DataTable to be available on start, after a click on the button I destroy that one and then instantiate it again. Sounds weird but this way it was all working in my set up. Thank you so much for your help !

Collectives™ on Stack Overflow

Update Datatable with Db records returned from AJAX Request

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related