DataTables Returns No Data Available in Table when its actually there

mchigubhu

Sorry Guys I am new to DataTables and have customised the code I got on the internet but I cant seem to be able to show the data. I amtrying to get data from a table tblbooking which has a lot of records. But each time I try to show the page I get the error No Data Available in Table.

I have 2 pages. the bookings page order.php with the following code $(document).ready(function(){

var orderdataTable = $('#order_data').DataTable({
    "processing":true,
    "serverSide":true,
    "order":[],
    "ajax":{
        url:"order_fetch.php",
        type:"POST"
    },
    <?php
    if($_SESSION["type"] == 'master')
    {
    ?>
    "columnDefs":[
        {
            "targets":[4, 5, 6, 7, 8, 9],
            "orderable":false,
        },
    ],
    <?php
    }
    else
    {
    ?>
    "columnDefs":[
        {
            "targets":[4, 5, 6, 7, 8],
            "orderable":false,
        },
    ],
    <?php
    }
    ?>
    "pageLength": 25
});

Then the order_fetch.php is as below

//order_fetch.php

include('database_connection.php');

include('function.php');

$query = '';

$output = array();

$query = " SELECT * FROM tblbooking ";

if($_SESSION['type'] == 'user') { $query .= 'WHERE DoneBy = "'.$_SESSION["user_id"].'" AND '; }

if(isset($_POST["search"]["value"])) { $query .= '(PaxName LIKE "%'.$_POST["search"]["value"].'%" '; $query .= 'OR Paxname LIKE "%'.$_POST["search"]["value"].'%" '; $query .= 'OR BookingRef LIKE "%'.$_POST["search"]["value"].'%" '; $query .= 'OR InvoiceNo LIKE "%'.$_POST["search"]["value"].'%" '; $query .= 'OR BookingDate LIKE "%'.$_POST["search"]["value"].'%") '; }

if(isset($_POST["order"])) { $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' '; } else { $query .= 'ORDER BY BookingID 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) { $payment_status = '';

if($row['IsCashSale']>0)
{
$payment_status = '<span class="label label-primary">Cash</span>';
}
else
{
$payment_status = '<span class="label label-warning">Credit</span>';
}

$status = '';
if($row['Cancelled']<1)
{
$status = '<span class="label label-success">Active</span>';
}
else
{
$status = '<span class="label label-danger">Inactive</span>';
}
$sub_array = array();
$sub_array[] = $row['BookingID'];
$sub_array[] = $row['PaxName'];
$sub_array[] = $row['BookingID'];
$sub_array[] = $payment_status;
$sub_array[] = $status;
$sub_array[] = $row['BookingID'];
if($_SESSION['type'] == 'master')
{
$sub_array[] = get_user_name($connect, $row['user_id']);
}
$sub_array[] = '<a href="view_order.php?pdf=1&order_id='.$row['BookingID'].'" class="btn btn-info btn-xs">View PDF</a>';
$sub_array[] = '<button type="button" name="update" id="'.$row['BookingID'].'" class="btn btn-warning btn-xs update">Update</button>';
$sub_array[] = '<button type="button" name="delete" id="'.$row['BookingID'].'" class="btn btn-danger btn-xs delete" data-status="'.$row["Cancelled"].'">Delete</button>';
$data[] = $sub_array;
}

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

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

echo json_encode($output);

<?php > Please anyone who can check and advise where my error is. I am new to dataTables and struggling for the past 4 days with same issue ?>

allan

What is the server returning? This tech note will show you how to get the JSON response.

It sounds like there might be an issue with your PHP script.

Do you really need server-side processing? Do you have tens of thousands or more records?

Allan

mchigubhu

Thank you Allan, I have found the problem. Each time the field has an apostrophe in the data. I get this errror. But on removing apostrophe its fine. Problem its changing the data

allan

Interesting. Does the server's error logs show anything? Perhaps something about a misformed UTF8 character?

What does the server return if there is an apostrophe?

Allan