Tutorials

Server CRUD

Preface

All server side issues are very complex.
Self CRUD and RESTful are ones of the most complex parts of working with server.
So let us learn how to do self CRUD.

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 url-s for 4 requests types.
Also we need to add some code for add and delete rows.
To enable cells editing it requires to add property editable: true for columns.
There are variety of varients to do add and delete rows, we will do one of the simplest way. 

<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: {
		    api: {
              create: 'new.php',
			  read: 'load.php',
			  update: 'update.php',
			  destroy: 'destroy_action.php'
		    }
		  }
		},
		selModel: 'row',
		trackOver: true,
		tbar: [{
		  type: 'button',
		  text: 'Add',
		  action: 'add'
		},{
		  type: 'button',
		  text: 'Delete',
		  action: 'remove'
		}],
		defaults: {      
		  resizable: true,
		  sortable: true,
		  editable: 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 5 files
The first one is light class for connecting to DataBase and others for read, delete, update, insert data.

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');
  }
}
?>

load.php

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.

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

  require_once("DB.php");
  $db = new DB();
  
  $sql_query = "SELECT * FROM `staff`";
  
  $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;
?>

destroy_action.php

The third file is deleting data from DataBase.
To delete row from DataBase grid sends request with id. 

<?
  header('Content-Type: application/json');
  
  require_once("DB.php");
  $db = new DB();
  $id = $_GET['id'];
	
  $sql = "DELETE FROM `users` WHERE `id` = " . $id;
	
  mysql_query($sql);
  
  echo '{"success":true}';
?>

new.php

The fourth file is adding data to DataBase.

<?
  header('Content-Type: application/json');
  
  require_once("DB.php");
  $db = new DB();
  
  $sql = "INSERT INTO `staff` () VALUES ();";
  mysql_query($sql);  
  $result = mysql_query('SELECT * FROM `staff` ORDER BY ID DESC LIMIT 1');
  $row = mysql_fetch_assoc($result);
	
  echo '{"success":true,"message":"Created new User","data":{"id":'.$row['id'].'}}';
?>

update.php

The fifth file is adding data to DataBase.
Grid sends 3 params 'id', data 'key', new 'value'. 

<?
  header( 'Content-Type: application/json' );
  
  require_once("DB.php");
  $db = new DB();

  $id = $_GET['id'];
  $key = $_GET['key'];
  $value = $_GET['value'];
  
  $sql = "UPDATE `staff` SET `$key`='$value' WHERE `id` = $id;";
  mysql_query($sql);
  
  echo '{"success":true}';
?>

Live Sample

CRUD

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