การใช้ DataTables ดึงข้อมูลจาก MySQL

2 มิ.ย. 2020 , 11,746 Views , หมวดหมู่ jQuery,JavaScript,Ajax MySQL PHP โค๊ดดิ้ง , ป้ายกำกับ:data Table, mysql, php

ไฟล์หลัก

<!DOCTYPE html>
<html>
	<title>Datatable Demo </title>
	<head>
		<meta charset="utf-8">
		<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
		<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.css">
		<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
		<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.js"></script>

		<script type="text/javascript" language="javascript" >
			$(document).ready(function() {
				var dataTable = $('#customer-grid').DataTable( {
					"processing": true,
					"serverSide": true,
					"pageLength": 10,
					"ajax":{
						url :"getData.php", // json datasource
						type: "post",  // method  , by default get
						error: function(){  // error handling
							$(".customer-grid-error").html("");
							$("#customer-grid").append('<tbody class="customer-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
							$("#customer-grid_processing").css("display","none");
							
						}
					}
				} );
			} );
		</script>
	</head>
	<body>
		<div class="container-fluid">
			<table id="customer-grid"  cellpadding="0" cellspacing="0" border="0" class="display" width="100%">
				<thead>
					<tr>
						<th>ID</th>
						<th>Name TH</th>
						<th>Name EN</th>
						<th>Code</th>
					</tr>
				</thead>
			</table>
		</div>
	</body>
</html>

<!DOCTYPE html>

<html>

<title>Datatable Demo </title>

<head>

$(document).ready(function() {

var dataTable = $('#customer-grid').DataTable( {

"processing": true,

"serverSide": true,

"pageLength": 10,

"ajax":{

url :"getData.php", // json datasource

type: "post", // method , by default get

error: function(){ // error handling

$(".customer-grid-error").html("");

$("#customer-grid").append('<tbody class="customer-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');

$("#customer-grid_processing").css("display","none");

}

} );

</script>

</head>

<body>

<thead>

<tr>

</tr>

</thead>

</table>

</div>

</body>

</html>

ไฟล์ getData.php

<?php
require_once("../../config.inc.php");

$mysqli = new mysqli($dbconfig['db_server'],$dbconfig['db_username'],$dbconfig['db_password'],$dbconfig['db_name']);	
if ($mysqli->connect_errno) {
	die( "Failed to connect to MySQL Vtiger: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);
}	
$mysqli->set_charset("utf8");

$requestData= $_REQUEST;
 
//ฟิลด์ที่จะเอามาแสดงและค้นหา
$columns = array( 
// datatable column index  => database column name
	0 =>'id', 
	1 => 'name_th',
	2=> 'name_en',
	3=> 'code'
);

$search = "";
if( !empty($requestData['search']['value']) ) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
	$search.=" AND ( ";
	$i = 0;
	foreach($columns as $columnname)
	{
		$search.= $i==0 ? "" : " OR ";
		$search.= $columnname." LIKE '%".$requestData['search']['value']."%' ";
		$i++;
	}
	$search.=")";
}
// getting total number records without any search
$query = "SELECT count(*) AS num_rows ";
$query.=" FROM amphures WHERE 1=1 ".$search;

$getRes = $mysqli->query($query);
$row = $getRes->fetch_assoc();
$totalData = $row['num_rows'];
$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
 
$query = "SELECT ".implode(",",$columns);
$query.=" FROM amphures WHERE 1=1 ".$search;
$query.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']."  
		LIMIT ".$requestData['start'].",".$requestData['length']." ";
$getRes = $mysqli->query($query);
 
$data = array();
while($row = $getRes->fetch_assoc())
{
	$nestedData=array(); 
	foreach($columns as $columnname)
	{
		$nestedData[] = $row[$columnname];
	}	
	$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

?>

<?php

require_once("../../config.inc.php");

$mysqli = new mysqli($dbconfig['db_server'],$dbconfig['db_username'],$dbconfig['db_password'],$dbconfig['db_name']);

if ($mysqli->connect_errno) {

die( "Failed to connect to MySQL Vtiger: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);

}

$mysqli->set_charset("utf8");

$requestData= $_REQUEST;

//ฟิลด์ที่จะเอามาแสดงและค้นหา

$columns = array(

// datatable column index => database column name

0 =>'id',

1 => 'name_th',

2=> 'name_en',

3=> 'code'

);

$search = "";

if( !empty($requestData['search']['value']) ) { // if there is a search parameter, $requestData['search']['value'] contains search parameter

$search.=" AND ( ";

$i = 0;

foreach($columns as $columnname)

{

$search.= $i==0 ? "" : " OR ";

$search.= $columnname." LIKE '%".$requestData['search']['value']."%' ";

$i++;

}

$search.=")";

}

// getting total number records without any search

$query = "SELECT count(*) AS num_rows ";

$query.=" FROM amphures WHERE 1=1 ".$search;

$getRes = $mysqli->query($query);

$row = $getRes->fetch_assoc();

$totalData = $row['num_rows'];

$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.

$query = "SELECT ".implode(",",$columns);

$query.=" FROM amphures WHERE 1=1 ".$search;

$query.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']."

LIMIT ".$requestData['start'].",".$requestData['length']." ";

$getRes = $mysqli->query($query);

$data = array();

while($row = $getRes->fetch_assoc())

{

$nestedData=array();

foreach($columns as $columnname)

{

$nestedData[] = $row[$columnname];

}

$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

ดูเพิ่มเติม
https://datatables.net/examples/

https://devbanban.com/?p=2177

MySQL GROUP_CONCAT order by แล้วตัดมา 1 ค่า…

connect data source ผ่าน gateway error We were…

ป้ายกำกับ:data Table, mysql, php

การใช้ DataTables ดึงข้อมูลจาก MySQL

Related Posts

หมวดหมู่

ติดต่อเรา

ติดตามเรา

การใช้ DataTables ดึงข้อมูลจาก MySQL

Related Posts

หมวดหมู่

Popular Posts

ป้ายกำกับ

ติดต่อเรา

ติดตามเรา