Server Paging
Preface
There are 2 ways to enable paging for grid.The first one is static paging on client side.
To enable paging on client side it requires to add property
paging: true
.To enable server sorting it requires to do more actions.
Let us learn it.
Data
We assume that you have data base filled with sql from previous sample.
CREATE DATABASE `company`;
USE `company`;
CREATE TABLE `staff` (
`id` int(11) AUTO_INCREMENT,
`name` VARCHAR(50),
`surname` VARCHAR(50),
`country` VARCHAR(50),
`position` VARCHAR(50),
`email` VARCHAR(50),
`age` INT,
PRIMARY KEY (`id`)
);
INSERT INTO `staff` (`id`, `name`, `surname`, `country`, `position`, `email`, `age`) VALUES
(1, "Taylor", "Davis", "Netherlands", "DevOps", "[email protected]", 36),
(2, "Isabella", "Scott", "Australia", "PHP Developer", "[email protected]", 39),
(3, "Chloe", "Woods", "Sweden", "C++ Developer", "[email protected]", 23),
(4, "Ivan", "Richardson", "Singapore", "C++ Developer", "[email protected]", 28),
(5, "Ivan", "Brown", "Taiwan", "Data Science Engineer", "[email protected]", 24),
(6, "Ella", "Brown", "Sweden", "ASP.NET Developer", "[email protected]", 32),
(7, "Elizabeth", "Scott", "USA", "iOS Developer", "[email protected]", 43),
(8, "Peter", "Johnson", "Taiwan", "C++ Developer", "[email protected]", 42),
(9, "Randy", "Martin", "Netherlands", "C++ Developer", "[email protected]", 27),
(10, "Michael", "Phillips", "UK", "Software Tester", "[email protected]", 23),
(11, "Ed", "Brown", "San Marino", "Python Developer", "[email protected]", 33),
(12, "Luis", "Richardson", "Netherlands", "Software Tester", "[email protected]", 22),
(13, "Ed", "Johnson", "Australia", "ASP.NET Developer", "[email protected]", 44),
(14, "Gavin", "Garcia", "Belgium", "DevOps", "[email protected]", 26),
(15, "Taylor", "Howard", "Taiwan", "JavaScript Developer", "[email protected]", 42),
(16, "Orlando", "Scott", "Ireland", "Frontend Developer", "[email protected]", 26),
(17, "Chloe", "Taylor", "Finland", "Java Developer", "[email protected]", 21),
(18, "Jacob", "Hill", "Japan", "Python Developer", "[email protected]", 25),
(19, "Paula", "Scott", "Netherlands", "Data Science Engineer", "paula.sc[email protected]", 23),
(20, "Lily", "Miller", "Austria", "JavaScript Developer", "[email protected]", 35);
Client side code
Let's prepare HTML and js code.We would need to add property
remotePage: true
.
<html>
<head>
<link href="https://cdn.fancygrid.com/fancy.min.css" rel="stylesheet">
<script src="https://cdn.fancygrid.com/fancy.min.js"></script>
<script>
document.addEventListener("DOMContentLoaded", function() {
new FancyGrid({
title: 'Server Paging',
renderTo: 'container',
width: 550,
height: 500,
data: {
remotePage: true,
proxy: {
url: 'load.php'
}
},
selModel: 'cell',
cellTrackOver: true,
defaults: {
resizable: true,
sortable: true,
draggable: true
},
columns: [{
index: 'name',
title: 'Name'
}, {
index: 'surname',
title: 'SurName'
}, {
index: 'country',
title: 'Country'
}, {
index: 'position',
title: 'Position'
}, {
index: 'email',
title: 'Email'
}]
});
});
</script>
</head>
<body>
<div id="container"></div>
</body>
</html>
Server side code
We will use 2 files.The first one is light class for connecting to DataBase.
DB.php
<?
class DB {
public $host = "localhost",
$dbuser = "user_name",
$dbpassword = "password",
$dbname = "company";
function __construct(){
$this->connect();
$this->mysql_select_db();
}
function connect(){
mysql_connect($this->host,$this->dbuser,$this->dbpassword) || die('could not connect to db');
}
function mysql_select_db(){
mysql_select_db($this->dbname) || die('could not select db');
}
}
?>
The second file is reading data from DataBase.Very important moment is sending headers.
It requires to send
Content-Type
that is equal to application/json
.And some extra code special for paging.
load.php
<?
header('Content-Type: application/json');
require_once("DB.php");
$db = new DB();
$page = $_GET['page'];
if(isset($_GET['limit'])){
$limit = $_GET['limit'];
}
else{
$limit = 10;
}
$totalCount = 0;
$start = $page * $limit;
$sql_query = "SELECT * FROM `staff` LIMIT $start, $limit;";
$result_db_request = mysql_query($sql_query);
for($dataArr=array();$row=mysql_fetch_assoc($result_db_request);$dataArr[]=$row){}
$data = '[ ';
for($i = 0, $iL = count($dataArr); $i < $iL; $i++){
$data .= '{ ';
$row = $dataArr[$i];
$data .= '"id": '. $row[ 'id' ] . ',"name": "'. $row[ 'name' ] . '","surname": "' . $row[ 'surname' ] . '","position": "' . $row[ 'position' ] . '","country": "' . $row[ 'country' ] . '","email": "' . $row[ 'email' ] .'"';
$data .= '},';
}
$data = substr($data, 0, -1);
$sql_query = "SELECT * FROM `staff`;";
$result_db_request = mysql_query($sql_query);
$totalCount = mysql_num_rows($result_db_request);
$data .= ']';
$responce .= '{"success":true,';
$responce .= "\"data\": $data,";
$responce .= "\"totalCount\": $totalCount";
$responce .= '}';
echo $responce;
?>
Live Sample
Server PagingMethod
By default method is'GET'
.To change method it requires to set it for every requests type.
data: {
proxy: {
methods: {
create: 'POST',
read: 'POST',
update: 'POST',
destroy: 'POST'
},
...
}
}
Request Params
- limit - rows to display
- page - page to display