PHP+MySqli EP.16 การทำ Datatable Serverside (แจกสคิป พร้อม สอนวิธีแก้ไขและใช้งาน)
PHP+MySqli EP.16 การทำ Datatable Serverside
ทำเพื่อให้การโหลดข้อมูลจำนวนมาก ๆ โหลดได้ไวขึ้น
ไฟล์ตัวอย่าง
serverside.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
<?php include('condb.php'); // storing request (ie, get/post) global array to a variable $requestData= $_REQUEST; //ฟิลด์ที่จะเอามาแสดงและค้นหา $columns = array( // datatable column index => database column name 0 =>'employee_name', 1 => 'employee_salary', 2=> 'employee_age' ); // getting total number records without any search $sql = "SELECT employee_name, employee_salary, employee_age "; $sql.=" FROM employee"; $query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees"); $totalData = mysqli_num_rows($query); $totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows. $sql = "SELECT employee_name, employee_salary, employee_age "; $sql.=" FROM employee WHERE 1=1"; if( !empty($requestData['search']['value']) ) { // if there is a search parameter, $requestData['search']['value'] contains search parameter $sql.=" AND ( employee_name LIKE '".$requestData['search']['value']."%' "; $sql.=" OR employee_salary LIKE '".$requestData['search']['value']."%' "; $sql.=" OR employee_age LIKE '".$requestData['search']['value']."%' )"; } $query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees"); $totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result. $sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." "; /* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc */ $query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees"); $data = array(); while( $row=mysqli_fetch_array($query) ) { // preparing an array $nestedData=array(); $nestedData[] = $row["employee_name"]; $nestedData[] = $row["employee_salary"]; $nestedData[] = $row["employee_age"]; $data[] = $nestedData; } $json_data = array( "draw" => 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. "recordsTotal" => intval( $totalData ), // total number of records "recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData "data" => $data // total data array ); echo json_encode($json_data); // send data as json format ?> |
index.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
<!DOCTYPE html> <html> <title>Datatable Demo By fordev22.com </title> <head> <meta charset="utf-8"> <link rel="stylesheet" type="text/css" href="css/jquery.dataTables.css"> <script type="text/javascript" language="javascript" src="js/jquery.js"></script> <script type="text/javascript" language="javascript" src="js/jquery.dataTables.js"></script> <script type="text/javascript" language="javascript" > $(document).ready(function() { var dataTable = $('#employee-grid').DataTable( { "processing": true, "serverSide": true, "ajax":{ url :"employee-grid-data.php", // json datasource type: "post", // method , by default get error: function(){ // error handling $(".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> <style> div.container { margin: 0 auto; max-width:760px; } div.header { margin: 100px auto; line-height:30px; max-width:760px; } body { background: #f7f7f7; color: #333; font: 90%/1.45em "Helvetica Neue",HelveticaNeue,Verdana,Arial,Helvetica,sans-serif; } </style> </head> <body> <div class="header"><h1>DataTable demo (Server side) in Php,Mysql and Ajax By fordev22.com </h1></div> <div class="container"> <table id="employee-grid" cellpadding="0" cellspacing="0" border="0" class="display" width="100%"> <thead> <tr> <th>Employee name</th> <th>Salary</th> <th>Age</th> </tr> </thead> </table> </div> </body> </html> |