Display the MySQL table using PHP and JavaScript in datatable

Question

Currently, I am working to develop a website to expand and sharpen my knowledge in web development, I am a beginner in programming.

So I am trying to display the data of my table named accountinfo in html datatable. I have tried to display it only in html datatable using php and echoed it in html file and it work perfectly fine. like this:

<table id="tblList" class="text-center">
    <thead class="bg-light text-capitalize">
        <tr>
            <th hidden>User ID</th>
            <th>Control No.</th>
            <th>Account Name</th>
            <th>Date Inspection</th>
            <th>Date of Report</th>
            <th>Actual Use</th>
            <th>Landmark</th>
            <th>Registered Owner</th>
            <th>Status</th>
        </tr>
    </thead>
    <tbody>
        <?php
            echo $output;
        ?>
    </tbody>
</table>

This code has no errors, but i want to use the javascript to display it in the data table.

But i dont have any idea on how to work on it..

I have tried it a little but i did not get the right result. it does not display the value of the Mysql Table in HTML data table.

I tried this:

function loadAccountList() {
    $.ajax({
        type: 'POST',
        url: '../php_functions/tableList.php',
        dataType: 'json',
        data: xdata,
        contentType: 'application/json; charset=utf-8',
        success: function (response) {
            $('#tbodyemplist').empty();
            var cells = eval("(" + response.d + ")");
            for (var i = 0; i < cells.length; i++) {

                var resDate;
                if (cells[i].res_date == "")
                {
                    resDate = "N/A";
                }
                else
                {
                    resDate = cells[i].res_date;
                }

                $('#tbodyemplist').append('<tr data-empid="' + cells[i].ID + '">'
                + '<td style="font-size:11px; text-align:center;" hidden>' + cells[i].badgenum+ '</td>'
                + '<td style="font-size:11px; text-align:center;">' + cells[i].CN+ '</td>'
                + '<td style="font-size:11px; text-align:center;">' +cells[i].AccName+ '</td>'
                + '<td style="font-size:11px; text-align:center;">' + cells[i].DI+ '</td>'
                + '<td style="font-size:11px; text-align:center;">' +cells[i].contact_no+'</td>'
                + '<td style="font-size:11px; text-align:center;">' + cells[i].DR+</td>'
                + '<td style="font-size:11px; text-align:center;">' +  cells[i].ActUse+ '</td>'                            
                + '<td style="font-size:11px; text-align:center;">' + cells[i].Landmark+ '</td>'
                + '<td style="font-size:11px; text-align:center;">' + cells[i].status+ '</td>'
                                    + '</tr>');
            }
        },
        error: function (error) {
            console.log(error);
        },
        complete: function () {
            $('#tblemplist').dataTable({
                "order": [],
                "columnDefs": [{
                    "targets": 'no-sort',
                    "orderable": false,
                }]
            }).end();
            $('#tblemplist').css('width', '100%');
        }
    });
}

And call it in html like this

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="jsFiles/index.js"></script>

and also the php file

<?php 
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "benchmark";

    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $acc_id="";
    $acc_name="";
    $date_inspection="";
    $date_report = "";
    $act_useof_prop="";
    $landmark="";
    $reg_owner="";
    $bvcsi_control = "";
    $status = "";
    $lstrow = "";

    $sql = "SELECT account_id, account_name, date_inspection, date_report, act_useof_prop, landmark, reg_owner, bvcsi_control,status from accountinfo ORDER BY account_id ASC";
    $result = mysqli_query($conn,$sql);
    $num = mysqli_num_rows($result);
    $output = '';
    if ($num > 0)
    {
        while ($row = mysqli_fetch_array($result))
        {
            $ID= $row["account_id"];
            $CN= $row["bvcsi_control"];
            $AccName= $row["account_name"];
            $DI= $row["date_inspection"];
            $DR= $row["date_report"];
            $actUse= $row["act_useof_prop"];
            $landmark = $row["landmark"];
            $reg_owner = $row["reg_owner"];
            $status = $row["status"];

        }
    }
?>

This project will be given to the company I had been render my OJT.

Edit

Please see attached image: Console error:

enter image description here

EDIT: enter image description here

This is what I got from jQuery official website "The jqXHR.success(), jqXHR.error(), and jqXHR.complete() callback methods are removed as of jQuery 3.0. You can use jqXHR.done(), jqXHR.fail(), and jqXHR.always() instead." You are using 3.3.1 version. — Sagar
– Sagar, Commented Mar 13, 2019 at 6:50
in this part success: function (response) try to console.log(response) it is show data in your console (inspect element browser and choose console tab)? — dianeryanto
– dianeryanto, Commented Mar 13, 2019 at 7:00

dianeryanto · Accepted Answer · 2019-03-13 08:25:13Z

1

If you want to create tabled based on your database using javascript what you need is :

First step is, Select data to your table and return it in json format.

For example like this, say it as data.php :

$temparray = array();

$sqlselect = "SELECT * FROM accountinfo ";
$result = $conn->query($sqlselect);
if ($result->num_rows > 0){
    while($row = $result->fetch_assoc()) 
    {
        array_push($temparray, $row); //save your data into array
    }
}
echo json_encode($temparray);

Try open data.php it is show json data? if yes, go to second step.

Second step is, calling your json data using ajax.

For example like this, say it as loadTable.js:

$.ajax({
    url: "data.php",
    type: "get", 
    dataType: "JSON",
    data: {}, //this is data you send to your server
    success: function(res)
    {   
        console.log(res); //show all data
        console.log(res.length); //check data length

        //doing looping for create tbody.

    }
})

If console.log show the data, you can doing the next step using looping.

I try to create simple working example for help you, hope it help:

$.ajax({
  url: "https://api.myjson.com/bins/dgd5u", //change to your php file (in my example data.php)
  type: "get",
  dataType: "JSON",
  data: {}, //this is data you send to your server
  success: function(res) {
    console.log(res);
    console.log(res.length);

    for (let i = 0; i < res.length; i++) {
      $('#testing').append('<tr><td>' + res[i]['name'] + '</td><td>' + res[i]['phone'] + '</td><td>' + res[i]['email'] + '</td></tr>')
    }

  }
})

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

<div class="col-md-6">
  <table class="table table-bordered" style="text-align:center">
    <thead>
      <td>Name</td>
      <td>Phone</td>
      <td>Email</td>
    </thead>
    <tbody id='testing'>
    </tbody>
  </table>
</div>

</div>

See in full page.

edited Mar 13, 2019 at 8:25

answered Mar 13, 2019 at 8:16

dianeryanto

6002 gold badges6 silver badges18 bronze badges

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

6 Comments

Alexis Abulencia Villar Over a year ago

hi sir! you code does actually work.. but it has an error, it always show in the data table the no data available in table and the searchbox of the data table is not working. please see the attached image again.

dianeryanto Over a year ago

for datatables plugin make sure you already read the documentation how to load datatables using ajax. For using plugin datatables you don't need define and append it. My example based on your actually do, but from echo method changed using append. For datatables you need different ajax

Alexis Abulencia Villar Over a year ago

what do you mean different ajax sir??

dianeryanto Over a year ago

check documentation

Alexis Abulencia Villar Over a year ago

then sir, why does the table look like that?? i mean. did i do something wrong?

|

Collectives™ on Stack Overflow

Display the MySQL table using PHP and JavaScript in datatable

Edit

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related