So one of the buzzword’s used in 2012 was ‘Big Data’, representing the interconnection of lots of databases from a variety of sources all communicating together.

Why might you want to engage with big data? Well say your website or app wants to make use of a post code search tool to confirm someones address, or allow your visitors to look up a weather report for a holiday destination you are offering without the user leaving your website or app.

This is achieved through the use of some technology known as ‘web services’ or an ‘Application Programming Interface’ (API).

One of my projects for 2013 involves making use of API’s to load data from a web server so I thought I would share how API’s work through this demo:

Lets say you have a database of recipes containing:

  • Recipe ID
  • Title
  • Thumbnail of the Dish
  • Ingredients & Method
  • Author

Next you would need to define what formats you want your data to be presented back, in this case I am going to use JSON and XML formatting.

On your web server create a new folder or appropriate place to store your API data file. In my case I have chosen: and add a new file titled ‘API.php’

Inside API.php we can start to add some code:


//Checking and Getting the two variables format and number of records
if(isset($_GET[‘format’]) and intval($_GET[‘num’])) {

//Set our variables
$format = strtolower($_GET[‘format’]);
$num = intval($_GET[‘num’]);

In this code we open the PHP tags and check that we are getting two variables from the HTTP request – the format (either XML or JSON) and num (number of rows of data).

After this we need to connect to the database and start to query for data:

//Connect to the Database
$con = mysql_connect(“localhost”,”username”,”password”) or die (‘MySQL Connection Error.’);
mysql_select_db(“database_name”, $con) or die(‘MySQL Table Error.’);

//Run the API query
$result = mysql_query(‘SELECT * FROM table ORDER BY `recipe_id` ASC LIMIT ‘ . $num, $con) or die(‘MySQL Query Error.’);

This query will look-up the table ‘table’ from the database ‘database_name’ and return in ASCENDING order rows of data.

Now we have the data we can build an array and begin to present the data we would like.

//Preapre our output
if($format == ‘json’) {

$recipes = array();
while($recipe = mysql_fetch_array($result, MYSQL_ASSOC)) {
$recipes[] = array(‘post’=>$recipe);

$output = json_encode(array(‘posts’ => $recipes));

Fortunately for us PHP includes an encoder for JSON so specifying that we want the data in JSON format we can load the data into an array and then write the data directly into the correct format.

If on the other hand we would like our data in XML format we can use a loop inside php to load the array and then fill in the XML tags to present back the XML format required:

elseif($format == ‘xml’) {

header(‘Content-type: text/xml’);
$output .= “<?xml version=\”1.0\”?>\n”;
$output .= “<recipes>\n”;

for ($i = 0 ; $i < mysql_num_rows($result) ; $i++)
$row = mysql_fetch_assoc($result);
$output .= “<recipe> \n”;
$output .= “<recipe_id>” . $row[‘recipe_id’] . “</recipe_id> \n”;
$output .= “<recipe_name>” . $row[‘title’] . “</recipe_name> \n”;
$output .= “<recipe_img_small>” . $row[‘thumbnail_photo_url’] . “</recipe_img_small> \n”;
$output .= “<recipe_link>” . $row[‘recipe_id’] . “</recipe_link> \n”;
$output .= “</recipe> \n”;

$output .= “</recipes>”;

The else if statement is connected to the IF statement above and relates to the users choice of data they would like (JSON Vs XML). As the If statement is run it loops through the statement filling in the blanks with data from the Array creating a complete XML file.

This is what the files look like in their respective outputs.


{“posts”:[{“post”:{“recipe_id”:”1″,”title”:”Quick and Easy Rice”,”large_photo_url”:”tbd”,”thumbnail_photo_url”:”tbd”,”ingredients”:”Rice\r\nBoiling Water\r\nSesame Oil – Optional\r\n”,”method”:”Cook some rice”,”Author”:”Mathew”,”thumbs”:”1″}}]}


<recipe_name>Quick and Easy Rice</recipe_name>

Building an API like this gives developers the most amount of flexibility when it comes to interfacing with your information and data.

Its worth noting that this API has got NO limitations attached to it and that should someone get a hold of your the API they would be able to spam your web server countless times bringing it down. I HIGHLY recommend the use of an API Key system such as or