listing.html
<!-- https://datatables.net/examples/server_side/simple.html -->
<!-- https://datatables.net/manual/ajax -->
<!-- https://datatables.net/manual/ajax#Column-data-points -->
<!-- https://stackoverflow.com/questions/64526856/how-to-add-edit-delete-buttons-in-each-row-of-datatable -->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.css">
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.2.0/css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.12.1/css/dataTables.bootstrap5.min.css">
<script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.6.1.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.js"></script>
<title>Document</title>
</head>
<body>
<table id="example" class="display table table-striped" style="width:100%">
<thead>
<tr>
<th>Name</th>
<th>Address</th>
<th>Salary</th>
<th>Action</th>
</tr>
</thead>
</table>
</body>
<script>
$(document).ready(function () {
$('#example').DataTable({
processing: true,
serverSide: true,
ajax: 'http://ciacloud.in/juhi/rems/get_employees.php',
columns: [
{ data: 'name'},
{ data: 'address'},
{ data: 'salary'},
{
data: 'id',
render: (data,type,row) => {
console.log({data,type,row})
return `<a href='edit_form.php?id=${data}'>Edit</a> | <a href='delete.php?id=${data}'>Delete</a>`;
}
}
],
columnDefs: [
{ orderable: false, targets: -1 },
{
"defaultContent": "-",
"targets": "_all"
}],
order: [0,1,2,3],
});
});
</script>
</html>
get_employees.php
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
$params = $_REQUEST;
header('Access-Control-Allow-Origin: *');
$search = isset($params['search']['value']) && $params['search']['value'] != '' ? $params['search']['value'] : '';
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$where = '';
if($search !== '')
$where = " name LIKE '$search%' ";
else
$where = " 1 = 1 ";
$sort_columns = ['name', 'address', 'salary'];
$sort_column_index = $params['order'][0]['column'];
$sort_order = isset($params['order'][0]['dir']) && $params['order'][0]['dir'] != 1 ? $params['order'][0]['dir'] : 'desc';
$order_by = " $sort_columns[$sort_column_index] $sort_order";
$offset = $params['start'];
$limit = $params['length'];
$sql = "SELECT * FROM employees WHERE $where ORDER BY $order_by LIMIT $offset, $limit";
$stmt = $conn->prepare($sql);
$stmt->execute();
// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$employees = $stmt->fetchAll();
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$count_sql = "SELECT COUNT(*) FROM employees WHERE $where";
$result = $conn->prepare($count_sql);
$result->execute();
$totalRecords = $result->fetchColumn();
$conn = null;
$json_data = array(
"draw" => intval( $params['draw'] ),
"recordsTotal" => intval( $totalRecords ),
"recordsFiltered" => intval($totalRecords),
"data" => $employees,
"count_sql" => $count_sql,
"sql" => $sql,
);
echo json_encode($json_data);