MySQLi

Preface

In server section we will learn how to handle with data that stored on server in database.
We will use the most simple stack PHP and MySQL to avoid complex issues.

Data

First of all you need data for your sample in SQL.
If you don't have real data, you can try to do it manually or generate over some services like Mockaroo.
So we have generated SQL data.

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", "[email protected]", 23),
(20, "Lily", "Miller", "Austria", "JavaScript Developer", "[email protected]", 35);

Client side code

Let's prepare HTML and js code

<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: {
		  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->check_connection();
  }
  
  function connect(){
    $this->link = new mysqli($this->host, $this->dbuser, $this->dbpassword, $this->dbname);
  }
	
  function check_connection(){
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }
  }
}
?>
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.

load.php


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

  require_once("DB.php");
  $db = new DB();
  
  $sql_query = "SELECT * FROM `staff`";
  
  $sql = $sql_query;
  
  $result_db_request = mysqli_query($db->link, $sql_query);
  for($dataArr=array();$row=$result_db_request->fetch_assoc();$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

Load data from DataBase