I tried doing the remote sorting method but the fetched data does not appear in the grid
Javascript part(fetchData.php):
<?php
require_once 'require/include.php';
?>
<html>
<head>
</head>
<body>
<script>
$(function () {
var colM = [
{ title: "Excel ID", width: 100, dataIndx:"ExcelID" },
{ title: "Excel Name", width: 130, dataIndx:"ExcelName" },
{ title: "Excel Email", width: 100, dataIndx:"ExcelEmail", dataType:"string" }
];
var dataModel = {
location: "remote",
sorting: "remote",
dataType: "JSON",
method: "GET",
sortIndx: ["ExcelID"],
sortDir: ["down"],
//url: "/pro/orders/get", //for ASP.NET
url: "orders.php", //for PHP
getData: function (dataJSON) {
return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: dataJSON.data };
}
}
var gObj = {
width: 700, height: 400,
dataModel: dataModel,
wrap: false,
hwrap: false,
showBottom:false,
collapsible:false,
colModel: colM,
title: "Excel Table",
resizable: true,
columnBorders: true,
freezeCols: 0
};
var $grid = $("div#grid_sorting_remote").pqGrid(gObj);
//bind the select list.
$("#grid_remote_sorting_select").change(function (evt) {
var val = $(this).val();
var DM = $grid.pqGrid("option", "dataModel");
if (val == "multiple") {
DM.sortIndx = [DM.sortIndx];
DM.sortDir = [DM.sortDir];
}
else {
DM.sortIndx = DM.sortIndx[0];
DM.sortDir = DM.sortDir[0];
}
$grid.pqGrid("option", "dataModel", DM);
$grid.pqGrid("refreshDataAndView");
});
});
</script>
<div style="margin: auto; width: 250px; margin-bottom: 20px;">
Sorting type:
<select id="grid_remote_sorting_select">
<option value="single">Single Column</option>
<option value="multiple" selected="">Multiple Column</option>
</select>
</div>
<div id="grid_sorting_remote" style="margin: 5px auto;">
</div>
</body>
</html>
PHP part(orders.php):
<?php
require_once 'require/conf.php';
class ColumnHelper
{
public static function isValidColumn($dataIndx)
{
if (preg_match('/^[a-z,A-Z]*$/', $dataIndx))
{
return true;
}
else
{
return false;
}
}
}
class SortHelper
{
public static function deSerializeSort($pq_sort)
{
$sorters = json_decode($pq_sort);
$columns = array();
$sortby = "";
foreach ($sorters as $sorter){
$dataIndx = $sorter->dataIndx;
$dir = $sorter->dir;
if ($dir == "up")
{
$dir = "asc";
}
else
{
$dir = "desc";
}
if (ColumnHelper::isValidColumn($dataIndx))
{
$columns[] = $dataIndx . " " . $dir;
}
else{
throw new Exception("invalid column ".$dataIndx);
}
}
if (sizeof($columns) > 0)
{
$sortby = " order by " . join(", ", $columns);
}
return $sortby;
}
}
//orders.php
$sortQuery = "";
if(isset($_GET["pq_sort"]))
{
$pq_sort = $_GET["pq_sort"];
$sortQuery = SortHelper::deSerializeSort($pq_sort);
}
$sql = "SELECT * FROM tbl_excel ".$sortQuery;
$dsn = 'mysql:host='.DB_HOSTNAME.';dbname='.DB_NAME;
$options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
$dbh = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);
$stmt = $dbh->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "{\"data\":". json_encode( $rows ) ." }" ;
?>
