Tutorials

Server Sorting

Preface

There are 2 ways to enable sorting for grid.
The first one is static sorting on client side.
To enable sorting on client side it requires to add property sortable: true to columns.
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", "taylor.davis@fancygrid.com", 36),
(2, "Isabella", "Scott", "Australia", "PHP Developer", "isabella.scott@fancygrid.com", 39),
(3, "Chloe", "Woods", "Sweden", "C++ Developer", "chloe.woods@fancygrid.com", 23),
(4, "Ivan", "Richardson", "Singapore", "C++ Developer", "ivan.richardson@fancygrid.com", 28),
(5, "Ivan", "Brown", "Taiwan", "Data Science Engineer", "ivan.brown@fancygrid.com", 24),
(6, "Ella", "Brown", "Sweden", "ASP.NET Developer", "ella.brown@fancygrid.com", 32),
(7, "Elizabeth", "Scott", "USA", "iOS Developer", "elizabeth.scott@fancygrid.com", 43),
(8, "Peter", "Johnson", "Taiwan", "C++ Developer", "peter.johnson@fancygrid.com", 42),
(9, "Randy", "Martin", "Netherlands", "C++ Developer", "randy.martin@fancygrid.com", 27),
(10, "Michael", "Phillips", "UK", "Software Tester", "michael.phillips@fancygrid.com", 23),
(11, "Ed", "Brown", "San Marino", "Python Developer", "ed.brown@fancygrid.com", 33),
(12, "Luis", "Richardson", "Netherlands", "Software Tester", "luis.richardson@fancygrid.com", 22),
(13, "Ed", "Johnson", "Australia", "ASP.NET Developer", "ed.johnson@fancygrid.com", 44),
(14, "Gavin", "Garcia", "Belgium", "DevOps", "gavin.garcia@fancygrid.com", 26),
(15, "Taylor", "Howard", "Taiwan", "JavaScript Developer", "taylor.howard@fancygrid.com", 42),
(16, "Orlando", "Scott", "Ireland", "Frontend Developer", "orlando.scott@fancygrid.com", 26),
(17, "Chloe", "Taylor", "Finland", "Java Developer", "chloe.taylor@fancygrid.com", 21),
(18, "Jacob", "Hill", "Japan", "Python Developer", "jacob.hill@fancygrid.com", 25),
(19, "Paula", "Scott", "Netherlands", "Data Science Engineer", "paula.scott@fancygrid.com", 23),
(20, "Lily", "Miller", "Austria", "JavaScript Developer", "lily.miller@fancygrid.com", 35);

Client side code

Let's prepare HTML and js code.
We would need to add property remoteSort: 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({
		renderTo: 'container',
		width: 550,
		height: 500,
		data: {
		  remoteSort: 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 exactly 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 sorting.

load.php


<?
  header('Content-Type: application/json');

  require_once("DB.php");
  $db = new DB();
  
  $sort = '';
  $dir = '';
  
  if( isset($_GET['sort']) && isset($_GET['dir']) ){
    $sort = $_GET['sort'];
    $dir = $_GET['dir'];
  }
  
  $sql_query = "SELECT * FROM `staff`";
  
  if( $sort != '' ){    
    $sql_query .= " ORDER BY `$sort` $dir ";
  }
  
  $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);
   
  $data .= ']';
  
  $responce .= "{\"success\":true,\"data\": $data}";

  echo $responce;
?>

Live Sample

Server Sorting

Method

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'
    },
	...
  }
}