{"id":2430,"date":"2022-09-18T03:40:25","date_gmt":"2022-09-18T03:40:25","guid":{"rendered":"https:\/\/codeinsightacademy.com\/blog\/?p=2430"},"modified":"2022-09-18T03:41:06","modified_gmt":"2022-09-18T03:41:06","slug":"datatable","status":"publish","type":"post","link":"https:\/\/codeinsightacademy.com\/blog\/javascript\/datatable\/","title":{"rendered":"Datatable"},"content":{"rendered":"\n

listing.html<\/h2>\n\n\n\n
<!-- https:\/\/datatables.net\/examples\/server_side\/simple.html -->\n<!-- https:\/\/datatables.net\/manual\/ajax -->\n<!-- https:\/\/datatables.net\/manual\/ajax#Column-data-points -->\n<!-- https:\/\/stackoverflow.com\/questions\/64526856\/how-to-add-edit-delete-buttons-in-each-row-of-datatable -->\n\n<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n    <meta charset=\"UTF-8\">\n    <meta http-equiv=\"X-UA-Compatible\" content=\"IE=edge\">\n    <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\">\n    <link rel=\"stylesheet\" type=\"text\/css\" href=\"https:\/\/cdn.datatables.net\/1.12.1\/css\/jquery.dataTables.css\">\n  \n    <link rel=\"stylesheet\" type=\"text\/css\" href=\"https:\/\/cdnjs.cloudflare.com\/ajax\/libs\/twitter-bootstrap\/5.2.0\/css\/bootstrap.min.css\">\n    <link rel=\"stylesheet\" type=\"text\/css\" href=\"https:\/\/cdn.datatables.net\/1.12.1\/css\/dataTables.bootstrap5.min.css\">\n\n    <script type=\"text\/javascript\" charset=\"utf8\" src=\"https:\/\/code.jquery.com\/jquery-3.6.1.js\"><\/script>\n<script type=\"text\/javascript\" charset=\"utf8\" src=\"https:\/\/cdn.datatables.net\/1.12.1\/js\/jquery.dataTables.js\"><\/script>\n    <title>Document<\/title>\n<\/head>\n<body>\n<table id=\"example\" class=\"display table table-striped\" style=\"width:100%\">\n        <thead>\n            <tr>\n                <th>Name<\/th>\n                <th>Address<\/th>\n                <th>Salary<\/th>\n                <th>Action<\/th>\n            <\/tr>\n        <\/thead>\n    <\/table>\n<\/body>\n<script>\n    $(document).ready(function () {\n    $('#example').DataTable({\n        processing: true,\n        serverSide: true,\n        ajax: 'http:\/\/ciacloud.in\/juhi\/rems\/get_employees.php',\n        columns: [\n            { data: 'name'},\n            { data: 'address'},\n            { data: 'salary'},\n            { \n                data: 'id',\n                render: (data,type,row) => {\n                    console.log({data,type,row})\n                   return `<a href='edit_form.php?id=${data}'>Edit<\/a> | <a href='delete.php?id=${data}'>Delete<\/a>`;\n                 }\n            }\n        ],\n        columnDefs: [\n            { orderable: false, targets: -1 },\n            {\n                \"defaultContent\": \"-\",\n                \"targets\": \"_all\"\n            }],\n            order: [0,1,2,3],\n    });\n});\n<\/script>\n<\/html>\n<\/code><\/pre>\n\n\n\n
get_employees.php<\/h2>\n\n\n\n
<?php\n$servername = \"localhost\";\n$username = \"username\";\n$password = \"password\";\n$dbname = \"database\";\n\n$params = $_REQUEST;\nheader('Access-Control-Allow-Origin: *');\n\n$search = isset($params['search']['value'])  && $params['search']['value'] != '' ? $params['search']['value'] : '';\n\ntry {\n  $conn = new PDO(\"mysql:host=$servername;dbname=$dbname\", $username, $password);\n  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);\n\n  $where = '';\n\n  if($search !== '')\n\t  $where = \" name LIKE '$search%' \";\n  else\n\t  $where = \" 1 = 1 \";\n\n  $sort_columns = ['name', 'address', 'salary'];\n  $sort_column_index = $params['order'][0]['column'];\n  $sort_order = isset($params['order'][0]['dir']) && $params['order'][0]['dir'] != 1 ? $params['order'][0]['dir'] : 'desc';\n\n  $order_by = \" $sort_columns[$sort_column_index] $sort_order\";\n\t\n  $offset = $params['start'];\n  $limit = $params['length'];\n\n  $sql = \"SELECT * FROM employees WHERE $where ORDER BY $order_by LIMIT $offset, $limit\";\n  $stmt = $conn->prepare($sql);\n  $stmt->execute();\n\n  \/\/ set the resulting array to associative\n  $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);\n  $employees = $stmt->fetchAll();\n\n} catch(PDOException $e) {\n  echo \"Error: \" . $e->getMessage();\n}\n\n$count_sql = \"SELECT COUNT(*) FROM employees WHERE $where\";\n$result = $conn->prepare($count_sql); \n$result->execute(); \n$totalRecords = $result->fetchColumn(); \n\n$conn = null;\n\n\n$json_data = array(\n\t\t\"draw\"            => intval( $params['draw'] ),\n\t\t\"recordsTotal\"    => intval( $totalRecords ),\n\t\t\"recordsFiltered\" => intval($totalRecords),\n\t\t\"data\"            => $employees,\n\t\t\"count_sql\"\t=> $count_sql,\n\t\t\"sql\"\t\t=> $sql,\n\t);\n\necho json_encode($json_data);<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
listing.html get_employees.php<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[18],"tags":[],"_links":{"self":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts\/2430"}],"collection":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/comments?post=2430"}],"version-history":[{"count":2,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts\/2430\/revisions"}],"predecessor-version":[{"id":2432,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts\/2430\/revisions\/2432"}],"wp:attachment":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/media?parent=2430"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/categories?post=2430"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/tags?post=2430"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}