RESTful

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 RESTful.

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", "[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 define type of data for restful type: 'rest' and
url of requests url: 'restful_url'.

<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: {
		    type: 'rest',
		    url: 'app.php/staff'
		  }
		},
		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 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');
  }
}
?>

app.php

The second file is read/delete/update/insert 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();
  
  $data = '[ ';

  $method = $_SERVER["REQUEST_METHOD"];
  $path = $_SERVER["PATH_INFO"];

  switch($method){
    case 'GET':
      $sql_query = "SELECT * FROM `staff`";
  
      $result_db_request = mysql_query($sql_query);
      for($dataArr=array();$row=mysql_fetch_assoc($result_db_request);$dataArr[]=$row){}
  
      for($i = 0, $iL = count($dataArr); $i < $iL; $i++){
        $row = $dataArr[$i];    
        $data .= '{"id":' . $row['id'] . ',"country":"'  . $row[ 'country' ] . '","name":"' . $row[ 'name' ] . '","surname":"' . $row[ 'surname' ] . '","email":"' . $row[ 'email' ] . '","position":"' . $row[ 'position' ] . '"},';
      }
  
      $data = substr($data, 0, -1);
  
      $data .= ']';
  
      $responce .= "{\"success\":true, \"data\": $data}";

      echo $responce;
	  break;
    case 'POST':
      $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'].'}}';
      break;
    
    case 'PUT':
	  parse_str(file_get_contents('php://input'), $data);
	  
      $id = $data['id'];
      $key = $data['key'];
      $value = $data['value'];
    
      $sql = "UPDATE `staff` SET `$key` = '$value' WHERE `id` = $id";
      mysql_query($sql);
  
      $responce = '';
  
      $responce .= "{\"success\":true, \"message\": \"Updated User $id\"}";
  
      echo $responce;
	  break;
	case 'DELETE':
	  parse_str(file_get_contents('php://input'), $data);
      $id = $data['id'];
    
      $sql = "DELETE FROM `staff` WHERE `id` =$id;";
  
      mysql_query($sql);
  
      $responce .= "{\"success\":true, \"message\": \"Destroyed User $id\"}";
  
      echo $responce;
	  break;
  }
?>
As you can see RESTful is not simple. But the code above is one the most simple realization.

Live Sample

RESTful

Method

By default methods for RESTful are
  • read - GET
  • create - POST
  • update - PUT
  • destroy - DELETE

To change methods it requires to set it for every requests type.

data: {
  proxy: {
    methods: {
      create: 'POST',
      read: 'POST',
      update: 'POST',
      destroy: 'POST'
    },
	...
  }
}