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:

UtahUtahUtahUtahUtahUtah Posts: 5Questions: 1Answers: 0
edited November 2022 in Free community support

I've already followed how to check developer tool -> network -> response and it showed this problem:
<b>Notice</b>: Undefined index: draw in <b>C:\xampp\htdocs\sample\action.php</b> on line <b>82</b><br />
{"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); } } ?>

This question has an accepted answers - jump to answer

Answers

  • UtahUtahUtahUtahUtahUtah Posts: 5Questions: 1Answers: 0
    edited November 2022

    Messed up the code. Anyway here is the code:

    <?php 
    
    //index.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);
        }
    }
    
    ?>
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    <b>Notice</b>: Undefined index: draw in <b>C:\xampp\htdocs\sample\action.php</b> on line <b>82</b><br />

    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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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

  • UtahUtahUtahUtahUtahUtah Posts: 5Questions: 1Answers: 0

    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?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • UtahUtahUtahUtahUtahUtah Posts: 5Questions: 1Answers: 0
    edited 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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • UtahUtahUtahUtahUtahUtah Posts: 5Questions: 1Answers: 0

    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?

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

Sign In or Register to comment.