Bayesean Blog - Desktop, Mobile and IOT Developer Blog


PHP PDO REST Server Part 1

Posted on 8th Aug 2017 in Bootstrap 3, PHP


restserver.png

 

 

This will cover the implementation of a REST Back-end for a Webserver. The PHP SLIM framework library is lightweight and fast. Using this framework eliminates the need to specially write your own which could easily face security issues.  Although REST provides the required connections, the developer will need to write specific code for the Routes and Middleware to ensure that the dedicated URL’s are setup correctly.

 

Scope

Set up a PHP REST Server and use a Debugger to test the Web Server.

 

Setting Up

It is best to use a local XAMP or WAMP setup, an open source Apache Server. This will load PHP, MYSQL or MARIA database server depending on your Installation. Maria DB is an on-going Open Source alternative to MySQL DB and is designed by the same developers for MySQL DB. I am not going to explain how to set your Apache server for a quick Google on this subject will provide the required steps to set this up.

Composer is used to download SLIM 3 into your project. The SLIM 3 documentation explains how to perform this process.

Bootstrap 3 is a Responsive mobile first framework and is only used to View the database.

A live test REST Server example of the code is available so that calls can be easily made from a REST DEBUGGER and ultimately from the various working DELPHI CLIENT Applications that will be presented over the FIVE PART SERIES. This TEST REST SERVER allows both HTTP and HTTPS Calls to be made to it.  

File Structure

 reststructure.PNG

  • Folder dist:- holds the bootstrap 3 library for the view.php file.
  • Folder public :-contains the index.php to which all the url calls are made to.
  • .htaccess is an apache file.
  • The View.php:- shows a table view of the database. A button will call the Resetdb.php which in turn resets the Database. This is handy should all the data be deleted in your LocalHost Server.
  • Folder src:- contains the config folder for the db.php which connects to the MySql / Maria Database.
  • Folder routes:- has the pricing.php file which is file that will technically process all the REST Requests and Responses.
  • Folder vender:- is the Slim library.

 

Database

As this is a five part series Covering a pricelist REST server. The salesREST.sql file has a single

pricelist table …

 

`salesREST`

 Table structure for table `pricelist`

CREATE TABLE `pricelist` (

  `id` int(11) NOT NULL,

  `sku` varchar(20) DEFAULT NULL,

  `skusize` varchar(20) DEFAULT NULL,

  `barcode` varchar(25) DEFAULT NULL,

  `description` varchar(100) DEFAULT NULL,

  `price` float(30,2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Create the index.php

 

To activate SLIM framework, we add the four lines below.

 

 

 

 Adding the Database connection db.php

This uses the PDO connection procedure and is wrapped in a class database.

 To connect call $pdo = Database::connect();

 To Disconnect call Database::disconnect();

 

 

   

Create pricing.php

 

  • This file will handle the GET, PUT, POST and DELETE calls.

 

Include the Database access in the config.

Include '../src/config/db.php';

 

  • First the call to use the REST Request interface and the REST Response interface of SLIM.
  • Then call the SLIM framework.
  • SLIM app told to use the folder routes and the function requirement is REST Request returning a REST Response (back to the client).
  • A middleware function is created by calling ‘$app-> add’ to accept authorization Headers (if required) and to accept the methods GET, POST, PUT and DELETE and OPTIONS. This invokes a PSR 7 Response ‘withHeader’.
use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;

$app = new \Slim\App;

$app->options('/{routes:.+}', function ($request, $response, $args) {
    return $response;
});

$app->add(function ($req, $res, $next) {
    $response = $next($req, $res);
    return $response
            ->withHeader('Access-Control-Allow-Origin', '*')
            ->withHeader('Access-Control-Allow-Headers', 'X-Requested-With, Content-Type, Accept, Origin, Authorization')
            ->withHeader('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS');
});

 

  • Call to SLIM to get the URL and the function to process the REST Request and return a REST Response.
  • The pricelist table is accessed and all data is fetched as a PDO object then encoded into JSON format, sent as the REST Request to the Client. If there is an error then the Error REST Response is sent.

 

//Get All Products and Prices
$app->get('/api/pricing', function (Request $request, Response $response) {
  	$sql = "SELECT * FROM pricelist";
  		try{
  
  		$pdo = Database::connect();
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		
		$stmt = $pdo->query($sql);
		$pricing = $stmt->fetchAll(PDO::FETCH_OBJ);
		Database::disconnect();
		echo json_encode($pricing);

} catch(PDOException $e){
	echo '{"error": {"text": '.$e->getMessage().'}';
}
});

  • The URL with {id} means that a row id number must be added to the URL.
  • The id is received by using the SLIM getattribute(‘id’) procedure.
  • The single row is fetched as a PDO OBJECT then encoded into JSON and sent as a REST Response.
//Get single Product Priced unit
$app->get('/api/pricing/{id}', function (Request $request, Response $response) {

$id = $request->getAttribute('id');

  	$sql = "SELECT * FROM pricelist where id = $id";
  		try{
    	$pdo = Database::connect();
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

		$stmt = $pdo->query($sql);
		$pricing = $stmt->fetchAll(PDO::FETCH_OBJ);
		Database::disconnect();
		echo JSON_encode($pricing);
} catch(PDOException $e){
	echo '{"error": {"text": '.$e->getMessage().'}';
}
});
  • Adding product requires the getParam procedure to extract the JSON code directly.
  • Calling the INSERT sql will insert the record by using the prepare statement and bindparam to add the row items.

 

// Add Product and Pricing single field
   $app->post('/api/pricing/add', function(Request $request, Response $response){

    $sku = $request->getParam('sku');
    $skusize = $request->getParam('skusize');
    $barcode = $request->getParam('barcode');
    $description = $request->getParam('description');
    $price = $request->getParam('price');

	$sql = "INSERT INTO pricelist (sku,skusize,barcode,description,price)
	 values(:sku ,:skusize, :barcode, :description, :price)";

    try{
        $pdo = Database::connect();
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $pdo->prepare($sql);

        $stmt->bindParam(':sku', $sku);
        $stmt->bindParam(':skusize',  $skusize);
        $stmt->bindParam(':barcode',      $barcode);
        $stmt->bindParam(':description',      $description);
        $stmt->bindParam(':price',    $price);

        $stmt->execute();
		Database::disconnect();
        echo '{"notice": "Single Product and Pricing Added"}';

    } catch(PDOException $e){
        echo '{"error": {"text": '.$e->getMessage().'}';
}
});
  •  Adding all the products requires a request using the getBody procedure.
  • The JSON decodes the object into an array.
  • Each row is looped using the foreach call and the row items are updated using the bindValue procedure.

 

// Add Product and Pricing many fields
$app->post('/api/pricing/add/all', function(Request $request, Response $response){
$data = '['.$request->getBody().']';
$data=json_decode($data,true);
try{
  
//This is a slow method of update as each row is updated one at a time. Should Update once only
       $pdo = Database::connect();
       $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
       $sql = "INSERT INTO pricelist (sku,skusize,barcode,description,price)
       values(:sku ,:skusize, :barcode, :description, :price)";
       $stmt = $pdo->prepare($sql);

  foreach ($data as $data){

       $stmt->bindValue(':sku',        $data['sku']);
        $stmt->bindValue(':skusize',    $data['skusize']);
       $stmt->bindValue(':barcode',    $data['barcode']);
        $stmt->bindValue(':description',$data['description']);
        $stmt->bindValue(':price',      $data['price']);
         $stmt->execute();
     }
        Database::disconnect();
        echo '{"notice": "All Products and Pricing Added "}';
  
    } catch(PDOException $e){
        echo '{"error": {"text": '.$e->getMessage().'}';
}
});

  • Updating uses the id with get Attribute.
  • The getParam procedure converts the JSON into a PhP string variable.
  • The SQL call to UPDATE updates the row as per the id number.

 

// Update Pricing (Put)
$app->put('/api/pricing/update/{id}', function(Request $request, Response $response){
    $id = $request->getAttribute('id');
    $sku = $request->getParam('sku');
    $skusize = $request->getParam('skusize');
    $barcode = $request->getParam('barcode');
    $description = $request->getParam('description');
    $price = $request->getParam('price');

    $sql = "UPDATE pricelist SET
				sku 	     = :sku,
				skusize 	     = :skusize,
              				 barcode    = :barcode,
                			description= :description,
                			price 	      = :price
			WHERE    id              = $id";

    try{
        // Get DB Object
        $pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $pdo->prepare($sql);

        $stmt->bindParam(':sku',        $sku);
        $stmt->bindParam(':skusize',    $skusize);
        $stmt->bindParam(':barcode',    $barcode);
        $stmt->bindParam(':description',$description);
        $stmt->bindParam(':price',      $price);

        $stmt->execute();
        Database::disconnect();
        echo '{"notice": {"text": "Product and Price Updated"}';

    } catch(PDOException $e){
        echo '{"error": {"text": '.$e->getMessage().'}';
    }
});

 

  •  One URL is set for delete all and delete by id.
  • Delete all when id = ‘0’ and the table is truncated or cleared.
  • If the Id then a prepare statement is processed on the ID received with a client response.
// Delete price(product)
$app->delete('/api/pricing/delete/{id}', function(Request $request, Response $response){
    $id = $request->getAttribute('id');

   if ($id==0){
    $sql = "TRUNCATE TABLE pricelist "; 
      }else{
   $sql = "DELETE FROM pricelist WHERE id = $id";   
   }
   try{
        $pdo = Database::connect();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $pdo->prepare($sql);
        $stmt->execute();
      Database::disconnect();
       if ($id==0){
         echo '{"notice": "All Product and Prices Deleted"}';
       }else{
        echo '{"notice": "Product and Price Deleted"}';
       }
    } catch(PDOException $e){
        echo '{"error": {"text": '.$e->getMessage().'}';
    }
});
 ?>

 

 

.htaccess

This needs to be placed in the index.php folder. It ia an Apache file that rewrites the urls to be read correctly.

 

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^ index.php [L]

 

 

 

 

Testing and Debugging

There are many free available Debuggers on the market. As the Five part series covers a Delphi Client, I am making use of the free to download Embarcadero Debugger.

delphirestdebug.PNG 

API Calls

The ‘Live View’ Sales REST demo accepts both http and https calls.

 

API Calls -    id (integer) row no of the database table

Get All 'http://www.bayeseanblog.com/blog/public/demo/salesREST/public/api/pricing/'

Get One 'http://www.bayeseanblog.com/blog/public/demo/salesREST/public/api/pricing/id'

Put One 'http://www.bayeseanblog.com/blog/public/demo/salesREST/public/api/pricing/update/id'

Post 'http://www.bayeseanblog.com/blog/public/demo/salesREST/public/api/pricing/add'

Delete 'http://www.bayeseanblog.com/blog/public/demo/salesREST/public/api/pricing/delete/id'

Delete All 'http://www.bayeseanblog.com/blog/public/demo/salesREST/public/api/pricing/delete/0

 

You can also send this request to your localhost. Be sure to have the index.php live in the browser. It is correct not to show any data.

Change the first-part of the URL to your localhost URL and the debugger will process the REST requests and responses on your local machine. Example:-

Get All ‘localhost/public/api/pricing/'

 

 

REST DEBUGGER  using GET call

 

Get All 'http://www.bayeseanblog.com/blog/public/demo/salesREST/public/api/pricing/'

restdebugget.PNG

The content type is set to application/JSON, add the ‘get URL ‘ and send the request.

REST DEBUGGER  using PUT call.

 

restdebugput.PNG

Set the Method to PUT and Add the URL as above.

Add to the Custom Body JSON code – an existing entry but changed

Example of the content

{

“id”:”3”,

“sku”: TAS013”,

“skusize”:”1x6”,

“barcode”:”6004567893”,

“description”:”Baby Food”,

“price”:”45.78”

}

Send Request…

 

REST DEBUGGER  using POST call

restdebugpost.PNG

Set the Method to POST and Add the URL as above.

Create JSON text as above and insert it in the custom body.

{

“id”:”55”,

“sku”: CRA013”,

“skusize”:”1x6”,

 “barcode”:”6004567893”,

“description”:”Baby Food”,

 

“price”:”45.78”

}

Send Request…..

The Response will reply with a notice {Product and Pricing Added}

 

REST DEBUGGER  using DELETE call

To delete one record

restdebugdelete.PNG

To delete ALL records

 

Change Method to Delete and Add the URL followed by ‘0’ . Send Request and the response will confirm That ALL the Product and Price was deleted.

Feel free to download the Code and also make use of the Live REST debugger to make calls to test how the REST SERVER functions when using your REST DEBUGGER.

 

Live View  Download Code

Happy Debugging


Be the First to Comment...

Add a Comment

9+5

Recent News

Delphi A Professional VCL DBGrid Part Four
Delphi A Professional VCL DBGrid Part Three
Delphi A Professional VCL DBGrid Part Two
Delphi A Professional VCL DBGrid Part One
Delphi VCL Buttons in DBGrid
Two Helper Apps for Delphi LibUSB
Delphi Libusb Library Introduction
Delphi Object directly to a Json string in a REST Client

Categories

Bootstrap 4
Delphi VCL
Delphi FMX
Ajax
Bootstrap 3
CSS
XE4>Delphi > XE4
Delphi < XE4
PHP

Archives

June 2019

Delphi A Professional VCL DBGrid Part Four

May 2019

Delphi A Professional VCL DBGrid Part Three

April 2019

Delphi A Professional VCL DBGrid Part Two

March 2019

Delphi A Professional VCL DBGrid Part One

November 2018

Delphi VCL Buttons in DBGrid

October 2018

Two Helper Apps for Delphi LibUSB

September 2018

Delphi Libusb Library Introduction

August 2018

Delphi Object directly to a Json string in a REST Client
Delphi using Environment Variables in your App

July 2018

Delphi FMX Leaflet Plotter using OSM Maps

June 2018

C2PAS32 Convertor Application
C to Delphi Open Source Convertors Shootout
Delphi command-line programs with DOSCommand

May 2018

Delphi PDF Embedded viewer with PDF.js

March 2018

Delphi FMX - Changing TCharacter to TCharHelper
Make Your Delphi App POP using Javascript!

January 2018

Delphi FMX Dashboard using Chart.JS
Delphi FMX Form Docking

December 2017

PHP Slim REST Server & Delphi Auth Part 5

November 2017

Delphi FMX REST Client App Part 4

October 2017

Delphi VCL REST Pricing Client App Part 3

September 2017

Delphi REST VCL Client Basic Auth Part 2B

August 2017

Delphi REST Client Part 2A
PHP PDO REST Server Part 1

July 2017

PHP REST Server and Delphi Client Intro

June 2017

Delphi SQLite Encryptor-Decryptor Tool
Updating Applications Manifest using Delphi

May 2017

Create a Visual IP Address Geolocation with PHP

March 2017

PHP Downloader using Countdown timer
PHP File Downloader from a Inbox Selection

February 2017

Javascript Image-File Uploader with ThumbViewer

January 2017

Morris Charts and PHP-PDO

December 2016

CSS to create a functional Toggle Button