add checkbox to datatable while loading data from database using ajax

Question

I can't add checkbox to my DataTable while bind data from db using ajax. How can I add a checkbox with server-side data loading?

My jQuery:

var table = $('#example').DataTable({
        "ajax": "getBusperOrder.php",
        "bPaginate": true,
        "retrieve": true,
        "bProcessing": true,
        "pageLength": 10,
        "columns": [{
                mData: 'district'
            }, {
                mData: 'deponame'
            }, {
                mData: 'busname'
            }, {
                mData: 'bonnetnumber'
            }, {
                mData: 'routename'
            }, {
                mData: 'bustype'
            }, {
                mData: 'status'
            }
        ],
    });

HTML:

<table id="example">
    <thead>
        <tr>
            <th>District</th>
            <th>Depo Name</th>
            <th>Bus Number</th>
            <th>Bonnet Number</th>
            <th>Route Name</th>
            <th>Bus Type</th>
            <th>Action</th>
        </tr>
    </thead>
</table>

gerBusperOrder.php

<?php
require('database/db.php');
$sql = "select * from bus as B left join depo as D on B.depoid=D.depoid left join district as DS on D.district=DS.id left join bustype as BS on B.bustypeid=BS.bustypeid left join route as R on B.routeid=R.routeid LEFT JOIN bustype as BT on B.bustypeid=BT.bustypeid WHERE B.busid IN(SELECT busid from bus where busid NOT IN (SELECT DISTINCT(bus_id) from advt_book_side AS ABS INNER JOIN booking as B on ABS.booking_number=B.bookingnumber WHERE B.todate>CURDATE() GROUP BY bus_id HAVING COUNT(sides_id)=4))";
$resultset = mysqli_query($db, $sql) or die("database error:" . mysqli_error($db));
$data = array();
while ($rows = mysqli_fetch_assoc($resultset)) {
    $data[] = $rows;
}
$results = array(
    "sEcho" => 1,
    "iTotalRecords" => count($data),
    "iTotalDisplayRecords" => count($data),
    "aaData" => $data
);
echo json_encode($results);
?>

I need to add a checkbox on the first column of each td with id

There's no id column in your table. Should that also be generated dynamically along with checkbox? — Yevhen Horbunkov
– Yevhen Horbunkov, Commented Aug 20, 2019 at 9:12

Yevhen Horbunkov · Accepted Answer · 2019-08-20 09:43:36Z

2

You may employ columns.render option for that purpose:

"columns": [{
        mData: 'district'
        render: (_,__,rowData) => `<input type="checkbox" value="${rowData.busid}">${rowData.busid}</input>`
    },
    ...
]

edited Aug 20, 2019 at 9:43

answered Aug 20, 2019 at 9:01

Yevhen Horbunkov

15.6k3 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user11831635 Over a year ago

But i need to set value of checkbox from there, i tried both {render: mData => '<input type="checkbox" value="+id"></input>'}, and {render: mData => '<input type="checkbox"></input>'+id}.

user11831635 Over a year ago

No. I got another values like,{ mData: 'district' } , { mData: 'deponame' }, . I need to assign { mData: 'busid' } to checkbox

Yevhen Horbunkov Over a year ago

@Linu: If you need to put the value of another property of source data object into the cell contents, you may refer to entire row data, like in the example I've given above

Muhammed Shihabudeen Labba A · Accepted Answer · 2019-08-20 09:28:37Z

1

Please find this answer. You can populate checkbox from the server-side itself

var table = $('#example').DataTable({
        "processing": false,
        "serverSide": true,
        "order": [],
        "ajax": {
            "url": "getBusperOrder.php",
            "type": "POST"
        }

In the HTML you need to add

<table id="example">
<thead>
    <tr>
        <th></th>
        <th>District</th>
        <th>Depo Name</th>
        <th>Bus Number</th>
        <th>Bonnet Number</th>
        <th>Route Name</th>
        <th>Bus Type</th>
        <th>Action</th>
    </tr>
</thead>

Changes in PHP

<?php
require('database/db.php');
$sql = "select * from bus as B left join depo as D on B.depoid=D.depoid left join district as DS on D.district=DS.id left join bustype as BS on B.bustypeid=BS.bustypeid left join route as R on B.routeid=R.routeid LEFT JOIN bustype as BT on B.bustypeid=BT.bustypeid WHERE B.busid IN(SELECT busid from bus where busid NOT IN (SELECT DISTINCT(bus_id) from advt_book_side AS ABS INNER JOIN booking as B on ABS.booking_number=B.bookingnumber WHERE B.todate>CURDATE() GROUP BY bus_id HAVING COUNT(sides_id)=4))";
$resultset = mysqli_query($db, $sql) or die("database error:" . mysqli_error($db));
$data = array();
while ($rows = mysqli_fetch_assoc($resultset)) {
    $row = array();
    $row[] = '<div class="table-checkbox table-checkbox-data"><input type="checkbox" value="'. $rows['id'] .'"></div>';
   //insert other columns in $row array
    $data[] = $rows;
}
$output = array(
            "recordsTotal" => count($data),,
            "recordsFiltered" => count($data),,
            "data" => $data,
        );

    echo json_encode($output);
?>

answered Aug 20, 2019 at 9:28

Muhammed Shihabudeen Labba A

6398 silver badges21 bronze badges

2 Comments

user11831635 Over a year ago

But, how can I display this checkbox on datatable?

Muhammed Shihabudeen Labba A Over a year ago

Datatable will take care of that as per the number of columns. Glad that other solution worked for you.

Collectives™ on Stack Overflow

add checkbox to datatable while loading data from database using ajax

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related