以Datatable格式显示从PHP返回的JSON数据

Question

I am new to JQUERY and I am trying to search for the something and based on the searched text I am doing an ajax call which will call php function and the PHP is returning me with JSON data. I want to display the returned data in the Datatable form. I have my PHP file table.php and JavaScript file jss.js and my main.php. The PHP file is returning the JSON data and I able to use alert to display it.

I want to know how can I display it in datatable.

<div>
<input type="text" name="search_query" id="search_query" placeholder="Search Client" size="50" autocomplete="off"/>
<button  id="search" name="submit">Search</button>
</div>

my ajax/jss.js file

$(document).ready(function(){
    $('#search').click(function(){
        var search_query = $('#search_query').val();


        if(search_query !='')
        {       
            $.ajax({
                url:"table.php",
                method:"POST",
                data:{search_query:search_query},

                success: function(data)
                {
                    alert("HEKKI "+data);
                }
            });
        }
        else
        {
            alert("Please Search again");
        }
    });
});

my table.php file

<?php
    $data=array();
    $dbc = mysqli_connect('localhost','root','','acdc') OR die('Could not connect because: '.mysqli_connect_error());

        if (isset($_REQUEST['search_query'])) 
        {
            $name = $_REQUEST['search_query'];
        }


        if($dbc)
        {

            if (!empty($name)) 
            {
                $sql = "select  c.res1      res1, 
                                cc.res2     res2, 
                                cc.res3     res3, 
                                cc.res4     res4, 
                                cc.res5     res5 
                        from table1 c 
                        inner join table2 cc
                        on c.id = cc.id
                        where c.name like '".$name."%'
                        and cc.ENABLED = 1";

                $res = mysqli_query($dbc,$sql);


                if(!(mysqli_num_rows($res)==0))
                {
                    while($row=mysqli_fetch_array($res))
                    {
                        $data['RES1']   =   $row['res1'];
                        $data['RES2']   =   $row['res2'];
                        $data['RES3']   =   $row['res3'];
                        $data['RES4']   =   $row['res4'];
                        $data['RES5']   =   $row['res5'];
                    }
                }

                else

                {
                    echo "<div style='display: block; color:red; text-align:center'><br/> Not Found,Please try again!!!</div>";
                }
            }
        }
        echo json_encode($data);

        /*

    */

    ?>

Can you please guide me how to display the result in main page.

Answer 1

Setting utf8 as charset is probably a good idea. If you have different charset in your table you will get a JSON error :

mysqli_set_charset($dbc, 'utf8');

Then use mysqli_fetch_assoc instead of mysqli_fetch_array. You want field: value records turned into JSON :

$data = array();
while($row=mysqli_fetch_assoc($res)) {
   $data[] = $row;
}

Output the JSON :

echo json_encode( array('data' => $data) );

Now you can use it directly along with dataTables :

<table id="example"></table>

$('#example').DataTable({
  ajax: {
    url: 'table.php'
  },
  columns: [
    { data: 'res1', title: 'res1'},
    { data: 'res2', title: 'res2'},
    //etc..
  ]
})