CODEWITHSUNDEEP
phpmysqldatatables
RRg
RRg

Reputation: 123

Populate data in datatable from MySQL: Php Ajax

I'm trying to retrieve the data from MySQL database and push it into datatable, but the data table is not being populated with the data.

I'm able to get the data in the backend when I console log it, but I'm not being able to push it into datatable.

Any help will be appriciated

index.php

<!DOCTYPE html>
<html>
    <head>
        <script language="JavaScript" src="https://code.jquery.com/jquery-3.5.1.js" type="text/javascript"></script>
        <script language="JavaScript" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js" type="text/javascript"></script>
        <script language="JavaScript" src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap4.min.js" type="text/javascript"></script>
        <script language="JavaScript" src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.js" type="text/javascript"></script>
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css" type="text/css">
        <link rel="stylesheet" href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap.min.css" type="text/css">
        <link rel="stylesheet" href="https://nightly.datatables.net/css/dataTables.bootstrap4.min.css" type="text/css">
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
    </head>
    <style>
        tfoot {
            display: table-header-group;
        }
    </style>
    <body>

        <table id="example" class="display" style="width:100%">
            <thead>
                <tr>
                    <th scope="col">control_id</th>
                    <th scope="col">sample_id</th>
                    <th scope="col">sample_nm</th>
                    <th scope="col">sample_type_nm</th>
                    <th scope="col">sample_type_with_nm</th>
                    <th scope="col">variant_type</th>
                    <th scope="col">output_location</th>
                    <th scope="col">object_type</th>
                    <th scope="col">output_type</th>
                    <th scope="col">oncotree_nm</th>
                    <th scope="col">status</th>
                    <th scope="col">pipeline_status</th>
                    <th scope="col">oncsuite_status</th>
                    <th scope="col">control_completion_status</th>
                    <th scope="col">send_oncsuite_status</th>
                    <th scope="col">api_log_creation_dt</th>
                </tr>
            </thead>
            <tbody>
            </tbody>
            <tfoot>
            <tr>
                <th>control_id</th>
                <th>sample_id</th>
                <th>sample_nm</th>
                <th>sample_type_nm</th>
                <th>sample_type_with_nm</th>
                <th>variant_type</th>
                <th>output_location</th>
                <th>object_type</th>
                <th>output_type</th>
                <th>oncotree_nm</th>
                <th>status</th>
                <th>pipeline_status</th>
                <th>oncsuite_status</th>
                <th>control_completion_status</th>
                <th>send_oncsuite_status</th>
                <th>api_log_creation_dt</th>
            </tr>
            </tfoot>
        </table>

        <script>
            $(document).ready(function() {
                $('#example').DataTable( {
                    "processing": true,
                    "serverSide": true,
                    "ajax": {
                        "url": "../model/getData.php",
                        "type": "POST"
                    },
                    "columns": [
                        {data: 'control_id'},
                        {data: 'sample_id'},
                        {data: 'sample_nm'},
                        {data: 'sample_type_nm'},
                        {data: 'sample_type_with_nm'},
                        {data: 'variant_type'},
                        {data: 'output_location'},
                        {data: 'object_type'},
                        {data: 'output_type'},
                        {data: 'oncotree_nm'},
                        {data: 'status'},
                        {data: 'pipeline_status'},
                        {data: 'oncsuite_status'},
                        {data: 'ontrol_completion_status'},
                        {data: 'send_oncsuite_status'},
                        {data: 'api_log_creation_dt'}
                    ]
                } );

            } );
                
        </script>
    </body>

</html>

getData.php

<?php
include('connection.php');

class getData
{
    public function selectData()
    {
        $output = array();
        $pdo = Db::getInstance();
        $query = "SELECT control_id, sample_id, sample_nm, sample_type_nm, sample_type_with_nm, variant_type, output_location, object_type, output_type, oncotree_nm, status, pipeline_status, oncsuite_status, control_completion_status, send_oncsuite_status, api_log_creation_dt FROM vonc_wes.view_control_variant_context_new";
        $stmt = $pdo->prepare($query);
        try {
            $stmt->execute();
        } catch(PDOException $e) {
            echo $e;
        }
        $rows = $stmt->fetchAll();
        foreach ($rows as $row) {

            $subarray = array();
            $subarray["control_id"] = $row[0];
            $subarray["sample_id"] = $row[1];
            $subarray["sample_nm"] = $row[2];
            $subarray["sample_type_nm"] = $row[3];
            $subarray["sample_type_with_nm"] = $row[4];
            $subarray["variant_type"] = $row[5];
            $subarray["output_location"] = $row[6];
            $subarray["object_type"] = $row[7];
            $subarray["output_type"] = $row[8];
            $subarray["oncotree_nm"] = $row[9];
            $subarray["status"] = $row[10];
            $subarray["pipeline_status"] = $row[11];
            $subarray["oncsuite_status"] = $row[12];
            $subarray["ontrol_completion_status"] = $row[13];
            $subarray["send_oncsuite_status"] = $row[14];
            $subarray["api_log_creation_dt"] = $row[15];
            array_push($output, $subarray);
        }
        $outputJson = json_encode($output);
        echo $outputJson;
    }
}

$getallData             = new getData();
$getallData->selectData();

The output from getData.php is

[{"control_id":"1","sample_id":"8043","sample_nm":"ST","sample_type_nm":"NORMAL","sample_type_with_nm":":NORMAL","variant_type":"gatk4","output_location":"atic.tsv","object_type":"tsv","output_type":"gatk4","oncotree_nm":"NORMA","status":"2","pipeline_status":"2","oncsuite_status":"0","ontrol_completion_status":"0","send_oncsuite_status":"400","api_log_creation_dt":"2020-04-1"},{"control_id":"12","sample_id":"843","sample_nm":"ST","sample_type_nm":"NORMAL","sample_type_with_nm":":NORMAL","variant_type":"gatk4","output_location":"atic.tsv","object_type":"tsv","output_type":"gatk4","oncotree_nm":"NORMA","status":"2","pipeline_status":"2","oncsuite_status":"0","ontrol_completion_status":"0","send_oncsuite_status":"400","api_log_creation_dt":"2020-04-1"}]

Upvotes: 0

Views: 1587

Answers (1)

David
David

Reputation: 218847

From the DataTables documentation:

Note that DataTables expects the table data to be an array of items in the data parameter of the object (use the ajax.dataSrc option of ajax as an object, if your data is formatted differently)

The object you're sending back is just an array, not an object with a data property. The simplest approach would probably be to make use of that ajax.dataSrc option and set it to an empty string:

"ajax": {
  "url": "../model/getData.php",
  "type": "POST",
  "dataSrc": ""
}

Alternatively you'd want to modify your PHP code to create an object with a data property and set your $output as the value of that property, then json_encode and output that object.

Upvotes: 1

Related Questions