RESTful best practices Query String Params

filtering_sorting_field_selection_paging.txt

<?php
/*
Based on http://blog.mwaysolutions.com/2014/06/05/10-best-practices-for-better-restful-api/

7. Provide filtering, sorting, field selection and paging for collections
*/


/******

Filtering:

Use a unique query parameter for all fields or a query language for filtering.

GET /cars?color=red Returns a list of red cars
GET /cars?seats<=2 Returns a list of cars with a maximum of 2 seats


*******/


/******

Sorting:

Allow ascending and descending sorting over multiple fields.

GET /cars?sort=-manufactorer,+model

*******/


/******

Field selection

Mobile clients display just a few attributes in a list. They don’t need all attributes of a resource. Give the API consumer the ability to choose returned fields. This will also reduce the network traffic and speed up the usage of the API.

GET /cars?fields=manufacturer,model,id,color

*******/


/******

Paging

Use limit and offset. It is flexible for the user and common in leading databases. The default should be limit=20 and offset=0

GET /cars?offset=10&limit=5

*******/




?>

simple-mysql-restful-adapter.php

<?php

$db = new PDO('mysql:host=localhost;dbname=random_db', 'root', 'root', array(
    PDO::ATTR_PERSISTENT => true
));

require 'Slim/Slim.php';
\Slim\Slim::registerAutoloader();


$app = new \Slim\Slim();

$alltables=$db->query("SHOW TABLES",PDO::FETCH_NUM);

while($result=$alltables->fetch()){
	$app->get('/'.$result[0].'/?', function () use ($app, $db, $result)  {

		parse_str($_SERVER['QUERY_STRING']);

		$fields = (!isset($fields)) ? "*" : implode(explode(",", $fields), ",");
		$limit = (!isset($limit)) ? " LIMIT 100" : " LIMIT " . $limit;
		$offset = (!isset($offset)) ? " " : " OFFSET " . $offset;
		
		$sql = "SELECT ".$fields." FROM ".$result[0]." " . $limit . $offset;

		
		$query = $db->prepare($sql);
		$query->execute();
		header('Cache-Control: no-cache, must-revalidate');
		header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
		header('Content-type: application/json');

		if($query->errorCode() == 0) {
			echo json_encode($query->fetchAll(PDO::FETCH_OBJ));
		} else {
		    $errors = $query->errorInfo();
		    echo json_encode($errors);
		}
	});

	$app->get('/'.$result[0].'/:id?', function ($id) use ($app, $db, $result) {

		parse_str($_SERVER['QUERY_STRING']);

		$fields = (!isset($fields)) ? "*" : implode(explode(",", $fields), ",");
		$limit = (!isset($limit)) ? " LIMIT 100" : " LIMIT " . $limit;
		$offset = (!isset($offset)) ? " " : " OFFSET " . $offset;
		
		$sql = "SELECT ".$fields." FROM ".$result[0]." WHERE id = ".$id." " . $limit . $offset;

		
		$query = $db->prepare($sql);
		$query->execute();
		header('Cache-Control: no-cache, must-revalidate');
		header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
		header('Content-type: application/json');

		if($query->errorCode() == 0) {
			echo json_encode($query->fetchAll(PDO::FETCH_OBJ)[0]);
		} else {
		    $errors = $query->errorInfo();
		    echo json_encode($errors);
		}

	});
}

$app->run();	

?>