{"id":90,"date":"2018-03-20T18:09:07","date_gmt":"2018-03-20T18:09:07","guid":{"rendered":"https:\/\/shanecunningham.me\/blog\/?p=90"},"modified":"2020-11-14T14:30:07","modified_gmt":"2020-11-14T14:30:07","slug":"datatables-with-pagination-using-ajax-php-mysql-calling-a-php-functions","status":"publish","type":"post","link":"https:\/\/cun.ie\/blog\/2018\/03\/20\/datatables-with-pagination-using-ajax-php-mysql-calling-a-php-functions\/","title":{"rendered":"Datatables with Pagination using Ajax, PHP, MySql calling a php functions"},"content":{"rendered":"<div class=\"et_d4_element et_pb_section et_pb_section_0  et_pb_css_mix_blend_mode et_section_regular et_block_section\" >\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_d4_element et_pb_row et_pb_row_0  et_pb_css_mix_blend_mode et_block_row\">\n\t\t\t\t<div class=\"et_d4_element et_pb_column_4_4 et_pb_column et_pb_column_0  et_pb_css_mix_blend_mode et-last-child et_block_column\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_module et_d4_element et_pb_text et_pb_text_0  et_pb_text_align_left et_pb_bg_layout_light\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_text_inner\"><p>This PHP tutorial will help you create jquery Datatable 1.10.16 using a Bootstrap 3.3.7. with pagination. The tutorial is broken into 8 steps.<\/p>\n<p>We will use the following structure for the datatable:<\/p>\n<ul>\n<li>index.php<\/li>\n<li>configDB.php<\/li>\n<li>response.php<\/li>\n<\/ul>\n<p><strong>Step 1:<\/strong> Create a Bootstrap Html page called <strong>index.php<\/strong> and include the scripts and CSS below<\/p><\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_code et_pb_code_0\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_dmb_code_snippet et_pb_dmb_code_snippet_0\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_module_inner\">\n\t\t\t\t\t<h4 class=\"dbcs_title\">CSS<\/h4><pre class=\"github-gist\"><code class=\"hljs html show_linenums\">&lt;link href=&quot;https:\/\/maxcdn.bootstrapcdn.com\/bootstrap\/3.3.7\/css\/bootstrap.min.css&quot; rel=&quot;stylesheet&quot;\/&gt;\n&lt;link href=&quot;https:\/\/cdn.datatables.net\/1.10.16\/css\/dataTables.bootstrap.min.css&quot; rel=&quot;stylesheet&quot;\/&gt;\n\n &lt;script src=&quot;https:\/\/code.jquery.com\/jquery-3.3.1.min.js&quot;\n  integrity=&quot;sha256-FgpCb\/KJQlLNfOu91ta32o\/NMZxltwRo8QtmkMRdAu8=&quot;\n  crossorigin=&quot;anonymous&quot;&gt;&lt;\/script&gt;\n    &lt;script src=&quot;https:\/\/maxcdn.bootstrapcdn.com\/bootstrap\/3.3.7\/js\/bootstrap.min.js&quot;&gt;&lt;\/script&gt; \n    &lt;script type=&quot;text\/javascript&quot; src=&quot;https:\/\/cdn.datatables.net\/r\/dt\/dt-1.10.9\/datatables.min.js&quot;&gt;&lt;\/script&gt; \n    &lt;script type=&quot;text\/javascript&quot; src=&quot;https:\/\/cdn.datatables.net\/1.10.16\/js\/jquery.dataTables.min.js&quot;&gt;&lt;\/script&gt;\n    &lt;script type=&quot;text\/javascript&quot; src=&quot;https:\/\/cdn.datatables.net\/1.10.16\/js\/dataTables.bootstrap.min.js&quot;&gt;&lt;\/script&gt;<\/code><\/pre >\n\t\t\t\t<\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_text et_pb_text_1  et_pb_text_align_left et_pb_bg_layout_light\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_text_inner\"><p><strong>Step 2:<\/strong> Add the Html below to inside the body tags<\/p><\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_dmb_code_snippet et_pb_dmb_code_snippet_1\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_module_inner\">\n\t\t\t\t\t<pre class=\"github-gist\"><code class=\"hljs html show_linenums\">&lt;div class=&quot;container&quot;&gt;\n    &lt;div class\n    &lt;div class=&quot;header&quot;&gt;&lt;h1&gt;Server side DataTable demo in Php,Mysql and Ajax &lt;\/h1&gt;&lt;\/div&gt;\n    &lt;div class=&quot;container&quot;&gt;\n        &lt;table id=&quot;employee-grid&quot; class=&quot;table table-striped table-bordered&quot; style=&quot;width:100%&quot;&gt;\n            &lt;thead&gt;\n            &lt;tr&gt;\n                &lt;th&gt;Employee name&lt;\/th&gt;\n                &lt;th&gt;Salary&lt;\/th&gt;\n                &lt;th&gt;Age&lt;\/th&gt;\n            &lt;\/tr&gt;\n            &lt;\/thead&gt;\n        &lt;\/table&gt;\n    &lt;\/div&gt;\n&lt;\/div&gt;<\/code><\/pre >\n\t\t\t\t<\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_text et_pb_text_2  et_pb_text_align_left et_pb_bg_layout_light\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_text_inner\"><p><strong>Step 3:<\/strong> Instantiate the datatable object in<strong> index.php<\/strong><\/p><\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_dmb_code_snippet et_pb_dmb_code_snippet_2\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_module_inner\">\n\t\t\t\t\t<pre class=\"github-gist\"><code class=\"hljs javascript show_linenums\">$(document).ready(function () {\n          var dataTable = $(&#039;#employee-grid&#039;).DataTable({\n              &quot;responsive&quot;: true,\n              &quot;processing&quot;: true,\n              &quot;serverSide&quot;: true,\n              &quot;ajax&quot;: {\n                  url: &quot;ajax\/response.php&quot;, \/\/ json datasource\n                  data: {action: &#039;getEMP&#039;}, \/\/ Set the POST variable  array and adds action: getEMP\n                  type: &#039;post&#039;,  \/\/ method  , by default get\n              },\n              error: function () {  \/\/ error handling\n                  $(&quot;.employee-grid-error&quot;).html(&quot;&quot;);\n                  $(&quot;#employee-grid&quot;).append(&#039;&lt;tbody class=&quot;employee-grid-error&quot;&gt;&lt;tr&gt;&lt;th colspan=&quot;3&quot;&gt;No data found in the server&lt;\/th&gt;&lt;\/tr&gt;&lt;\/tbody&gt;&#039;);\n                  $(&quot;#employee-grid_processing&quot;).css(&quot;display&quot;, &quot;none&quot;);\n              }\n          });\n      });<\/code><\/pre >\n\t\t\t\t<\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_text et_pb_text_3  et_pb_text_align_left et_pb_bg_layout_light\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_text_inner\"><p><strong>Step 4:<\/strong> Create a database connection file called <strong>config.php<\/strong><\/p><\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_dmb_code_snippet et_pb_dmb_code_snippet_3\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_module_inner\">\n\t\t\t\t\t<pre class=\"github-gist\"><code class=\"hljs show_linenums\">&lt;?php\ndefine(&#039;DB_SERVER&#039;, &#039;localhost&#039;);\ndefine(&#039;DB_USERNAME&#039;, &#039;username&#039;);\ndefine(&#039;DB_PASSWORD&#039;, &#039;passwordForDB&#039;);\ndefine(&#039;DB_NAME&#039;, &#039;DatabaseName&#039;);\n$DBconnect = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);\n\/\/echo &quot;Connect Successfully. Host info: &quot; . mysqli_get_host_info($DBconnect);\nif (!$DBconnect) {\n    die(&quot;Connection failed: &quot; . mysqli_connect_error());\n}<\/code><\/pre >\n\t\t\t\t<\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_text et_pb_text_4  et_pb_text_align_left et_pb_bg_layout_light\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_text_inner\"><p><strong>Step 5<\/strong>: For this example, you will need to load data from the MySQL database, so we need to create a database table to store and retrieve data.<\/p><\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_dmb_code_snippet et_pb_dmb_code_snippet_4\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_module_inner\">\n\t\t\t\t\t<pre class=\"github-gist\"><code class=\"hljs sql show_linenums\">--\n-- Table structure for table `employee`\n--\n\nCREATE TABLE IF NOT EXISTS `employee` (\n  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT &#039;primary key&#039;,\n  `employee_name` varchar(255) NOT NULL COMMENT &#039;employee name&#039;,\n  `employee_salary` double NOT NULL COMMENT &#039;employee salary&#039;,\n  `employee_age` int(11) NOT NULL COMMENT &#039;employee age&#039;,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT=&#039;datatable demo table&#039; AUTO_INCREMENT=64 ;\n\n--\n-- Dumping data for table `employee`\n--\n\nINSERT INTO `employee` (`id`, `employee_name`, `employee_salary`, `employee_age`) VALUES\n(1, &#039;Tiger Nixon&#039;, 320800, 61),\n(2, &#039;Garrett Winters&#039;, 170750, 63),\n(3, &#039;Ashton Cox&#039;, 86000, 66),\n(4, &#039;Cedric Kelly&#039;, 433060, 22),\n(5, &#039;Airi Satou&#039;, 162700, 33),\n(6, &#039;Brielle Williamson&#039;, 372000, 61),\n(7, &#039;Herrod Chandler&#039;, 137500, 59),\n(8, &#039;Rhona Davidson&#039;, 327900, 55),\n(9, &#039;Colleen Hurst&#039;, 205500, 39),\n(10, &#039;Sonya Frost&#039;, 103600, 23),\n(11, &#039;Jena Gaines&#039;, 90560, 30),\n(12, &#039;Quinn Flynn&#039;, 342000, 22),\n(13, &#039;Charde Marshall&#039;, 470600, 36),\n(14, &#039;Haley Kennedy&#039;, 313500, 43),\n(15, &#039;Tatyana Fitzpatrick&#039;, 385750, 19),\n(16, &#039;Michael Silva&#039;, 198500, 66),\n(17, &#039;Paul Byrd&#039;, 725000, 64),\n(18, &#039;Gloria Little&#039;, 237500, 59),\n(19, &#039;Bradley Greer&#039;, 132000, 41),\n(20, &#039;Dai Rios&#039;, 217500, 35),\n(21, &#039;Jenette Caldwell&#039;, 345000, 30),\n(22, &#039;Yuri Berry&#039;, 675000, 40),\n(23, &#039;Caesar Vance&#039;, 106450, 21),\n(24, &#039;Doris Wilder&#039;, 85600, 23),\n(25, &#039;Angelica Ramos&#039;, 1200000, 47),\n(26, &#039;Gavin Joyce&#039;, 92575, 42),\n(27, &#039;Jennifer Chang&#039;, 357650, 28),\n(28, &#039;Brenden Wagner&#039;, 206850, 28),\n(29, &#039;Fiona Green&#039;, 850000, 48),\n(30, &#039;Shou Itou&#039;, 163000, 20),\n(31, &#039;Michelle House&#039;, 95400, 37),\n(32, &#039;Suki Burks&#039;, 114500, 53),\n(33, &#039;Prescott Bartlett&#039;, 145000, 27),\n(34, &#039;Gavin Cortez&#039;, 235500, 22),\n(35, &#039;Martena Mccray&#039;, 324050, 46),\n(36, &#039;Unity Butler&#039;, 85675, 47),\n(37, &#039;Howard Hatfield&#039;, 164500, 51),\n(38, &#039;Hope Fuentes&#039;, 109850, 41),\n(39, &#039;Vivian Harrell&#039;, 452500, 62),\n(40, &#039;Timothy Mooney&#039;, 136200, 37),\n(41, &#039;Jackson Bradshaw&#039;, 645750, 65),\n(42, &#039;Olivia Liang&#039;, 234500, 64),\n(43, &#039;Bruno Nash&#039;, 163500, 38),\n(44, &#039;Sakura Yamamoto&#039;, 139575, 37),\n(45, &#039;Thor Walton&#039;, 98540, 61),\n(46, &#039;Finn Camacho&#039;, 87500, 47),\n(47, &#039;Serge Baldwin&#039;, 138575, 64),\n(48, &#039;Zenaida Frank&#039;, 125250, 63),\n(49, &#039;Zorita Serrano&#039;, 115000, 56),\n(50, &#039;Jennifer Acosta&#039;, 75650, 43),\n(51, &#039;Cara Stevens&#039;, 145600, 46),\n(52, &#039;Hermione Butler&#039;, 356250, 47),\n(53, &#039;Lael Greer&#039;, 103500, 21),\n(54, &#039;Jonas Alexander&#039;, 86500, 30),\n(55, &#039;Shad Decker&#039;, 183000, 51),\n(56, &#039;Michael Bruce&#039;, 183000, 29),\n(57, &#039;Donna Snider&#039;, 112000, 27);<\/code><\/pre >\n\t\t\t\t<\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_text et_pb_text_5  et_pb_text_align_left et_pb_bg_layout_light\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_text_inner\"><p><strong>Step 6:<\/strong> Create the<strong> response.php<\/strong> and include the connection at the top of the file<\/p><\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_dmb_code_snippet et_pb_dmb_code_snippet_5\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_module_inner\">\n\t\t\t\t\t<pre class=\"github-gist\"><code class=\"hljs show_linenums\">if (isset($_POST[&#039;action&#039;]) &amp;&amp; !empty($_POST[&#039;action&#039;])) {\n    $action = $_POST[&#039;action&#039;];\n    switch ($action) {\n        case &#039;getEMP&#039; :\n            getEMP($DBconnect);\n            break;\n        case &#039;getProd&#039; :\n            getProducts($DBconnect);\n            break;\n        \/\/ ...etc...\n    }\n}<\/code><\/pre >\n\t\t\t\t<\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_text et_pb_text_6  et_pb_text_align_left et_pb_bg_layout_light\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_text_inner\"><p><strong>Step 8: <\/strong>Create the function <strong>getEMP<\/strong> and pass in the $DBconnect object.<\/p><\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_dmb_code_snippet et_pb_dmb_code_snippet_6\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_module_inner\">\n\t\t\t\t\t<pre class=\"github-gist\"><code class=\"hljs show_linenums\">function getEMP($DBconnect)\n{\n\/\/ storing  request (ie, get\/post) global array to a variable\n    $requestData = $_REQUEST;\n    $columns = array(\n\/\/ datatable column index  =&gt; database column name\n        0 =&gt; &#039;employee_name&#039;,\n        1 =&gt; &#039;employee_salary&#039;,\n        2 =&gt; &#039;employee_age&#039;\n    );\n\/\/ getting total number records without any search\n    $sql = &quot;SELECT employee_name, employee_salary, employee_age &quot;;\n    $sql .= &quot; FROM employee&quot;;\n    $query = mysqli_query($DBconnect, $sql) or die(&quot;Mysql Mysql Error in getting : get products&quot;);\n    $totalData = mysqli_num_rows($query);\n    $totalFiltered = $totalData;  \/\/ when there is no search parameter then total number rows = total number filtered rows.\n    $sql = &quot;SELECT employee_name, employee_salary, employee_age &quot;;\n    $sql .= &quot; FROM employee WHERE 1=1&quot;;\n    if (!empty($requestData[&#039;search&#039;][&#039;value&#039;])) {   \/\/ if there is a search parameter, $requestData[&#039;search&#039;][&#039;value&#039;] contains search parameter\n        $sql .= &quot; AND ( employee_name LIKE &#039;&quot; . $requestData[&#039;search&#039;][&#039;value&#039;] . &quot;%&#039; &quot;;\n        $sql .= &quot; OR employee_salary LIKE &#039;&quot; . $requestData[&#039;search&#039;][&#039;value&#039;] . &quot;%&#039; &quot;;\n        $sql .= &quot; OR employee_age LIKE &#039;&quot; . $requestData[&#039;search&#039;][&#039;value&#039;] . &quot;%&#039; )&quot;;\n    }\n    $query = mysqli_query($DBconnect, $sql) or die(&quot;Mysql Mysql Error in getting : get products&quot;);\n    $totalFiltered = mysqli_num_rows($query); \/\/ when there is a search parameter then we have to modify total number filtered rows as per search result.\n    $sql .= &quot; ORDER BY &quot; . $columns[$requestData[&#039;order&#039;][0][&#039;column&#039;]] . &quot;   &quot; . $requestData[&#039;order&#039;][0][&#039;dir&#039;] . &quot;   LIMIT &quot; . $requestData[&#039;start&#039;] . &quot; ,&quot; . $requestData[&#039;length&#039;] . &quot;   &quot;;\n    \/* $requestData[&#039;order&#039;][0][&#039;column&#039;] contains colmun index, $requestData[&#039;order&#039;][0][&#039;dir&#039;] contains order such as asc\/desc , $requestData[&#039;start&#039;] contains start row number ,$requestData[&#039;length&#039;] contains limit length. *\/\n    $query = mysqli_query($DBconnect, $sql) or die(&quot;Mysql Mysql Error in getting : get products&quot;);\n    $data = array();\n    while ($row = mysqli_fetch_array($query)) {  \/\/ preparing an array\n        $nestedData = array();\n        $nestedData[] = $row[&quot;employee_name&quot;];\n        $nestedData[] = $row[&quot;employee_salary&quot;];\n        $nestedData[] = $row[&quot;employee_age&quot;];\n        $data[] = $nestedData;\n    }\n    $json_data = array(\n        &quot;draw&quot; =&gt; intval($requestData[&#039;draw&#039;]),   \/\/ for every request\/draw by clientside , they send a number as a parameter, when they recieve a response\/data they first check the draw number, so we are sending same number in draw.\n        &quot;recordsTotal&quot; =&gt; intval($totalData),  \/\/ total number of records\n        &quot;recordsFiltered&quot; =&gt; intval($totalFiltered), \/\/ total number of records after searching, if there is no searching then totalFiltered = totalData\n        &quot;data&quot; =&gt; $data   \/\/ total data array\n    );\n    echo json_encode($json_data);  \/\/ send data as json format\n}<\/code><\/pre >\n\t\t\t\t<\/div>\n\t\t\t<\/div><div class=\"et_pb_module et_d4_element et_pb_text et_pb_text_7  et_pb_text_align_left et_pb_bg_layout_light\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_text_inner\"><p>Links to the Demo and Source code below.<\/p><\/div>\n\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t<\/div><div class=\"et_d4_element et_pb_row et_pb_row_1  et_pb_css_mix_blend_mode et_block_row\">\n\t\t\t\t<div class=\"et_d4_element et_pb_column_1_4 et_pb_column et_pb_column_1  et_pb_css_mix_blend_mode et_block_column\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_button_module_wrapper et_pb_button_0_wrapper  et_pb_module \">\n\t\t\t\t<a class=\"et_pb_button et_d4_element et_pb_button_0 et_pb_bg_layout_light et_block_module\" href=\"https:\/\/projects.shanecunningham.me\/jquery\/\" target=\"_blank\">Demo<\/a>\n\t\t\t<\/div>\n\t\t\t<\/div><div class=\"et_d4_element et_pb_column_1_4 et_pb_column et_pb_column_2  et_pb_css_mix_blend_mode et_block_column\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t<div class=\"et_pb_button_module_wrapper et_pb_button_1_wrapper  et_pb_module \">\n\t\t\t\t<a class=\"et_pb_button et_d4_element et_pb_button_1 et_pb_bg_layout_light et_block_module\" href=\"https:\/\/gist.github.com\/ShaneCunn\/0012cd8f492f72cd669910ef55c0e007\" target=\"_blank\">Source Code<\/a>\n\t\t\t<\/div>\n\t\t\t<\/div><div class=\"et_d4_element et_pb_column_1_4 et_pb_column et_pb_column_3  et_pb_css_mix_blend_mode et_block_column et_pb_column_empty\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t<\/div><div class=\"et_d4_element et_pb_column_1_4 et_pb_column et_pb_column_4  et_pb_css_mix_blend_mode et-last-child et_block_column et_pb_column_empty\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t<\/div>\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t<\/div>\n\t\t\t\t\n\t\t\t\t\n\t\t\t<\/div>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":99,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"aside","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"This PHP tutorial will help you create jquery <a href=\"https:\/\/datatables.net\/\">Datatable<\/a> 1.10.16 using a <a href=\"https:\/\/getbootstrap.com\/docs\/3.3\/\">Bootstrap 3.3.7<\/a>. with pagination. The tutorial is broken into 8 steps.\n\nWe will use the following structure for the datatable:\n<ol>\n \t<li>index.php<\/li>\n \t<li>configDB.php<\/li>\n \t<li>response.php<\/li>\n<\/ol>\n<!--more-->\n\n<strong>Step 1:<\/strong> Create a Bootstrap Html page called index.php and include the scripts and CSS below\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"html\">&lt;link href=\"https:\/\/maxcdn.bootstrapcdn.com\/bootstrap\/3.3.7\/css\/bootstrap.min.css\" rel=\"stylesheet\"\/&gt;\n&lt;link href=\"https:\/\/cdn.datatables.net\/1.10.16\/css\/dataTables.bootstrap.min.css\" rel=\"stylesheet\"\/&gt;\n\n &lt;script src=\"https:\/\/code.jquery.com\/jquery-3.3.1.min.js\"\n  integrity=\"sha256-FgpCb\/KJQlLNfOu91ta32o\/NMZxltwRo8QtmkMRdAu8=\"\n  crossorigin=\"anonymous\"&gt;&lt;\/script&gt;\n    &lt;script src=\"https:\/\/maxcdn.bootstrapcdn.com\/bootstrap\/3.3.7\/js\/bootstrap.min.js\"&gt;&lt;\/script&gt; \n    &lt;script type=\"text\/javascript\" src=\"https:\/\/cdn.datatables.net\/r\/dt\/dt-1.10.9\/datatables.min.js\"&gt;&lt;\/script&gt; \n    &lt;script type=\"text\/javascript\" src=\"https:\/\/cdn.datatables.net\/1.10.16\/js\/jquery.dataTables.min.js\"&gt;&lt;\/script&gt;\n    &lt;script type=\"text\/javascript\" src=\"https:\/\/cdn.datatables.net\/1.10.16\/js\/dataTables.bootstrap.min.js\"&gt;&lt;\/script&gt;<\/pre>\n<strong>Step 2:<\/strong>  Add the Html below to inside the body tags\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"html\">&lt;div class=\"container\"&gt;\n    &lt;div class\n    &lt;div class=\"header\"&gt;&lt;h1&gt;Server side DataTable demo in Php,Mysql and Ajax &lt;\/h1&gt;&lt;\/div&gt;\n    &lt;div class=\"container\"&gt;\n        &lt;table id=\"employee-grid\" class=\"table table-striped table-bordered\" style=\"width:100%\"&gt;\n            &lt;thead&gt;\n            &lt;tr&gt;\n                &lt;th&gt;Employee name&lt;\/th&gt;\n                &lt;th&gt;Salary&lt;\/th&gt;\n                &lt;th&gt;Age&lt;\/th&gt;\n            &lt;\/tr&gt;\n            &lt;\/thead&gt;\n        &lt;\/table&gt;\n    &lt;\/div&gt;\n&lt;\/div&gt;<\/pre>\n<strong>Step 3:<\/strong> Instantiate the datatable object in index.php\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"js\" data-enlighter-highlight=\"6-9\">$(document).ready(function () {\n          var dataTable = $('#employee-grid').DataTable({\n              \"responsive\": true,\n              \"processing\": true,\n              \"serverSide\": true,\n              \"ajax\": {\n                  url: \"ajax\/response.php\", \/\/ json datasource\n                  data: {action: 'getEMP'}, \/\/ Set the POST variable  array and adds action: getEMP\n                  type: 'post',  \/\/ method  , by default get\n              },\n              error: function () {  \/\/ error handling\n                  $(\".employee-grid-error\").html(\"\");\n                  $(\"#employee-grid\").append('&lt;tbody class=\"employee-grid-error\"&gt;&lt;tr&gt;&lt;th colspan=\"3\"&gt;No data found in the server&lt;\/th&gt;&lt;\/tr&gt;&lt;\/tbody&gt;');\n                  $(\"#employee-grid_processing\").css(\"display\", \"none\");\n              }\n          });\n      });<\/pre>\n<strong>Step 4:<\/strong> Create a database connection file called config.php\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"php\">&lt;?php\ndefine('DB_SERVER', 'localhost');\ndefine('DB_USERNAME', 'username');\ndefine('DB_PASSWORD', 'passwordForDB');\ndefine('DB_NAME', 'DatabaseName');\n$DBconnect = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);\n\/\/echo \"Connect Successfully. Host info: \" . mysqli_get_host_info($DBconnect);\nif (!$DBconnect) {\n    die(\"Connection failed: \" . mysqli_connect_error());\n}<\/pre>\n<strong>Step 5:<\/strong> For this example,  you will need to load data from MySQL database, so we need to create a database table to store and retrieve data.\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">--\n-- Table structure for table `employee`\n--\n\nCREATE TABLE IF NOT EXISTS `employee` (\n  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',\n  `employee_name` varchar(255) NOT NULL COMMENT 'employee name',\n  `employee_salary` double NOT NULL COMMENT 'employee salary',\n  `employee_age` int(11) NOT NULL COMMENT 'employee age',\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=64 ;\n\n--\n-- Dumping data for table `employee`\n--\n\nINSERT INTO `employee` (`id`, `employee_name`, `employee_salary`, `employee_age`) VALUES\n(1, 'Tiger Nixon', 320800, 61),\n(2, 'Garrett Winters', 170750, 63),\n(3, 'Ashton Cox', 86000, 66),\n(4, 'Cedric Kelly', 433060, 22),\n(5, 'Airi Satou', 162700, 33),\n(6, 'Brielle Williamson', 372000, 61),\n(7, 'Herrod Chandler', 137500, 59),\n(8, 'Rhona Davidson', 327900, 55),\n(9, 'Colleen Hurst', 205500, 39),\n(10, 'Sonya Frost', 103600, 23),\n(11, 'Jena Gaines', 90560, 30),\n(12, 'Quinn Flynn', 342000, 22),\n(13, 'Charde Marshall', 470600, 36),\n(14, 'Haley Kennedy', 313500, 43),\n(15, 'Tatyana Fitzpatrick', 385750, 19),\n(16, 'Michael Silva', 198500, 66),\n(17, 'Paul Byrd', 725000, 64),\n(18, 'Gloria Little', 237500, 59),\n(19, 'Bradley Greer', 132000, 41),\n(20, 'Dai Rios', 217500, 35),\n(21, 'Jenette Caldwell', 345000, 30),\n(22, 'Yuri Berry', 675000, 40),\n(23, 'Caesar Vance', 106450, 21),\n(24, 'Doris Wilder', 85600, 23),\n(25, 'Angelica Ramos', 1200000, 47),\n(26, 'Gavin Joyce', 92575, 42),\n(27, 'Jennifer Chang', 357650, 28),\n(28, 'Brenden Wagner', 206850, 28),\n(29, 'Fiona Green', 850000, 48),\n(30, 'Shou Itou', 163000, 20),\n(31, 'Michelle House', 95400, 37),\n(32, 'Suki Burks', 114500, 53),\n(33, 'Prescott Bartlett', 145000, 27),\n(34, 'Gavin Cortez', 235500, 22),\n(35, 'Martena Mccray', 324050, 46),\n(36, 'Unity Butler', 85675, 47),\n(37, 'Howard Hatfield', 164500, 51),\n(38, 'Hope Fuentes', 109850, 41),\n(39, 'Vivian Harrell', 452500, 62),\n(40, 'Timothy Mooney', 136200, 37),\n(41, 'Jackson Bradshaw', 645750, 65),\n(42, 'Olivia Liang', 234500, 64),\n(43, 'Bruno Nash', 163500, 38),\n(44, 'Sakura Yamamoto', 139575, 37),\n(45, 'Thor Walton', 98540, 61),\n(46, 'Finn Camacho', 87500, 47),\n(47, 'Serge Baldwin', 138575, 64),\n(48, 'Zenaida Frank', 125250, 63),\n(49, 'Zorita Serrano', 115000, 56),\n(50, 'Jennifer Acosta', 75650, 43),\n(51, 'Cara Stevens', 145600, 46),\n(52, 'Hermione Butler', 356250, 47),\n(53, 'Lael Greer', 103500, 21),\n(54, 'Jonas Alexander', 86500, 30),\n(55, 'Shad Decker', 183000, 51),\n(56, 'Michael Bruce', 183000, 29),\n(57, 'Donna Snider', 112000, 27);\n\n<\/pre>\n<strong>Step 6:<\/strong> Create the response.php file and include the connection at the top of the response.php file:\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"php\">include '..\/DB\/configDB.php';<\/pre>\n<strong>Step 7: <\/strong>Add an If statement to response.php, this checks to see if the <a href=\"https:\/\/www.tutorialrepublic.com\/php-tutorial\/php-get-and-post.php\"><strong>POST<\/strong><\/a> variable is set and is also not empty.  It then sets the $action variable and which is then used in a switch statement to call the getEMP function passing in a database connection object.\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"php\" data-enlighter-highlight=\"1,5\">if (isset($_POST['action']) &amp;&amp; !empty($_POST['action'])) {\n    $action = $_POST['action'];\n    switch ($action) {\n        case 'getEMP' :\n            getEMP($DBconnect);\n            break;\n        case 'getProd' :\n            getProducts($DBconnect);\n            break;\n        \/\/ ...etc...\n    }\n}<\/pre>\n<strong>Step 8:  <\/strong>Create the function getEMP and pass in the $DBconnect object.\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"php\">function getEMP($DBconnect)\n{\n\/\/ storing  request (ie, get\/post) global array to a variable\n    $requestData = $_REQUEST;\n    $columns = array(\n\/\/ datatable column index  =&gt; database column name\n        0 =&gt; 'employee_name',\n        1 =&gt; 'employee_salary',\n        2 =&gt; 'employee_age'\n    );\n\/\/ getting total number records without any search\n    $sql = \"SELECT employee_name, employee_salary, employee_age \";\n    $sql .= \" FROM employee\";\n    $query = mysqli_query($DBconnect, $sql) or die(\"Mysql Mysql Error in getting : get products\");\n    $totalData = mysqli_num_rows($query);\n    $totalFiltered = $totalData;  \/\/ when there is no search parameter then total number rows = total number filtered rows.\n    $sql = \"SELECT employee_name, employee_salary, employee_age \";\n    $sql .= \" FROM employee WHERE 1=1\";\n    if (!empty($requestData['search']['value'])) {   \/\/ if there is a search parameter, $requestData['search']['value'] contains search parameter\n        $sql .= \" AND ( employee_name LIKE '\" . $requestData['search']['value'] . \"%' \";\n        $sql .= \" OR employee_salary LIKE '\" . $requestData['search']['value'] . \"%' \";\n        $sql .= \" OR employee_age LIKE '\" . $requestData['search']['value'] . \"%' )\";\n    }\n    $query = mysqli_query($DBconnect, $sql) or die(\"Mysql Mysql Error in getting : get products\");\n    $totalFiltered = mysqli_num_rows($query); \/\/ when there is a search parameter then we have to modify total number filtered rows as per search result.\n    $sql .= \" ORDER BY \" . $columns[$requestData['order'][0]['column']] . \"   \" . $requestData['order'][0]['dir'] . \"   LIMIT \" . $requestData['start'] . \" ,\" . $requestData['length'] . \"   \";\n    \/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc\/desc , $requestData['start'] contains start row number ,$requestData['length'] contains limit length. *\/\n    $query = mysqli_query($DBconnect, $sql) or die(\"Mysql Mysql Error in getting : get products\");\n    $data = array();\n    while ($row = mysqli_fetch_array($query)) {  \/\/ preparing an array\n        $nestedData = array();\n        $nestedData[] = $row[\"employee_name\"];\n        $nestedData[] = $row[\"employee_salary\"];\n        $nestedData[] = $row[\"employee_age\"];\n        $data[] = $nestedData;\n    }\n    $json_data = array(\n        \"draw\" =&gt; intval($requestData['draw']),   \/\/ for every request\/draw by clientside , they send a number as a parameter, when they recieve a response\/data they first check the draw number, so we are sending same number in draw.\n        \"recordsTotal\" =&gt; intval($totalData),  \/\/ total number of records\n        \"recordsFiltered\" =&gt; intval($totalFiltered), \/\/ total number of records after searching, if there is no searching then totalFiltered = totalData\n        \"data\" =&gt; $data   \/\/ total data array\n    );\n    echo json_encode($json_data);  \/\/ send data as json format\n}<\/pre>\nYou can view the Demo and download the source code below.\n\n<a href=\"https:\/\/projects.shanecunningham.me\/jquery\/\" rel=\"noopener\">Live Demo <\/a> <a href=\"https:\/\/gist.github.com\/ShaneCunn\/0012cd8f492f72cd669910ef55c0e007\" rel=\"noopener\">Source code<\/a>","_et_gb_content_width":"","footnotes":""},"categories":[7],"tags":[],"class_list":["post-90","post","type-post","status-publish","format-aside","has-post-thumbnail","hentry","category-php","post_format-post-format-aside"],"_links":{"self":[{"href":"https:\/\/cun.ie\/blog\/wp-json\/wp\/v2\/posts\/90","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cun.ie\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cun.ie\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cun.ie\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cun.ie\/blog\/wp-json\/wp\/v2\/comments?post=90"}],"version-history":[{"count":0,"href":"https:\/\/cun.ie\/blog\/wp-json\/wp\/v2\/posts\/90\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cun.ie\/blog\/wp-json\/wp\/v2\/media\/99"}],"wp:attachment":[{"href":"https:\/\/cun.ie\/blog\/wp-json\/wp\/v2\/media?parent=90"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cun.ie\/blog\/wp-json\/wp\/v2\/categories?post=90"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cun.ie\/blog\/wp-json\/wp\/v2\/tags?post=90"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}