A COMPLETE AND EASY TUTORIAL FOR BUILDING A JSON BACKEND

 

JSON is a data-interchange format commonly used for machine-to-machine communication, including web applications’ backends and REST services.

JSON is a frequently used format in server-to-server and client-to-server data exchange connections like AJAX, along with simpler formats like CSV (comma separated values) or more complex ones like XML.

 

JSON is quite simple and also easily readable by humans, while still being powerful enough to be used in relatively complex applications.

PHP natively supports encoding and decoding of JSON strings, and in this tutorial we will see how this is done with a concrete example.

 

 

 

json

 

 

 

In this tutorial we will implement a JSON backend for retrieving a list of users from a database. Each user will have some information associated with it (name, address…) and a list of “access zones”. This example mimics a scenario typically found in many work environments.

 

 

 

SQL STRUCTURE

 

 

Let’s begin with the SQL structure. We are going to create two tables: the first, named users, will contain all the users’ informations; the second table, named access, will contain the association between users and access zones. This is just an example structure, so feel free to edit it and make some experiments if you like (if you need help with this part, just leave me a comment at the end of the post).

 

Here is the SQL code to create the tables:

 

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `users` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `user_first_name` varchar(80) NOT NULL,
  `user_surname` varchar(80) NOT NULL,
  `user_address` varchar(80) NOT NULL,
  `user_birthday` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`);
ALTER TABLE `users`
  MODIFY `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

 

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `access` (
  `access_id` int(11) NOT NULL,
  `access_user_id` int(11) NOT NULL,
  `access_zone` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `access`
  ADD PRIMARY KEY (`access_id`);
ALTER TABLE `access`
  MODIFY `access_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

 

 

Now you need to populate these tables with some data. You are welcome to do that by yourself, but if you prefer you can just use the following code to insert the data I used in this tutorial:

 

INSERT INTO `users` (`user_id`, `user_first_name`, `user_surname`, `user_address`, `user_birthday`) VALUES
(1, 'Johann', 'Strauss', 'Vienna', '1764-10-10'),
(2, 'Antonio', 'Vivaldi', 'Venice', '1678-03-04'),
(3, 'Joseph', 'Haydn', 'Rohrau', '1732-03-31'),
(4, 'Anton', 'Rubinstein', 'Odessa', '1829-11-20');

INSERT INTO `access` (`access_id`, `access_user_id`, `access_zone`) VALUES
(1, 1, 'Zone A'),
(2, 1, 'Zone C'),
(3, 2, 'Zone C'),
(4, 2, 'Zone D'),
(5, 3, 'Zone B'),
(6, 3, 'Zone E'),
(7, 4, 'Zone A');

 

 

 

PHP CODE

 

 

Now let’s move on to the PHP code.

 

This script will work as a data backend, which means it will output some data in response to a request. It could be, for example, an AJAX or a REST service backend.

The client making the request usually asks for a data subset by providing some kind of filter, like a time interval or a search string. This is done by passing one or more HTTP request parameters to the backend.

For the sake of making this tutorial a bit more realistic, the script will support one request parameter named search, and the returned users list will be limited to those whose names contain the searched string.

 

The first thing the script needs to do is to connect to the database and retrieve the users’ data from the tables (applying the search filter). For database connection it will use the PDO extension.

After that, the script will store all the data inside a PHP structure (in this case, an array), and finally it will convert this data into a proper JSON string and output it.

This may seem a lot of work but it’s actually quite easy. Here is the code:

 

<?php

/* Read the "search" request parameter */
$search = $_REQUEST['search'];

/* Connect to the database (see function definition for details) */
$db = db_connect();

/* Retrieve the users' information */
$users = retrieve_users($search);

/* Create the JSON string */
$json = json_encode($users, JSON_PRETTY_PRINT);

/* Output in a human readable format */
echo '<pre>';
print_r($json);
echo '</pre>';


/* Function to connect to our local MySQL server, using the PDO extension */
function db_connect()
{
	try
	{
		$db = new PDO('mysql:host=localhost;dbname=test', '', '');
		$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	}
	catch (PDOException $e)
	{
		/* Something went wrong */
		echo $e->getMessage();
		die();
	}
	
	return $db;
}

/* Function to retrieve the users */
function retrieve_users($search)
{
	global $db;
	
	$users = array();
	$query = 'SELECT * FROM test.users WHERE (user_first_name LIKE ?) OR (user_surname LIKE ?) ORDER BY user_surname ASC, user_first_name ASC';
	$st = $db->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	$st->execute(array('%' . $search . '%', '%' . $search . '%'));
	
	while ($res = $st->fetch(PDO::FETCH_ASSOC))
	{
		/* Save the user's informations in the array */
		$users_index = count($users);
		
		$users[$users_index] = array(
					
					'first name' => $res['user_first_name'],
					'surname' => $res['user_surname'],
					'address' => $res['user_address'],
					'birthday' => $res['user_birthday'],
					'access zones' => array());
		
		/* Now read the access zones of this user */
		$zones_query = 'SELECT * FROM test.access WHERE (access_user_id = ?) ORDER BY access_zone ASC';
		$zones_st = $db->prepare($zones_query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
		$zones_st->execute(array($res['user_id']));
		
		while ($zones_res = $zones_st->fetch(PDO::FETCH_ASSOC))
		{
			$users[$users_index]['access zones'][] = $zones_res['access_zone'];
		}
	}
	
	return $users;
}

 

 

Note: be sure to read my SQL injection prevention guide to know how to avoid injection attacks!

 

When we execute this script with “an” as search parameter, all users whose first name or surname contains “an” are included in the result.

If you are using a development environment on your computer, you can try this yourself by saving the previous example in a PHP file (say, “json.php”), and accessing it from a web browser like this:

http://localhost/json.php?search=an

 

This is the output from the script:

 

[
    {
        "first name": "Anton",
        "surname": "Rubinstein",
        "address": "Odessa",
        "birthday": "1829-11-20",
        "access zones": [
            "Zone A"
        ]
    },
    {
        "first name": "Johann",
        "surname": "Strauss",
        "address": "Vienna",
        "birthday": "1764-10-10",
        "access zones": [
            "Zone A",
            "Zone C"
        ]
    },
    {
        "first name": "Antonio",
        "surname": "Vivaldi",
        "address": "Venice",
        "birthday": "1678-03-04",
        "access zones": [
            "Zone C",
            "Zone D"
        ]
    }
]

 

 

 

JSON_ENCODE()

 

 

Now let’s have a look at the PHP code.

As you can see, most of the code is SQL related and I won’t go into the details, but If you have any doubt about the database part feel free to ask your questions in the comments at the end of the post.

 

More interesting than the database, you can see that all the JSON encoding is actually done by one function: json_encode(). 

This simple function automatically converts a variable into a proper JSON string. This function accepts many variable types, but I suggest you to always use arrays in order to keep the code readable.

(It’s also worth mentioning that a JSON structure generated from a string can cause compatibility issues).

Creating a JSON structure from an array is really as simple as calling json_encode().

 

This function also takes two more arguments: the first is an options bitmask, that is a list of any of the possible options available, and the second is the max structure depth (that defaults to 512).

 

You probably won’t need to worry about the last one.

Some of the options you can set in the second argument, however, are worth knowing. You may have noticed that I actually used one of them in the previous example: JSON_PRETTY_PRINT. If this option is set, then the output from json_encode() will be more readable thanks to some added whitespaces.

This is useful for debugging purposes (or when making examples) as it makes the code more easily readable by humans.

 

 There are also some other useful options, for example:

  • JSON_NUMERIC_CHECK: automatically converts all string numbers into floats;
  • JSON_PRESERVE_ZERO_FRACTION: always keeps the decimal part of float numbers, even if it’s zero.
  • JSON_FORCE_OBJECT: always create an object JSON structure instead of an array (we will se what this means in a minute…);

 

There are many other options available, but you don’t need to worry about them for now. You can just check them when you will need to. Now let’s see what the three options I listed before do.

 

JSON_NUMERIC_CHECK can be handy for automatically converting strings into numbers, but should be used with caution. Sometimes, automatically casting strings to numbers can generate unexpected results. What happens if the number doesn’t fit inside an int? Or if it’s actually a string even if it’s digit only? And what about float precision?

If you choose to use this option, make sure that no conversion mistakes can potentially occur.

 

 JSON_PRESERVE_ZERO_FRACTION makes all float numbers have at at least one decimal digit, even if it’s zero. This may be needed if the client expects numbers to always have a decimal part.

 

For the last option we need to talk a bit about JSON structures.

JSON structures can either be arrays or objects. Arrays are ordered lists of items, while objects are unordered lists of key => item pairs. Arrays are encapsed by square brackets and objects by curly brackets.

Let’s get back to our previous example. In the JSON string output, you can see that the main structure is an array, since is encaped by square brackets (“[” at the begin and “]” at the end). Such a structure is therefore an ordered list: there are no key => value pairs, just a list of items. “Ordered” list means that the order does matter.

 

Items inside JSON structures can be strings, numbers, boolean values and also arrays or objects, which can contain other items themselves and even create nested structures.

Look once again at the example: each of the main array’s items is a new JSON structure itself, this time an object. For example, this is the second item of the main array:

 

{
   "first name": "Johann",
   "surname": "Strauss",
   "address": "Vienna",
   "birthday": "1764-10-10",
   "access zones": [
      "Zone A",
      "Zone C"
   ]
}

 

It’s encased in curly brackets (“{” and “}”) which define an object. Here, each item is a key => value pair as can be clearly seen, and this time the list order does not matter. You can also notice that the last item of this list is again another JSON array, which is the list of the user’s access zones. This should give you an idea of how a nested JSON structure is made.

 

When creating a JSON structure from an array, json_encode() looks at the array type to determine if it should be converted into a JSON array or into an object. If the source array is a standard, numeric array (i.e., if its keys are integers, sequential and starting from zero), then it will be converted into a JSON array. Otherwise, it will be converted into a JSON object.

The JSON_FORCE_OBJECT option can be used to override this default behaviour, and it will force json_encode() to always create JSON objects regardless of the source array type.

Here is what happens if we add this option to the previous example (notice how you can add multiple options at line 2):

 

/* Create the JSON string */
$json = json_encode($users, JSON_PRETTY_PRINT | JSON_FORCE_OBJECT);

/* Output in a human readable format */
echo '<pre>';
print_r($json);
echo '</pre>';

 

 

And this is the new JSON output:

 

{
    "0": {
        "first name": "Anton",
        "surname": "Rubinstein",
        "address": "Odessa",
        "birthday": "1829-11-20",
        "access zones": {
            "0": "Zone A"
        }
    },
    "1": {
        "first name": "Johann",
        "surname": "Strauss",
        "address": "Vienna",
        "birthday": "1764-10-10",
        "access zones": {
            "0": "Zone A",
            "1": "Zone C"
        }
    },
    "2": {
        "first name": "Antonio",
        "surname": "Vivaldi",
        "address": "Venice",
        "birthday": "1678-03-04",
        "access zones": {
            "0": "Zone C",
            "1": "Zone D"
        }
    }
}

 

 

Every JSON array has been changed into an object. Square brackets have turned into curly brackets, and every item is now a key => value pair, where the key is the index of the PHP array and the value is the item itself.

When should you use this option? It all depends on what format the client is expecting, so you will need to choose whether to use it or not depending on the situation.

 

 

 

ERROR CHECKING AND CONTENT TYPE

 

 

The backend is almost finished, but there are still a few details that need to be addressed.

First, we need to check for errors. The json_encode() function returns FALSE if something goes wrong, so it’s a good idea to check that. In that case, you can also use the json_last_error() and the json_last_error_msg() functions to retrieve the error code and message respectively.

It’s up to you to decide whether to show the error or do some other kind of diagnostic operation (or just kill the script).

 

Another thing the backend must do is to set the proper content type, that for JSON data is application/json. This can easily be done with the header() function:

 

<?php

header('Content-Type: application/json');

 

 

 

Now the tutorial is complete! Click down below to expand the container and show the final example, or click the Download example file button to download the PHP file.

 

 

Click to show the final example
<?php


/* Read the "search" request parameter */
$search = $_REQUEST['search'];

/* Connect to the database (see function definition for details) */
$db = db_connect();

/* Retrieve the users' information */
$users = retrieve_users($search);

/* Create the JSON string */
$json = json_encode($users, JSON_PRETTY_PRINT);

/* Check for errors */
if ($json === FALSE)
{
	echo 'JSON error! Error code: ' . json_last_error() . '; error message: "' . json_last_error_msg() . '".';
	die();
}

/* Output the JSON string */
header('Content-Type: application/json');
echo $json;


/* Function to connect to our local MySQL server, using the PDO extension */
function db_connect()
{
	try
	{
		$db = new PDO('mysql:host=localhost;dbname=test', '', '');
		$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	}
	catch (PDOException $e)
	{
		/* Something went wrong */
		echo $e->getMessage();
		die();
	}
	
	return $db;
}

/* Function to retrieve the users */
function retrieve_users($search)
{
	global $db;
	
	$users = array();
	$query = 'SELECT * FROM test.users WHERE (user_first_name LIKE ?) OR (user_surname LIKE ?) ORDER BY user_surname ASC, user_first_name ASC';
	$st = $db->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	$st->execute(array('%' . $search . '%', '%' . $search . '%'));
	
	while ($res = $st->fetch(PDO::FETCH_ASSOC))
	{
		/* Save the user's informations in the array */
		$users_index = count($users);
		
		$users[$users_index] = array(
					
					'first name' => $res['user_first_name'],
					'surname' => $res['user_surname'],
					'address' => $res['user_address'],
					'birthday' => $res['user_birthday'],
					'access zones' => array());
		
		/* Now read the access zones of this user */
		$zones_query = 'SELECT * FROM test.access WHERE (access_user_id = ?) ORDER BY access_zone ASC';
		$zones_st = $db->prepare($zones_query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
		$zones_st->execute(array($res['user_id']));
		
		while ($zones_res = $zones_st->fetch(PDO::FETCH_ASSOC))
		{
			$users[$users_index]['access zones'][] = $zones_res['access_zone'];
		}
	}
	
	return $users;
}

 

 

If you have any questions just leave a comment below.

As always, I really thank you for reading this post, and if you liked it please take a second to share it!