How to edit a cell and delete a row from dataTable at serverside processing jquery

Question

I am using first time DataTable server side processing.

Data is shown in table, but I want to include edit/delete option for each cell in dataTable.

Here is my client side code for ajax call and html table

<script>
        $(document).ready(function() {
        var dataTable = $('.table-wrap').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax":{
        url: '<?php echo TEMPLATES_URI."schedule_ajax.php"; ?>',
        type: "post",
        error: function(){ 
        $(".employee-grid-error").html("");
        $("#employee-grid").append('<tbody class="employee-grid-error"><tr><th               
        colspan="3">No data found in the server</th></tr></tbody>');
        $("#employee-grid_processing").css("display","none");   
        }
        }
        });             
        } );
     </script>
    <table class = "table-wrap">
        <thead>
            <tr id = "a">
                <th>Device</th>
                <th>City</th>
                <th>Schedule Date</th>
                <th>Business Point</th>
            </tr>
        </thead>
    </table>

here is the serverside code

    <?php include("../inc/config.php");?>
    <?php
    $requestData= $_REQUEST;
    $columns = array( 
    // datatable column index  => database column name
    0 =>'name', 
    1 => 'BC_Name',
    2 => 'BS_ScheduledDate',
    3 => 'BP_Name',
    );
    $sql = "SELECT devices.name,
    et_business_cities.BC_Name, 
    et_business_schedules.BS_ScheduledDate,  
    et_business_point.BP_Name 
    FROM `et_business_schedules`
    INNER JOIN devices 
    ON et_business_schedules.BS_DeviceID = devices.id
    INNER JOIN et_business_cities 
    ON et_business_schedules.BS_CityID = et_business_cities.BC_ID
    INNER JOIN et_business_point 
    ON et_business_schedules.BS_BusinessPointID = et_business_point.BP_ID";
    $query=mysqli_query($db, $sql) 
    or die("schedule_ajax.php: get schedule    data");
    $totalData = mysqli_num_rows($query);
    $totalFiltered = $totalData;
    $sql = "SELECT devices.name,
            et_business_cities.BC_Name, 
            et_business_schedules.BS_ScheduledDate, 
            et_business_point.BP_Name 
    FROM `et_business_schedules`
            INNER JOIN devices ON et_business_schedules.BS_DeviceID = devices.id
            INNER JOIN et_business_cities 
    ON et_business_schedules.BS_CityID = et_business_cities.BC_ID
            INNER JOIN et_business_point 
    ON et_business_schedules.BS_BusinessPointID = et_business_point.BP_ID 
    WHERE   1=1";

   if( !empty($requestData['search']['value']) ) {   
       $sql.=" AND ( name 
       LIKE '".$requestData['search']['value']."%' ";    
       $sql.=" OR BC_Name 
       LIKE '".$requestData['search']['value']."%' ";
       $sql.=" OR BS_ScheduledDate 
       LIKE '".$requestData['search']['value']."%'";
       $sql.=" OR BC_Name 
       LIKE '".$requestData['search']['value']."%' ";
       $sql.=" OR BP_Name 
       LIKE '".$requestData['search']['value']."%' )";
       }    
       $query=mysqli_query($db, $sql) 
       or die("employee-grid-data.php: get schedule   data");
       $totalFiltered = mysqli_num_rows($query); 
       $sql.=" LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
       $query=mysqli_query($db, $sql) 
       or die("schedule_ajax.php: get schedule data");
       $data = array();
    while( $row=mysqli_fetch_array($query) ) { 
       $dt = new DateTime($row['BS_ScheduledDate']);
       $newdate =  $dt->format('d-m-Y');
       $nestedData=array(); 
       $nestedData[] = $row["name"];
       $nestedData[] = $row["BC_Name"];
       $nestedData[] = $newdate;
       $nestedData[] = $row["BP_Name"];
       $data[] = $nestedData;
       }

       $json_data = array(
            "draw" => intval( $requestData['draw'] ),   
            "recordsTotal" => intval( $totalData ),  
            "recordsFiltered" => intval( $totalFiltered ), 
            "data" => $data   
       );
      echo json_encode($json_data);     
      ?>

Answer 1

I changed my query as

"SELECT 
devices.name,
et_business_cities.BC_Name, 
et_business_schedules.BS_ID,
et_business_schedules.BS_ScheduledDate, 
et_business_point.BP_Name 

FROM `et_business_schedules`
INNER JOIN devices ON et_business_schedules.BS_DeviceID = devices.id
INNER JOIN et_business_cities ON et_business_schedules.BS_CityID = et_business_cities.BC_ID
INNER JOIN et_business_point ON et_business_schedules.BS_BusinessPointID = et_business_point.BP_ID WHERE 1=1";

and getting the id of row and redirect it to any other page where I can easily edit it and view it.

 while( $row=mysqli_fetch_array($query) ) { 
    $nestedData=array(); 
    $nestedData[] = $row["name"];
    $nestedData[] = $row["BC_Name"];
    $nestedData[] = $row["BS_ScheduledDate"];
    $nestedData[] = $row["BP_Name"];
    $nestedData[] ='<a class="fa fa-pencil-square-o" href="'.TEMPLATES_URI.$row['BS_ID'].'" >Edit</a>
                          <a class="fa fa-trash" href="'.TEMPLATES_URI.$row['BS_ID'].'" >Delete</a>
                          <a class="fa fa-eye" href="'.TEMPLATES_URI.$row['BS_ID'].'" >View</a>';
        $data[] = $nestedData;
    }

Answer 2

You can use rowCallback

According to the documentation:

This callback allows you to 'post process' each row after it have been generated for each table draw, but before it is rendered into the document. This means that the contents of the row might not have dimensions ($().width() for example) if it is not already in the document.

The code for the same will be something like:

var dataTable = $('.table-wrap').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax":{
            url: '<?php echo TEMPLATES_URI."schedule_ajax.php"; ?>',
            type: "post",
            error: function(){ 
                 $(".employee-grid-error").html("");
                 $("#employee-grid").append('<tbody class="employee-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
                 $("#employee-grid_processing").css("display","none");   
             }
        },
        "rowCallback": function(row, data, index){
                var newBtns = "<button>Edit</button>&nbsp;<button>Delete</button>";
                $(row).append(newBtns);
            }
     });

Here's a fiddle