A simple web service using PHP, JSON and MySQL

What is this post about?

In today’s web and mobile apps web services are used to access someone else’s resources; this could be to communicate two processes over the network, get some data from a database or fire up a process in a remote host. Although there are still web services being developed based on technologies such as XML and SOAP, the de-facto standards are JSON and REST. A web service based on REST is called a RESTful service. Almost every RESTful service uses HTTP as the default protocol.

So, put these facts  together and you will probably conclude that a simple good example of a web service could be a service to access someone else’s database, get some data from there in JSON format, or save some data using a POST message. Well, this post is about all of these.

Description of the test app

I have defined a user table in a remote dev_webservice_php_json database:

CREATE TABLE IF NOT EXISTS `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(100) NOT NULL DEFAULT '',
 `realname` varchar(100) NOT NULL DEFAULT '',
 `password` varchar(100) NOT NULL DEFAULT '',
 `email` varchar(100) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

This test app contains two methods to access the user database.

• The get method returns either the full list of users or a particular user, depending whether the id variable is set or not.
• The put method saves a user to the user table.

Both methods sent data via POST and the data are encoded using the JSON format.

The code itself is composed by the following files:

• index.php: it receives post messages and sends back the result in JSON format
• api.php: where the service methods’ are
• service_test.php: simple form to test the code
• configuration.json.dist: configuration data to access the remote database
• tables.sql: use this file to create a test database

Try it using the fast and easy way

I am not going through the code on this post, but you can try it easily. Clone the code from the Github repository on a folder under the Apache working path:

$ cd <path>
$ git clone https://github.com/memaker/webservice-php-json

Rename the file configuration.json.dist to a more appropriate configuration.json, edit it and complete the configuration data with your host name where the DB server is located, user, password and database.

{
 "host":"demohost",
 "user":"demouser",
 "password":"demopass",
 "database":"demodatabase"
}

If you do not have a test database you can create it using the script ‘tables.sql’; this script also includes test data.

And that’s all for the installation part.

Testing it using the included code

There are various ways to try the code. Open a browser and access the file ‘service_test.php’, e.g. http://localhost/webservice-php-json/service_test.php

If you left empty the ‘id’ field and click on the ‘get’ button you will receive a list of all the test data in JSON format:

{
"code": "0",
"data": [
{
"id": "1",
"username": "john",
"realname": "John Doe",
"password": "Saltro45",
"email": "john.doe@mail.com"
},
{
"id": "3",
"username": "Rob",
"realname": "Bar",
"password": "barbar",
"email": "bar@mail.com"
},
{
"id": "2",
"username": "saldo",
"realname": "Saldo Rush",
"password": "hro8052w",
"email": "saldo.rush@mail.com"
},
{
"id": "4",
"username": "simon",
"realname": "Simon Greath",
"password": "qbmu76rb",
"email": "simon.greath@mail.com"
}
]
}

If you complete the ‘id’ field with a valid id and click on the ‘get’ button you will receive only the selected user. The result JSON message is something like the following:

{
"code": "0",
"data": [
{
"id": "1",
"username": "john",
"realname": "John Doe",
"password": "Saltro45",
"email": "john.doe@mail.com"
}
]
}

When the “id” is wrong or does not exist, the web service returns an error message:

{
"code": "1",
"message": "Empty result or error on get method"
}

If you want to save data to the database you should complete the form and click on the ‘put’ button. If everything goes right you will receive the “id” of the new saved user in JSON format:

{
"code": "0",
"data": "7"
}

Testing it using the Chrome extension Advanced Rest Client

There is a more flexible method to test a web service: using the extension “Advanced Rest Client” for Chrome. Look for the extension on the Chrome webstore and complete some data to send a POST message to the web service as it is shown below.

Advanced Rest Client to test a web service

If you decide to try this example please add your comments, examples or improvements on this post. Thanks!