Hi anyone that can help me with this seems like i have an invalid json response here is my code:

UtahUtahUtah · November 2022

I've already followed how to check developer tool -> network -> response and it showed this problem:
Notice: Undefined index: draw in C:\xampp\htdocs\sample\action.php on line 82 
{"draw":0,"recordsTotal":0,"recordsFiltered":0,"data":[]}

Here is my code:

<?php

//action.php

$connect = new PDO("mysql:host=localhost;dbname=sumbong", "root", "");

if(isset($_POST["action"]))
{
if($_POST["action"] == 'fetch')
{
$order_column = array('report_topic', 'incident_address', 'report_date');

    $main_query = "SELECT report_date AS Date, COUNT(report_topic) AS Number_Of_Incidents_Reported 
    FROM report
    GROUP BY report_date
    ORDER BY report_date";

    $search_query = 'WHERE report_date <= "'.date('Y-m-d').'" AND ';

    if(isset($_POST["start_date"], $_POST["end_date"]) && $_POST["start_date"] != '' && $_POST["end_date"] != '')
    {
        $search_query .= 'report_date >= "'.$_POST["start_date"].'" AND report_date <= "'.$_POST["end_date"].'" AND ';
    }

    if(isset($_POST["search"]["value"]))
    {
        $search_query .= '(report_topic LIKE "%'.$_POST["search"]["value"].'%" OR incident_address LIKE "%'.$_POST["search"]["value"].'%" OR report_date LIKE "%'.$_POST["search"]["value"].'%")';
    }

    $group_by_query = " GROUP BY report_date ";

    $order_by_query = "";

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

    $limit_query = '';

    // if($_POST["length"] != -1)

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

    $statement = $connect->prepare($main_query . $search_query . $group_by_query . $order_by_query);

    $statement->execute();

    $filtered_rows = $statement->rowCount();

    $statement = $connect->prepare($main_query . $group_by_query);

    $statement->execute();

    $total_rows = $statement->rowCount();

    $result = $connect->query($main_query . $search_query . $group_by_query . $order_by_query . $limit_query, PDO::FETCH_ASSOC);

    $data = array();

    foreach($data as $row)
    {
        $sub_array = array();

        $sub_array[] = $row['report_topic'];

        $sub_array[] = $row['incident_address'];

        $sub_array[] = $row['report_date'];

        $data[] = $sub_array;
    }

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

    echo json_encode($output);
}

}

<?php > ?>

UtahUtahUtah · November 2022

Messed up the code. Anyway here is the code:

<?php 

//index.php


<?php
>
?>


<!doctype html>
<html lang="en">
    <head>
        <!-- Required meta tags -->
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <!-- Bootstrap CSS -->
        <link href="library/bootstrap-5/bootstrap.min.css" rel="stylesheet" />
        <link href="library/dataTables.bootstrap5.min.css" rel="stylesheet" />
        <link href="library/daterangepicker.css" rel="stylesheet" />

        <script src="library/jquery.min.js"></script>
        <script src="library/bootstrap-5/bootstrap.bundle.min.js"></script>
        <script src="library/moment.min.js"></script>
        <script src="library/daterangepicker.min.js"></script>
        <script src="library/Chart.bundle.min.js"></script>
        <script src="library/jquery.dataTables.min.js"></script>
        <script src="library/dataTables.bootstrap5.min.js"></script>

        <title>Sumbong | Generate Report</title>
    </head>
    <body>

        <div class="container-fluid">
            <h1 class="mt-2 mb-3 text-center text-primary">Generate Report</h1>
            <div class="card">
                <div class="card-header">
                    <div class="row">
                        <div class="col col-sm-9">Report Data</div>
                        <div class="col col-sm-3">
                            <input type="text" id="daterange_textbox" class="form-control" readonly />
                        </div>
                    </div>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <div class="chart-container pie-chart">
                            <canvas id="bar_chart" height="40"> </canvas>
                        </div>
                        <table class="table table-striped table-bordered" id="order_table">
                            <thead>
                                <tr>
                                    <th>Report Topic</th>
                                    <th>Reported Incident Address</th>
                                    <th>Report Date</th>
                                </tr>
                            </thead>
                            <tbody></tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </body>
</html>

<script>

    $(document).ready(function(){

        fetch_data();

        var report_chart;

        function fetch_data(start_date = '', end_date = '')
        {
            var dataTable = $('#order_table').DataTable({
                "processing" : true,
                "order" : [],
                "ajax" : {
                    url:"action.php",
                    type:"POST",
                    dataSrc: "",
                    data:{action:'fetch', start_date:start_date, end_date:end_date}
                },
                "drawCallback" : function(settings)
                {
                    var reports_date = [];

                    var incident = [];

                    for(var count = 0; count < settings.aoData.length; count++)
                    {
                        reports_date.push(settings.aoData[count]._aData[2]);
                        incident.push(parseFloat(settings.aoData[count]._aData[1]));
                    }

                    var chart_data = {
                        labels:reports_date,
                        datasets:[
                            {
                                label : 'Reports',
                                backgroundColor : 'rgb(255, 205, 86)',
                                color : '#fff',
                                data:incident
                            }
                        ]   
                    };

                    var group_chart3 = $('#bar_chart');

                    if(report_chart)
                    {
                        report_chart.destroy();
                    }

                    report_chart = new Chart(group_chart3, {
                        type:'bar',
                        data:chart_data
                    });
                }
            });
        }

        $('#daterange_textbox').daterangepicker({
            ranges:{
                'Today' : [moment(), moment()],
                'Yesterday' : [moment().subtract(1, 'days'), moment().subtract(1, 'days')],
                'Last 7 Days' : [moment().subtract(6, 'days'), moment()],
                'Last 30 Days' : [moment().subtract(29, 'days'), moment()],
                'This Month' : [moment().startOf('month'), moment().endOf('month')],
                'Last Month' : [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')]
            },
            format : 'YYYY-MM-DD'
        }, function(start, end){

            $('#order_table').DataTable().destroy();

            fetch_data(start.format('YYYY-MM-DD'), end.format('YYYY-MM-DD'));

        });

    });

</script>

And:
```
<?php

//action.php

$connect = new PDO("mysql:host=localhost;dbname=sumbong", "root", "");

if(isset($_POST["action"]))
{
if($_POST["action"] == 'fetch')
{
$order_column = array('report_topic', 'incident_address', 'report_date');

    $main_query = "SELECT report_date AS Date, COUNT(report_topic) AS Number_Of_Incidents_Reported 
    FROM report
    GROUP BY report_date
    ORDER BY report_date";

    $search_query = 'WHERE report_date <= "'.date('Y-m-d').'" AND ';

    if(isset($_POST["start_date"], $_POST["end_date"]) && $_POST["start_date"] != '' && $_POST["end_date"] != '')
    {
        $search_query .= 'report_date >= "'.$_POST["start_date"].'" AND report_date <= "'.$_POST["end_date"].'" AND ';
    }

    if(isset($_POST["search"]["value"]))
    {
        $search_query .= '(report_topic LIKE "%'.$_POST["search"]["value"].'%" OR incident_address LIKE "%'.$_POST["search"]["value"].'%" OR report_date LIKE "%'.$_POST["search"]["value"].'%")';
    }

    $group_by_query = " GROUP BY report_date ";

    $order_by_query = "";

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

    $limit_query = '';

    // if($_POST["length"] != -1)

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

    $statement = $connect->prepare($main_query . $search_query . $group_by_query . $order_by_query);

    $statement->execute();

    $filtered_rows = $statement->rowCount();

    $statement = $connect->prepare($main_query . $group_by_query);

    $statement->execute();

    $total_rows = $statement->rowCount();

    $result = $connect->query($main_query . $search_query . $group_by_query . $order_by_query . $limit_query, PDO::FETCH_ASSOC);

    $data = array();

    foreach($data as $row)
    {
        $sub_array = array();

        $sub_array[] = $row['report_topic'];

        $sub_array[] = $row['incident_address'];

        $sub_array[] = $row['report_date'];

        $data[] = $sub_array;
    }

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

    echo json_encode($output);
}

}

<?php > ``` ?>

allan · November 2022

Can you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Allan

kthorngren · November 2022

Notice: Undefined index: draw in C:\xampp\htdocs\sample\action.php on line 82

On line 82 of your PHP script you have "draw" => intval($_POST["draw"]),, meaning $_POST["draw"] doesn't exist. Your server script is expecting the Server Side Processing parameters sent from the client but that aren't being sent. You haven't enabled server side processing by setting serverSide true.

The first question is do you need Server Side Processing. If not then you can just return the row data, for example:

        $output = array(
            "data"          =>  $data
        );

Kevin

allan · November 2022

Actually - I see the issue. Your server-side script is expecting to handle a "server-side processing" request, but the serverSide option isn't specified in your DataTables configuration object.

You need to either add that option, or just have the server-side script respond with all data. See the manual here.

Allan

UtahUtahUtah · November 2022

WOW enabling serverside really helped me but still there are no datas appeared it just showed an error {"draw":1,"recordsTotal":0,"recordsFiltered":0,"data":[]}. Anyone can help me with this one?

allan · November 2022

I'd suggest you start debugging that by echoing out $main_query and see what is actually being created.

Btw you have loads of SQL injection attack vulnerabilities in your code. Could you use our server-side libraries for Editor perhaps? The are free to use for this sort of thing.

Also, do you really need server-side processing? Do you have at least 20'000 rows?

Allan

UtahUtahUtah · November 2022

Well the data from my database(MySQL) comes from an input of a client i guess that what makes it a server-side and i only have 5-6 rows on my table(db) but i just want 3 rows to be on the datatable

allan · November 2022

Right - if you only have three rows in the table, just use client-side processing. Get the server to send all the rows to the client-side like in this example.

It would also be worth reading over the section of the manual I linked to before to make sure you understand the difference between client-side and server-side processing in DataTables.

Allan

UtahUtahUtah · November 2022

Yeah if ill use client-side processing do i need to write down the datas of the rows completely like in the example? cause what if my client would write another set data?

kthorngren · November 2022

Sounds like you want to send parameters, via ajax, to the server to filter the data at the client. This example shows how by using ajax.data as a function. You don't need to enable server side processing for this to work. When the user updates the filters use ajax.reload() in the event handler to fetch the new set of data.

Kevi

Hi anyone that can help me with this seems like i have an invalid json response here is my code:

Hi anyone that can help me with this seems like i have an invalid json response here is my code:

Answers

Howdy, Stranger!

Categories

DataTables

Hi anyone that can help me with this seems like i have an invalid json response here is my code:

Hi anyone that can help me with this seems like i have an invalid json response here is my code:

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables