Tutorials

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", "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 

<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