Advertisement
Guest User

datatables

a guest
Apr 10th, 2018
118
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.20 KB | None | 0 0
  1. <?php
  2. /* Database connection start */
  3. $servername = "localhost";
  4. $username = "root";
  5. $password = "";
  6. $dbname = "db_x";
  7.  
  8. $conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
  9.  
  10. /* Database connection end */
  11.  
  12.  
  13. // storing request (ie, get/post) global array to a variable
  14. $requestData= $_REQUEST;
  15.  
  16.  
  17. $columns = array(
  18. // datatable column index => database column name
  19. 0 => 'kode_kab',
  20. 1 => 'nama_kab',
  21. 2 => 'nama_prov',
  22. 3 => 'jml_puskesmas',
  23. 4 => 'jml_rs'
  24. );
  25.  
  26. // getting total number records without any search
  27. $sql = "SELECT a.kode_prov, a.nama_prov, b.* FROM tb_kab_kota AS b
  28. INNER JOIN tb_prov AS a USING(kode_prov)";
  29. $query=mysqli_query($conn, $sql) or die("data_kabupaten.php: get InventoryItems");
  30. $totalData = mysqli_num_rows($query);
  31. $totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
  32.  
  33.  
  34. if( !empty($requestData['search']['value']) ) {
  35. // if there is a search parameter
  36. $sql = "SELECT a.kode_prov, a.nama_prov, b.* FROM tb_kab_kota AS b
  37. INNER JOIN tb_prov AS a USING(kode_prov)";
  38. $sql.=" WHERE kode_kab LIKE '".$requestData['search']['value']."%' "; // $requestData['search']['value'] contains search parameter
  39. $sql.=" OR nama_kab LIKE '".$requestData['search']['value']."%' ";
  40. $sql.=" OR nama_prov LIKE '".$requestData['search']['value']."%' ";
  41. $query=mysqli_query($conn, $sql) or die("data_kabupaten.php: get PO");
  42. $totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result without limit in the query
  43.  
  44. $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 , $requestData['start'] contains start row number ,$requestData['length'] contains limit length.
  45. $query=mysqli_query($conn, $sql) or die("data_kabupaten.php: get PO"); // again run query with limit
  46.  
  47. } else {
  48.  
  49. $sql = "SELECT a.kode_prov, a.nama_prov, b.* FROM tb_kab_kota AS b
  50. INNER JOIN tb_prov AS a USING(kode_prov)";
  51. $sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." ";
  52. $query=mysqli_query($conn, $sql) or die("data_kabupaten.php: get PO");
  53.  
  54. }
  55.  
  56. $data = array();
  57. $no = 1;
  58. while( $row=mysqli_fetch_array($query) ) { // preparing an array
  59. $nestedData=array();
  60.  
  61. $nestedData[] = '<center>'.$no++.'</center>';
  62. $nestedData[] = '<span class="text-center">'.$row['kode_kab'].'</span>';
  63. $nestedData[] = $row["nama_kab"];
  64. $nestedData[] = $row["nama_prov"];
  65. $nestedData[] = $row["jml_puskesmas"];
  66. $nestedData[] = $row["jml_rs"];
  67. $nestedData[] = '<td>
  68. <center>
  69. <a href="#edit_kabupaten" data-toggle="modal" data-id="'.$row['kode_kab'].'" style="font-size:12px;text-decoration:none;"><span class="label label-warning">Edit data</span></a>
  70. <a href="function/delete.php?aksi=del_kabupaten&kode_kab='.$row['kode_kab'].'" style="font-size:12px;text-decoration:none;"><span class="label label-warning">Delete data</span></a>
  71. </center>
  72. </td>';
  73.  
  74. $data[] = $nestedData;
  75.  
  76. }
  77.  
  78. $json_data = array(
  79. "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.
  80. "recordsTotal" => intval( $totalData ), // total number of records
  81. "recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
  82. "data" => $data // total data array
  83. );
  84.  
  85. echo json_encode($json_data); // send data as json format
  86.  
  87. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement