Using DataTables to a table with content from ajax

Question

I am working on this program. The program displays the topics under a specific subject.

My main question is, will the DataTable work in this kind of setup?

This is the code in HTML.

<div class="table-responsive" id="subject_container">
                <table id="tbl_subject" class="table table-striped">
                    <thead>
                        <tr>
                            <th>Chapter</th>
                            <th>Topic</th>
                            <th>Content</th>
                        </tr>
                    </thead>
                    <tbody id="disp_topics"></tbody>
                </table>
            </div>

This is the code snippet in jquery:

$(function(){
  //this is only for simplification. Subjectid will be coming from a select input.
  let subjectid = 1;
  get_topics(subjectid);

  //this part right here. If this cannot work, is there a way to make it work?
  $('#tbl_subject).DataTable();
});

function get_topics(subjectid){
  $.ajax({
    url: 'includes/subject_handler.php',
    method: 'POST',
    data: {
      key: 'get_topics',
      subjectid: subjectid
    },
    success: function(data){
      $('#disp_topics).html(data);
    }
  });

This is the code of includes/subject_handler.php: I am using keys since there are other tasks handled by this same script file. Thank you for understanding.

if($_POST['key'] == 'get_topics'){
   $subjectid = $_POST['subjectid'];
   $data = '';

   if(!empty($subjectid)){
      $sql = "SELECT topic.subjectid, chapter, topic, content FROM topic INNER JOIN subject
             ON subject.subjectid=topic.subjectid WHERE topic.subjectid = ?";
      $stmt=$db->prepare($sql);
      $stmt->bindValue(1, $subjectid);
      $stmt->execute();

      if($stmt->rowCount() > 0){
         $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

         foreach($rows as $row){
            $data .= '<tr>
                        <td>'.$row['chapter'].'</td>
                        <td>'.$row['topic'].'</td>
                        <td>'.$row['content'].'</td>
                     </tr>';
         }

         exit($data);
      }
   }

}

Answer 1

you can use ajax attribute of data table.

$('#tbl_subject').DataTable( {
    processing: true,
    serverSide: true,
    serverMethod: 'post',
    ajax: 'includes/subject_handler.php'
    columns: [
            {data: 'chapter', name: 'chapter'},
            {data: 'topic', name: 'topic'},
            {data: 'content', name: 'content'},
    ]
} );

And for backend if you need to access search value you can do like this,

$requestData = $_REQUEST;
$requestData['search']['value']

In your case this is how to use it in where close.

$sql .= " AND ( chapter LIKE '" . $requestData['search']['value'] . "%' ";
$sql .= " OR topic LIKE '" . $requestData['search']['value'] . "%' ";
$sql .= " OR content LIKE '" . $requestData['search']['value'] . "%' )";

And if you need to get sorting data you can get like this,

$requestData['order'][0]['column'] // column name which user sorts
$requestData['order'][0]['dir'] // derection (ASC/DESC)

In your case it will be like this and remember to use $requestData['start'] and $requestData['length'] for pagination to work.

$sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "   LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";

You can include this search value and order values inside your sql query inside backend.

And finally you need format the data before send to front end.

    $json_data = array(
        "draw" => intval($requestData['draw']),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
        "recordsTotal" => intval($totalData),  // total number of records
        "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
        "data" => $data   // total data array
    );
    echo json_encode($json_data);  

This is much simpler than looks. Need to get total records count from query, and total filtered record count from another query and dataset in json.

I have added previous sql example only to explain. For preventing SQL injection you have to use Prepared Statements.

$sql .= " AND ( chapter LIKE ? OR topic LIKE ? OR content LIKE ?";
$params = array($requestData['search']['value'] . "%", $requestData['search']['value'] . "%", $requestData['search']['value'] . "%");
$stmt = $handle->prepare($sql);
$stmt->execute($params);

And you need to do the same for ORDER BY and LIMIT part of the query.

and inside this tutorial there is a nice example how it works on backend.

You can send data to back end also like this.

       ajax: {
            url: "Your url",
            data: function (d) {
                d.var1= 'var 1';
                d.var2= 'var 2';
            }
        },

And access the data in back end like this.

$var1 = $_REQUEST['var1'];
$var2 = $_REQUEST['var2'];

And be careful not to use start and end (E.g.:- d.start= 'var 1';) to pass data to back end since they are used for pagination. This link has a nice example on this.