Set DataSource For jQuery DataTable to PHP JSON Encoded Array

Question

I am using the below code to query MySql and return the results to an array. I have verified with a for each loop that this has my results as expected.

My last step of this process is to set this php array as the datasource for the jQuery Datatable? I have the below code but my DataTable is never created.

What am I missing?

<table id="example" class="display" width="100%"></table>

<?php
$con=mysqli_connect("site", "user", "psasswr=ord", "db");

if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql="SELECT * FROM test LIMIT 10";
$result = mysqli_query($con,$sql);

$data = [];

foreach ($result as $row) {
   $data[] = $row;
}

mysqli_free_result($result);

mysqli_close($con);
?>

<script>
var dataSet = <?php echo json_encode($result); ?>;
$(document).ready(function() {
    $('#example').DataTable( {
        data: dataSet,
        columns: [
            { title: "Name" },
            { title: "Title" },
            { title: "Office" },
            { title: "Salary" }
        ]
    } );
} );
</script>

EDIT
As requested this is what my JSON looks like if I do a echo json_encode($data)

[{"Salesman":"Harris Teeter","Title":"Manager","Office":"Home","Salary":"0.000000"}]

take note DataTables has its own json format, you have to conform to it — Kevin
– Kevin, Commented Jul 29, 2019 at 1:55

Raj Purohit · Accepted Answer · 2019-07-29 12:19:11Z

1

First, you need to use data instead of title in datatable function.

$('#example').DataTable( {
    data: dataSet,
    columns: [
        { data: "Name" },
        { data: "Title" },
        { data: "Office" },
        { data: "Salary" }
    ]
} );

Second, you need to modify your JSON like below:

[{"Name":"Harris Teeter","Title":"Manager","Office":"Home","Salary":"0.000000"}]

Here is working JSFiddle : link

answered Jul 29, 2019 at 12:19

Raj Purohit

2411 silver badge8 bronze badges

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

Collectives™ on Stack Overflow

Set DataSource For jQuery DataTable to PHP JSON Encoded Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related