Technical note book of Chatura Dilan

PHP REST Data Services – Getting Started

This is an old tutorial, Please find the new tutorial from this link: http://technotes.dilan.me/?p=116

Want to create a REST web service from your favourite database in your Apache Web server which has PHP running in it? Simply saying, what if you want to create a REST web service out of your MYSQL database in a PHP shared environment. With the PHP REST Data Services, you don’t need to worry about writing heavy codes to generate JSON REST services from your MYSQL databases. It will do everything for you. You can also apply various types of security mechanisms including Social Network Authentications (With Hybrid Auth)

If you are not familiar with REST services. Please read this before continue. http://www.ibm.com/developerworks/webservices/library/ws-restful/

This framework is an open source project which you can use to freely generate data services for your mobile based, web based and other projects. Whether you have your databases in a high paid cloud, or low paid shared hosting space you can use this framework.

This project is initiated and coded by me (Chatura Dilan) for final year project of my Master’s degree.

Complete tutorial of PHP REST Data Services – Getting Started.

1. First you need to checkout the Source code from GIThub from following location to your Apache server.

http://chaturadilan.github.io/PHP-Data-Services/

https://github.com/chaturadilan/PHP-Data-Services

(or download from the beta release https://github.com/chaturadilan/PHP-Data-Services/releases )

2. Go to Mysql and create a database call ‘datas’ or any name you want

3. Select the /app/Config/Schema/datas.sql folder inside the app and import it to your MySQL database.

4. Please change the following setting of /app/Config/database.php file to match with your database settings.

public $default = array(
'datasource' => 'Database/Mysql',
'persistent' => false,
'host' => 'localhost',
'login' => 'root',
'password' => '',
'database' => 'datas',
'prefix' => ''
);

(Note: Please add necessary write permissions to /app/tmp/cache folder if you are getting error: PHP versions requred : 5.3 and above and MySQL version required: 5.2 and above)

5. Then you will get the following screen. Login to the system username: admin and password: admin (Please make sure to change the admin password, if you use this in a production machine).

1

6. Now you are done. Let’s create our APIs according to the below story and apply some authentication mechanisms to it.

Story: You have a Tourism application which allows users to  add comments to cities in provinces of Sri Lanka. Users need to login via Facebook to add comments to cities. Everyone can see the comments. Admins can manage comments, cities and provinces.

In this case we are going to use 5 database tables in the ‘tourism’ database.

i. provinces ( has records of all provinces in Sri Lanka, A province can have multiple cities)
ii. cities (has records of cities. city is associate with a one province)
iii. users (users who are registered with the system, who can add comments.)
iv. authentications (autentication information about each users against social networking sites)
v. comments ( has records of comments added by user)

Our user cases are folllowing

a. Users can register to the system via Facebook.

b. Registered users login via Facebook can view only his comments and add comments. Comments are stored by the user ID.

c. Everyone can view list of provinces, cities and comments. They can also view all the cities in a single province.

d. Admin can add, edit, delete provinces, cities and comments.

7. Suppose you have a MySQL database (in this case we will use this following sample database called ‘tourism’) with some data in it.

CREATE TABLE IF NOT EXISTS `authentications` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL COMMENT 'refer to users.id',
`provider` varchar(100) NOT NULL,
`provider_uid` varchar(255) NOT NULL,
`email` varchar(200) NOT NULL,
`display_name` varchar(150) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`profile_url` varchar(300) NOT NULL,
`website_url` varchar(300) NOT NULL,
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `provider_uid` (`provider_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `cities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`province_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `cities` (`id`, `name`, `province_id`) VALUES
(1, 'Colombo', 1),
(2, 'Gampaha', 1),
(3, 'Anuradhapura', 2),
(4, 'Polonnaruwa', 2),
(5, 'Kandy', 3);

CREATE TABLE IF NOT EXISTS `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`comment` text NOT NULL,
`user_id` int(11) NOT NULL,
`city_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `provinces` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `provinces` (`id`, `name`) VALUES
(1, 'Western'),
(2, 'North Central'),
(3, 'Central'),
(4, 'Eastern');

CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(200) NOT NULL,
`password` varchar(200) NOT NULL,
`first_name` varchar(200) NOT NULL,
`last_name` varchar(200) NOT NULL,
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

8. First you need to create a Data Provider to connect to the database. Click on ‘Data Provider’ top menu and add a new data provider entering details of your database server. (Currently it supports only MySQL and make sure that your DMBS host has the connection to the data services application server.). When entering data, select the Provider Source Type as MySQL and click on ‘Load Init Params’ button. It will load the JSON string which you can enter the details about your database server. Click Save.

2

3

9. Now we are going to create our Tourism Data App. Clcik on Data App menu and select ‘Add’. Enter Name, Alias, Details and secret key. (NOTE: Please make sure Alias is unique to each data app and it should only be a characters. Dashes can also be added to Alias. Enter any random string as the secret key of the application.) If you want to test your application make the SandBox mode on. This enable you to access your Data App’s services with the secret key. Make the published filed checked as the figure and click Save.

4

10. Now you have your ‘Tourism’ Data App. Now ‘Go Into’ your app and go to ‘Data Collections’ section. you can and multiple data collection to your app which associate with various data providers. In our scenario we need  3 data collections. One is for Public users, one is for Registered user and the other is for Admin user. (NOTE: Make sure that Data Collection alias should be unique to each collection in the Data App)

Data Collection 1
Name -> Tourism Public API
Alias -> tourism-public
Data Provider -> (the provider you created earlier)
Database -> tourism
Published -> Yes

Data Collection 2
Name -> Tourism User API
Alias -> tourism-user
Data Provider -> (the provider you created earlier)
Database -> tourism
Published -> Yes

Data Collection 3
Name -> Tourism Admin API
Alias -> tourism-admin
Data Provider -> (the provider you created earlier)
Database -> tourism
Published -> Yes

5

11. Got into public Api. In this case we need to enable the retrieve operations of comments, cities and provinces. Click on CRUD Operations Tab and it will list all the tables associate with your database. Think the retrieve operations of comments, cities and provinces tables. You can test these operations by clicking the test link (GET Operation) in the Test column.

6

7

12. Now we need to create an custom operation to retrieve data of the cities for given province. To create a custom operations, Select ‘Custom Methods’ tab Click ‘Add’. ( When retrieving you can send ‘limit’ and ‘offest’ parameters in query string to for pagination)

Enter following details

Name -> getCitiesByProvinceID (Make sure this is unique to alias, and no spaces between characters)
Alias -> cities (The main table name)
Command -> SELECT * FROM cities where province_id = {{id}}  (You can have many number of input parameters between double curly brackets)
HTTP Method -> GET
Published -> Yes

8

13. Now you have one input parameters called ‘id’. We need to make this parameter required. Click on ‘Input Parameters’ of the method and you will see the ‘id’ field is there. Click ‘Edit’ and make the filed required. You can also add input validations such as email, url or custom to this parameter in this screen. Since this is an ‘id’ change the validation type to numeric and save.

14. now Go back to method and click on ‘Test’ link.

15. Please enter the ‘id’ append it to the query string of the URL. You will get the result.

11

12

Eg: http://localhost/datas/services/data/tourism/tourism-admin/cities/getCitiesByProvinceID?secret=123456&id=1

How to access to REST service if it is in the Sandbox Mode.

You can access to web services like following if the Data App is in the Sandbox mode.

<Your host>/services/data/<Data App name>/<Data Collection name>/<Table name>?secret=<Data App secret key>

Eg: http://localhost/datas/services/data/tourism/tourism-public/cities/getCitiesByProvinceID?secret=123456&id=1

16. Now Go back to data collections and Enable following operations for ‘Tourism User’ and ‘Tourism Admin’

Tourism User -> create and retrieve methods of comments

Tourism Admin -> create, retrieve, update and delete methods of comments, cities and provinces.

Congratulations! you are done with Data Services. We will look at how to Apply security in my next blog post using Hybrid Auth.


6 thoughts on “PHP REST Data Services – Getting Started

Leave a Reply

Your email address will not be published. Required fields are marked *