How to create REST API with PHP and MySQL

Hi all and welcome to my first article for the phptechnicagroups blog! This article will cover how you can create a very simple API for any one of your projects. We are going to be using PHP and MySQL for the back end, and we will output our API data in two formats: XML or JSON.

API stand for Application Programming Interface. Put simply, it is a way for everyone (if you so choose) to access your website’s data. For example, let’s say that you have a website where users submit recipes they like. There is a lot of meta content that is associated to the initial submission, and you want all of this to be more accessible. This is a great example of a place where an API can do wonders. Take Twitter as an example, their success can be attributed largely to the success of their open API, providing developers with the “fire hose” to make their own apps and drive Twitter further.

<?php

if(isset($_GET['format']) && intval($_GET['num'])) {

//Set our variables

$format = strtolower($_GET['format']);

$num = intval($_GET['num']);

//Connect to the Database

$con = mysql_connect('localhost', 'root', '') or die ('MySQL Error.');

mysql_select_db('api', $con) or die('MySQL Error.');

//Run our query

$result = mysql_query('SELECT * FROM recipes ORDER BY `recipe_id` DESC LIMIT ' . $num, $con) or die('MySQL Error.');

//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));

} 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['recipe_name'] . "</recipe_name> \n";

$output .= "<recipe_poster>" . $row['recipe_poster'] . "</recipe_poster> \n";

$output .= "<recipe_quick_info>" . $row['recipe_quick_info'] . "</recipe_quick_info> \n";

$output .= "<recipe_link>" . $row['recipe_link'] . "</recipe_link> \n";

$output .= "</recipe> \n";

}

$output .= "</recipes>";

} else {

die('Improper response format.');

}

//Output the output.

echo $output;

}

?>

This is the full version of the script, just to get it out there, now let’s explain it a little bit:

1. First, we need to get some data from the user: What format do you want? How many responses do you want?
2. These are defined in the GET variables format and num. To start off our script, we check if these values are even supplied; if they’re not, the script won’t do anything. If they are both supplied, we move on by connecting to our database, running a relatively simple query to get all the rows, order them in descending order by their id, and limit the response to how many were requested.
3. The majority of our script is taken up by the output processing. After we have our data selected into a variable, we need to get it out to the user. First, we check if the user wanted JSON or XML, and depending on that, we serve up the proper output. Let’s go a little more in depth on each of the output styles…

XML

The XML is a little more tricky, it requires the use of the very powerful (and scary) for()function. Here, we are basically saying this: As long as the variable $i is less than the number of rows that we returned, go ahead and add this to our $output variable. Once you’ve done that, add one to the $i variable. Here’s the output I got from this:

<?xml version="1.0"?>

<recipes>

<recipe>

<recipe_id>20</recipe_id>

<recipe_name>Grilled Flounder</recipe_name>

<recipe_poster>Billy Bob</recipe_poster>

<recipe_quick_info>20 minutes prep, 40 minutes total time</recipe_quick_info>

<recipe_link>http://www.example.com/</recipe_link>

</recipe>

<recipe>

<recipe_id>19</recipe_id>

<recipe_name>Grilled Flounder</recipe_name>

<recipe_poster>Billy Bob</recipe_poster>

<recipe_quick_info>20 minutes prep, 40 minutes total time</recipe_quick_info>

<recipe_link>http://www.example.com/</recipe_link>

</recipe>

<recipe>

<recipe_id>18</recipe_id>

<recipe_name>Grilled Flounder</recipe_name>

<recipe_poster>Billy Bob</recipe_poster>

<recipe_quick_info>20 minutes prep, 40 minutes total time</recipe_quick_info>

<recipe_link>http://www.example.com/</recipe_link>

</recipe>

<recipe>

<recipe_id>17</recipe_id>

<recipe_name>Grilled Flounder</recipe_name>

<recipe_poster>Billy Bob</recipe_poster>

<recipe_quick_info>20 minutes prep, 40 minutes total time</recipe_quick_info>

<recipe_link>http://www.example.com/</recipe_link>

</recipe>

<recipe>

<recipe_id>16</recipe_id>

<recipe_name>Grilled Flounder</recipe_name>

<recipe_poster>Billy Bob</recipe_poster>

<recipe_quick_info>20 minutes prep, 40 minutes total time</recipe_quick_info>

<recipe_link>http://www.example.com/</recipe_link>

</recipe>

</recipes>

parsing your XML data using below code

<?php
$response = new SimpleXMLElement('http://127.0.0.1/recipes/format=xml',null, true);
echo "<pre>";print_r($response);
?>  

 end 

There are a few things I have left out of this article for the sake of simplicity. Once you have the basics of this figured out, you can add these yourself. A main thing I left out is the necessity of an API Key. If you want to manage who gets your data how, you must assign them an alphanumeric key of at least 32 characters, this will let you know who is accessing your data and you are also able to cut out just anyone from getting at your site. Another key part left out is the fact that you need to manage this API! Someone could just come in here, repeat this script over and over again and bring your website down. There are many free tools out there to limit your API usage, and one I highly recommend is 3scale. Twitter limits their API to 1000 requests per day, so you can get an idea of what you want your API limits to be from that.