How to Create a Simple API with PHP and MySQL

Introduction

Hello and welcome to my first article for the Brenelz Web Solutions 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.

A Little Info, First!

For the purposes of this article, we will be using the situation that I mentioned above: you own a recipe database and you want those to be accessible to a larger audience.

We will accomplish this by providing this data in our API:

• Recipe ID
• Recipe Name
• Recipe Poster
• Recipe Quick Info (# of minutes prep, # of minutes total time)
• Link to Recipe on our Websites

Let’s get started!

Into the Code…

I recommend you use the following URL structure (or something similar):

• http://api.example.com/recipes/

Once you have this structure, create a new file in the *recipes* folder called *get.php*. This is the main file that will be requested by developers after our data. Add the following to this file:

<?php

if(isset($_GET['format']) &amp;amp;amp;amp;&amp;amp;amp;amp; 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…

JSON

This one, luckily, is very simple. All we are doing is putting our data into an array, then using PHP’s very handy json_encode() function to encode the JSON. Here’s what I got with my dummy data outputting JSON:a into an array, then using PHP’s very handy _json_encode_ function to encode the JSON. Here’s what I got with my dummy data outputting JSON:

{"posts":[{"post":{"recipe_id":"20","recipe_name":"Grilled Flounder","recipe_poster":"Billy Bob","recipe_quick_info":"20 minutes prep, 40 minutes total time","recipe_link":"http:\/\/www.example.com\/"}},{"post":{"recipe_id":"19","recipe_name":"Grilled Flounder","recipe_poster":"Billy Bob","recipe_quick_info":"20 minutes prep, 40 minutes total time","recipe_link":"http:\/\/www.example.com\/"}},{"post":{"recipe_id":"18","recipe_name":"Grilled Flounder","recipe_poster":"Billy Bob","recipe_quick_info":"20 minutes prep, 40 minutes total time","recipe_link":"http:\/\/www.example.com\/"}},{"post":{"recipe_id":"17","recipe_name":"Grilled Flounder","recipe_poster":"Billy Bob","recipe_quick_info":"20 minutes prep, 40 minutes total time","recipe_link":"http:\/\/www.example.com\/"}},{"post":{"recipe_id":"16","recipe_name":"Grilled Flounder","recipe_poster":"Billy Bob","recipe_quick_info":"20 minutes prep, 40 minutes total time","recipe_link":"http:\/\/www.example.com\/"}}]}

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>

A few notes…

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.

Conclusion

Simple, wasn’t it? Using an API is a wonderful way to expand your user base as well as your content across the web. I hope this article has either helped you to learn something new or helped to put a concrete block under your knowledge. Thanks for reading!

Related posts:

1. Create URL Shortener For Twitter Using PHP
2. AJAX / JSON Guide
3. Really Simple Model/View Seperation With PHP
4. Create a Mashup from Scratch
5. Plain Text, XML, and JSON Transfer Formats

Written by Dixon Crews

Hey, I'm a 15 year old high school student who really loves web design and development from North Carolina, US. I'd love to go to school to learn even more about design, possibly computer science, and eventually having a job designing and developing websites! Follow me on Twitter.