Pull JSON data from your website to your mobile application with Ajax and PHP

Hello!

Making mobile applications is much easier than it used to be. As with previous posts, we have been experimenting with Apache Cordova Framework for easily building mobile applications, leveraging web frameworks like AngularJS.

Many mobile applications communicate with a centralized “server” or website that retains all the data that may be useful for the mobile app. For example you might want to have a mobile application that retains a centralized “friends list” that the end-user can modify. You can store this friends list in a database, and access that data by making an Ajax request to pull the data in json format.

For the website that retains and manages this data, we have decided (for this example) to use the PHP Slim Framework. We mainly chose this because it makes rolling out a web based application API with a database backend very simple (in under 30-50 lines of code).

For the PHP website portion of this example, we will establish a GET path that makes a simple mysql database query and returns that data after you access the API with a web request. First we want to create a simple database table to hold the data :

CREATE TABLE IF NOT EXISTS `friends` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `status` int(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
);

The above mysql query simply creates a table called “friends” to store some information for friends such as name ,email and the date/time that the entry was created. Next we want to install the PHP Slim framework and create a simple Slim app that receives a request for /friends , does a mysql query and then returns the json response.

get('/friends', 'getfriends');

$friends->run();

// ****************
// CUSTOM FUNCTIONS
// ****************

function getfriends() {
        $sql = "SELECT * from friends order by created_at DESC;";
        try {
                $db = connect_db();
                $result = $db->query($sql);
                while ( $row = $result->fetch_array(MYSQLI_ASSOC) ) {
                        $data[] = $row;
                }
                $db = NULL;
                echo json_encode($data);
        } catch(PDOException $e) {
                echo '{"error":{"text":'. $e->getMessage() .'}}';
        }
}

Whats happening here? Well there is quite a bit of documentation on the PHP Slim framework, but for any moderately experienced developer the above code is straightforward. The framework listens for a request to the URI “/friends” and then executes the SQL query and spits out the result.

We now have a very simple website that has a very simple API to interact with! When you actually make the query in your browser, you will see a JSON response similar to something like the following :

[{"id":"1","name":"john smith","email":"some@email.com","status":"1","created_at":"2015-02-20 17:24:01"},
{"id":"1","name":"jane smith","email":"other@email.com","status":"1","created_at":"2015-02-21 17:24:01"}]

Now in the mobile application we want to make an AJAX query to execute the GET request and store the data in an array so we can use that data in our mobile application!

$.getJSON('http://your-site/friends', function(data) {
        $.each(data, function(index) {
            alert(data[index].name);
        });
    });

The getJSON query simply connects to your site, and parses the json data into an array. Then you can do whatever you want with that data in your mobile app – put it in a list, table or anything you want.

You can also accept POST requests to reverse the flow of data. If you want to implement the ability for your IOS or Android application to make changes to said friends lists, then you could accept a post request to the PHP Slim framework from your mobile app. Then the data could be received, processed and stored as an UPDATE or INSERT query into the mysql database.

Next post I’ll be going into security considerations with these types of activities, including implementing an API key authentication to secure your data and only allow authorized parties to make these types of queries. Implementing SSL encryption, authentication host headers and additional host headers to have API key validation is important and part of a set of best practices in order to secure your systems end-to-end.