Fetch data from mysql database into datatable using jquery

Question

I have developed a mysql database and a php code. In php code i am using jQuery (ajax call) to fetch the data from the database. In html file i have printed datatable's table head only. Rest data i want to fetch from the database. code is given:

HTML CODE:

<div class="container box">
     <div class="table-responsive">
            <div id="alert_message"></div>
            <table id="example" class="display">
               <thead>
                  <tr>
                     <th>Student ID</th>
                     <th>Student Name</th>
                     <th>Email ID</th>
                     <th>Mobile</th>
                     <th>Status</th>
                  </tr>
               </thead>
            </table>
         </div>
      </div>

jQuery CODE:

<script>
    $(document).ready(function() {
        $('#example').dataTable({
            "processing": true,
            "serverSide": true,
            "ajax": {
                "url": "fetch.php",
                "type": "GET",
                "datatype": "json"
            }
        });
    });
</script>

fetch.php

<?php
$connect = mysqli_connect("localhost","root","","lib");
$sql = "SELECT StudentId, FullName, EmailId, MobileNumber, Status  FROM tblstudents";
$result = mysqli_query($connect,$sql);

$json_array = array();

while ($row = mysqli_fetch_assoc($result)) 
{
    $json_array[] = $row;
}

echo json_encode($json_array);
?>

Still the data is not printed in the datatable. What changes are necessary in jQuery?

check this if useful stackoverflow.com/questions/46444582/… — Bhushan Kawadkar
– Bhushan Kawadkar, Commented Aug 7, 2018 at 8:39
have you checked that the returned JSON is as you expected, and is in a format expected by the plugin? Are there any errors in the PHP logs, or in your console? You need to do some basic debugging, by the looks of it... — ADyson
– ADyson, Commented Aug 7, 2018 at 9:35
I have checked the json respond. its responding all the data from the database according to the sql query correctly — Deepak Tomar
– Deepak Tomar, Commented Aug 7, 2018 at 10:06
Ok but is that what DataTables is expecting? You didn't answer the second part of my question. I would imagine it expects data in a particular format? What about error messages or warnings? — ADyson
– ADyson, Commented Aug 7, 2018 at 10:14

Lahiru Madhusanka · Accepted Answer · 2018-08-07 11:45:08Z

0

In jQuery Datatables, not only you can fetch data from ajax call. you can manage page ordering, searching as well.

first of all you have to modify your javascript like this.

$(document).ready(function() {
        $('#example').dataTable({
            "bProcessing": true,
            "serverSide": true,
            "ajax": {
                "url": "fetch.php",
                "type": "post"
            },
                error: function () {  // error handling code
                    $("#example").css("display", "none");
                }
        });
    });

Then as you can see in the browsers Developer Tools -> Network tap, all the parameters that wanted for searching, sorting and ordering will be pass trough the ajax call.

you can see it by print_r($_POST) in the ajax page (in your case fetch.php) and viewing it on ajax respond in the browsers Developer Tools -> Network tap.

you can collect all of that as follows,

    $order_by = $_POST['order']; // This array contains order information of clicked column
    $search = $_POST['search']['value']; // This array contains search value information datatable
    $start = $_POST['start']; // start limit of data
    $length = $_POST['length']; // end limit of data
    $order_type = $order_by[0]['dir'];
    if ($order_by[0]['column']==0){
        $order_column = "table_column_name_of_the_first_column_in_the_datatable";
    } elseif ($order_by[0]['column']==1){
        $order_column = "table_column_name_of_the_second_column_in_the_datatable";
    }

then you can construct the query as follows,

if ($search!='' || $search!=NULL) {
    $str = "WHERE column_name_to_search LIKE '%$search%' OR column_name_to_search LIKE '%$search%' ";
} else {
    $str = "";
}
$data[][] = array(); 
$i = 0;
$sql = $connection->query("SELECT * FROM your_table $str ORDER BY $order_column $order_type LIMIT $start,$length");
while ($row_1 = $sql->fetch_assoc()){
    $data[$i] = array($row_1['column_1'],$row_1['column_2']);
    $i++;
}

then to get total record count you can do as follows,

$sql_2 = $connection->query("SELECT COUNT(*) AS all_count FROM your_table $str");
$row_2 = $sql_2->fetch_assoc();
$totalRecords = $row_2['all_count'];
if ($totalRecords==0){
    $data = array();
}

finally construct the json out put that will be sent to the front view.

$json_data = array(
    "draw"            => intval( $_POST['draw'] ),
    "recordsTotal"    => intval( $totalRecords ),
    "recordsFiltered" => intval($totalRecords),
    "data"            => $data   // total data array
);
$json_data = json_encode($json_data);
echo $json_data;

That will work.

edited Aug 7, 2018 at 11:45

answered Aug 7, 2018 at 9:42

Lahiru Madhusanka

2704 silver badges14 bronze badges

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

13 Comments

Deepak Tomar Over a year ago

you have written 5 para of coding. where would be the papa-2,3 and 4 would be placed? In fetch.php or in the same file where jQuery is written?

Lahiru Madhusanka Over a year ago

obviously, in fetch.php except that javascript.

Deepak Tomar Over a year ago

no its not working. its giving too much errors pls give some other solution

Lahiru Madhusanka Over a year ago

did you replace column names and table names in the code?

Lahiru Madhusanka Over a year ago

what is the error you getting? do you familiar working with ajax based developments?

|

Wick_ · Accepted Answer · 2019-04-06 03:08:19Z

0

I know this is really late, but I used the exact same code as you (thanks for that by the way!), and what made it work for me is to simply add to the JQuery code:

dataSrc = '' (after url = '...')

so that DataTables knows that it's loading an array. Putting that in made the code work fine!

answered Apr 6, 2019 at 3:08

Wick_

332 silver badges5 bronze badges

Collectives™ on Stack Overflow

Fetch data from mysql database into datatable using jquery

2 Answers 2

13 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related