Use MySQL results in DataTable using ajax and GET

Question

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!

Yogesh · Accepted Answer · 2013-07-04 10:26:55Z

1

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

answered Jul 4, 2013 at 10:26

Yogesh

9121 gold badge8 silver badges21 bronze badges

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

4 Comments

tsz Over a year ago

Sorry for the delay in response; been out of town. I'll give this a shot today.

tsz Over a year ago

This didn't work; I'm still getting each character of the response inserted into the first row of the table, and nothing else. It's like the json response is not being parsed, and it's just treating it like an array...

tsz Over a year ago

Got this working after changing the way i was formatting the json response. Thanks for the help.

Yogesh Over a year ago

@lorsungcu Glad to know that it served the purpose :)

Collectives™ on Stack Overflow

Use MySQL results in DataTable using ajax and GET

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related