javascript - DataTables “No data available in table” even when there are records in mysql database table

Question

I'm trying to fetch records from my database and populate datatables. AJAX sends a request to a supplier_fetch.php where i have made a connection to the database and listed the rows that i need to display in array format. I also placed the Edit and Disable buttons in the Datatable.

I have 2 records in the suppliers table which are meant to be displayed on this Datatable yet i get the No data available in table message. Two other tables in my database - categories and user are displaying on their respective Datatables respectively using the same method that I'm currently using for suppliers.

I have attached AJAX code, the HTML, the supplier_fetch.php and a screenshot of the database supplier table structure to reflect the table header titles.

AJAX

var supplierdataTable = $('#supplier_data').DataTable({
    "processing":true,
    "serverSide":true,
    "order":[],
    "ajax":{
        url:"supplier_fetch.php",
        type:"POST"
    },
    "columnDefs":[
        {
            "targets":[6, 7],
            "orderable":false,
        },
    ],
    "pageLength": 25
});

HTML Table

<div class="panel-body">
                    <div class="row">
                        <div class="col-sm-12 table-responsive">
                            <table id="supplier_data" class="table table-bordered table-striped">
                                <thead><tr>
                                    <th>ID</th>
                                    <th>Supplier Name</th>
                                    <th>Supplier Address</th>
                                    <th>Supplier Phone</th>
                                    <th>Supplier Email</th>
                                    <th>Status</th>
                                    <th>Edit</th>
                                    <th>Disable</th>
                                    
                                </tr></thead>
                            </table>
                        </div>
                    </div>
                </div>

supplier_fetch.php

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
include("./database/dbnew.php");

$query = '';

$output = array();

$query .= "
SELECT * FROM suppliers  
";

if(isset($_POST["search"]["value"]))
{
    $query .= '(supplier_name LIKE "%'.$_POST["search"]["value"].'%" ';
    $query .= 'OR supplier_address LIKE "%'.$_POST["search"]["value"].'%" ';
    $query .= 'OR supplier_status LIKE "%'.$_POST["search"]["value"].'%" ';
}

if(isset($_POST["order"]))
{
    $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
    $query .= 'ORDER BY supplier_id DESC ';
}

if($_POST["length"] != -1)
{
    $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

$data = array();

$filtered_rows = $statement->rowCount();

foreach($result as $row)
{
    $status = '';
    if($row["supplier_status"] == 'Active')
    {
        $status = '<span class="label label-success">Active</span>';
    }
    else
    {
        $status = '<span class="label label-danger">Inactive</span>';
    }
    $sub_array = array();
    $sub_array[] = $row['supplier_id'];
    $sub_array[] = $row['supplier_name'];
    $sub_array[] = $row['supplier_address'];
    $sub_array[] = $row['supplier_phone'];
    $sub_array[] = $row['supplier_email'];
    $sub_array[] = $status;
    $sub_array[] = '<button type="button" name="update" id="'.$row["supplier_id"].'" class="btn btn-warning btn-xs update">Edit</button>';
    $sub_array[] = '<button type="button" name="delete" id="'.$row["supplier_id"].'" class="btn btn-danger btn-xs delete" data-status="'.$row["category_status"].'">Disable</button>';
    $data[] = $sub_array;
}

$output = array(
    "draw"              =>  intval($_POST["draw"]),
    "recordsTotal"      =>  $filtered_rows,
    "recordsFiltered"   =>  get_total_all_records($connect),
    "data"              =>  $data
);
echo json_encode($output);

function get_total_all_records($connect)
{
    $statement = $connect->prepare("SELECT * FROM suppliers");
    $statement->execute();
    return $statement->rowCount();
}

?>

Screenshot of suppliers table

Is there any help I can get? Is there an alternate way of achieving this?

Answer 1

等待大神答复