How to make a public JSON API using PHP and MySQL

Hi all, I'm in a freelance project by myself and I wanted to create a mobile application (Android and iPhone) to complement my web application, you know... I want to be "in", so I asked some folks, their main job is to develop mobile apps and they told me that it is easy to integrate if I expose my data using an API and the easiest way to "read" it will be in JSON. So, as I mentioned we need data (don't you say!) so the first step will be create a database connection, I use MySQL by the way... Here's my code for that:

/**
 * @author Edgar Galvan
 * @copyright 2011
 */
$link = mysql_connect('localhost', 'root', ''); 
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('demo_db');

As you can see, this snippet is kind of old, you can use mysqli instead :). Then we need to extract our data in a method using PHP. You know how to do it... but here's an example.

if(function_exists($_GET["method"])){
 $_GET["method"]();
}

function getAllUsers(){
 $sqlUsers = 'SELECT * FROM users';
 $resultUsers = mysql_query($sqlUsers) or die(mysql_error());
 $arrayUsers = array();
 while($rowUsers = mysql_fetch_assoc($resultUsers)){
  $arrayUsers[] = $rowUsers;
 }
 $arrayUsers = json_encode($arrayUsers);
 echo $_GET['jsoncallback'].'('.$arrayUsers.')';
}

As you can see I use the global variable $_GET to fill some stuff in my script and I'll tell you why... because I need to make a request to my API in order to get the data, so I'm using GET to send the variables to my script. So, the first three lines are to validate if the function I'm requesting already exist, if yes, call that method. Then I just run a query to my database that says: Gimme all the users. Now that I have my result from my query I used the json_encode method to give my result a JSON format. Maybe you are wondering, why am I using the

$_GET['jsoncallback']

? That is because in my test page I make use of the jQuery getJSON method and that parameter allows me to see the results in my web browser console as JSONP that means JSON with Padding. Save all in a file called HelloServer.php. Let me show you my test page:

And there you go! You will be able to see the results in your console! You can also add parameters if the methods are more complicated just make the proper validations to avoid errors!

Thanks and happy coding!

If you have any comments, you have a better solution or you think I am wrong please let me know!

Edgar Marco Polo Galván de la Rosa :)