CODEWITHSUNDEEP
javascriptajaxmysqlidatatablehtml-table
tsz
tsz

Reputation: 307

Use MySQL results in DataTable using ajax and GET

I have a database that I need to get some rows from, and move them into a datatable. I've gotten it to the point where I'm querying for what I need and returning the data as JSON. I can't seem to get past this, though; I get an error saying the JSON is formatted incorrectly, and the closest I get to a result is once character on it's own row of a table, without the JSON having been parsed at all. Is there something obvious I'm doing wrong? Or am I headed in completely the wrong direction? 

<script>
    $(document).ready(function() {
        $(function (){
            $("#logForm").submit(function() {
                console.log("Sending " + $('#logForm').serialize());
                var logRequest = $.ajax({
                    url: 'logQuery.php',
                    type: 'get',
                    datatype: 'auto',
                    data: $('#logForm').serialize(),
                    success: function(logResponse) {
                        $('#DataTables_Table_0').dataTable( {
                            "bDestroy": true,
                            "bProcessing": true,
                            "bServerSide": true,
                            "aaData": logResponse
                        });
                    }
                });
                logRequest.fail(function(jqXHR, textStatus) {
                    console.log( "Request failed: " + textStatus);
                });
                return false;
            });
        });
    });
</script>

Here's the PHP generating the json:

<?
$q = "SELECT src,dst,calldate,billsec,disposition FROM cdr WHERE calldate >= '$from' AND calldate <= '$to' ORDER BY calldate DESC LIMIT $limit";

$r = mysqli_query($con,$q);
$rowCount = mysqli_num_rows($r);
$n = 1;
while($row = mysqli_fetch_assoc($r)) {
    $resArr[] = $row;
    $n++;
}

echo json_encode($resArr);
?>

Here's the table html:

<table class="table table-striped table-condensed table-bordered bootstrap-datatable datatable dataTable" id="DataTables_Table_0" aria-describedby="DataTables_Table_0_info">
    <thead>
        <tr role="row">
            <th class="sorting" role="columnheader" tabindex="0" aria-controls="DataTables_Table_0" rowspan="1" colspan="1" aria-label="From" style="width: 200px;">From</th>
            <th class="sorting" role="columnheader" tabindex="0" aria-controls="DataTables_Table_0" rowspan="1" colspan="1" aria-label="To" style="width: 200px;">To</th>
            <th class="sorting" role="columnheader" tabindex="0" aria-controls="DataTables_Table_0" rowspan="1" colspan="1" aria-label="Date" style="width: 153px;">Date/Time</th>
            <th class="sorting" role="columnheader" tabindex="0" aria-controls="DataTables_Table_0" rowspan="1" colspan="1" aria-label="Duration" style="width: 157px;">Duration</th>
            <th class="sorting" role="columnheader" tabindex="0" aria-controls="DataTables_Table_0" rowspan="1" colspan="1" aria-label="Disposition" style="width: 157px;">Disposition</th>
            <th class="sorting" role="columnheader" tabindex="0" aria-controls="DataTables_Table_0" rowspan="1" colspan="1" aria-label="Detail" style="width: 317px;">Detail</th>
        </tr>
    </thead>
    <tbody role="alert" aria-live="polite" aria-relevant="all">
        <tr class="odd">
            <td class="center "></td>
            <td class="center "></td>
            <td class="center "></td>
            <td class="center "></td>
            <td class="center ">
                <span class="label label-success"></span>
            </td>
            <td class="center ">
                <a class="btn btn-success" href="#">
                    <i class="halflings-icon zoom-in halflings-icon"></i>           
                </a>
            </td>
        </tr>
        <tr class="even">
            <td class="center "></td>
            <td class="center "></td>
            <td class="center "></td>
            <td class="center "></td>
            <td class="center ">
                <span class="label"></span>
            </td>
            <td class="center ">
                <a class="btn btn-success" href="#">
                    <i class="halflings-icon zoom-in halflings-icon"></i>           
                </a>
            </td>
        </tr>
    </tbody>
</table>

And here's an example of the json being generated:

[{"src":"10062","dst":"18665551234","calldate":"2013-06-30 23:52:29","billsec":"14","disposition":"ANSWERED"},{"src":"10062","dst":"186655551234","calldate":"2013-06-30 23:51:53","billsec":"21","disposition":"ANSWERED"}]

It would help me a lot if I understood better how datatables mapped fields in the JSON string to the table rows/columns - if you could explain that to me I would seriously appreciate it!

Upvotes: 1

Views: 2429

Answers (1)

Yogesh
Yogesh

Reputation: 924

Updated Javascript

$(document).ready(function() {
    $(function (){
        $("#logForm").submit(function() {
            console.log("Sending " + $('#logForm').serialize());
            var logRequest = $.ajax({
                url: 'logQuery.php',
                type: 'get',
                datatype: 'auto',
                data: $('#logForm').serialize(),
                success: function(logResponse) {
                    var datableObj = $('#DataTables_Table_0').dataTable( {
                        "bDestroy": true,
                        "bProcessing": true,
                        "bRetrieve": true,
                        "bServerSide": true
                    });
                    datableObj.fnAddData(logResponse);
                }
            });
            logRequest.fail(function(jqXHR, textStatus) {
                console.log( "Request failed: " + textStatus);
            });
            return false;
        });
    });
 });

For HTML You can remove HTML of TBODY tag completely as Ajax page with return data. You can also remove the additional attribute i.e. except STYLE attribute specified in TH and TABLE tag

Upvotes: 1

Related Questions