From PHP/MySQL/JSON to iOS/Objective-C/SQLite

Question

I'm trying to create an iOS application which upon loading, will initially connect via HTTP back to a PHP web service which will output data as JSON from a MySQL database. I would then like it to import this data into a local SQLite database within the iOS app. I've already downloaded the JSON-Framework for Objective-C.

My question is two fold.

1) What is the best way to output the JSON from PHP so that I can send multiple database tables in the same JSON file? I have 4 tables of data that I'm trying to send (user, building, room, device). Here is how I am currently outputting the JSON data:

    // Users
    $query = "SELECT * from user";
    $result = mysql_query($query,$conn) or die('Errant query:  '.$query);

    $users = array();
    if(mysql_num_rows($result)) {
      while($user = mysql_fetch_assoc($result)) {
      $users[] = array('user'=>$user);
      }
    }

    // Buildings
    $query = "SELECT * from building";
    $result = mysql_query($query,$conn) or die('Errant query:  '.$query);

    $buildings = array();
    if(mysql_num_rows($result)) {
      while($building = mysql_fetch_assoc($result)) {
       $buildings[] = array('building'=>$building);
     }
    }

    // Rooms
    $query = "SELECT * from room";
    $result = mysql_query($query,$conn) or die('Errant query:  '.$query);

    $rooms = array();
    if(mysql_num_rows($result)) {
     while($room = mysql_fetch_assoc($result)) {
      $rooms[] = array('room'=>$room);
     }
    }

    // Devices
    $query = "SELECT * from device";
    $result = mysql_query($query,$conn) or die('Errant query:  '.$query);

    $devices = array();
    if(mysql_num_rows($result)) {
     while($device = mysql_fetch_assoc($result)) {
      $devices[] = array('device'=>$device);
     }
    }

    header('Content-type: application/json');
    echo json_encode(array('users'=>$users));
    echo json_encode(array('buildings'=>$buildings));
    echo json_encode(array('rooms'=>$rooms));
    echo json_encode(array('devices'=>$devices)); 

I fear that this method isn't the right way to send multiple objects.

2) In the iOS app, how can I automatically take this JSON data and insert it into the corresponding local database tables in SQLite?

Thanks for any help.

Solution 1

Yeah Luke's recommendation is good but you will be fine with the way you are exporting your tables. You may just have to dig "deeper" into the structure to get what you want - i.e. your output with return a "dictionary of dictionaries of arrays" which will then contain the data for each table.

As for downloading them first:

1) NSURLConnection and its delegate methods - you can send asynchronous request to your webserver to get this file and get notified when the data has been downloaded so the user interface is never blocked in your app.

Here's the documentation with some good examples from Apple: http://developer.apple.com/library/mac/#documentation/Cocoa/Reference/Foundation/Classes/NSURLConnection_Class/Reference/Reference.html

At the end of the download, you will have an NSData object which can then be converted back to a string using NSString *jsonContents = [[NSString alloc] initWithData:jsonData encoding:NSUTF8StringEncoding.

You can then use a JSON parser library - I recommend SBJSON https://github.com/stig/json-framework - which will parse the data and return it as a dictionary or array depending on your structure.

From there you can access your tables and value with valueForKey in dictionaries or objectAtIndex: in arrays and then map it into your chosen local storage, for which I recommend Coredata (or you could use sqlite if you are familiar with it too).

Hope it helps. Rog

Solution 2

On 1. Instead of JSOn you could use binary Property lists they are natively implemented on the iPhone and there is a library to turn PHP into binary Plist https://github.com/rodneyrehm/CFPropertyList

There are many benefits to using binary property lists, they are probably 1/5 of the size of JSON, you don't need a external library to parse them, therefore all code is much simpler, etc.

On 2. There is no easy way to take the JSON/Plist structure and insert it to a SQL database, because JSON/Plist allow much more flexibility then SQL tables. So you would have to first create the right tables in your SQLite DB and then use normal INSERT to insert the data one by one into the database exactly like you would do with PHP.

Solution 3

I can't speak to 2), but for 1), I would recommend combining your JSON into a single array. One of the nice things about JSON (and arrays) is the ability to nest elements as deeply as you like.

echo json_encode(array(
    'users'=>$users,
    'buildings'=>$buildings,
    'rooms'=>$rooms,
    'devices'=>$devices,
)); 

Solution 4

What about preparing the SQLite database on the webserver and downloading that to the iOS application? This way, you do the heavy lifting on the PHP side. If the data is relatively static, you can even setup a scheduled task to generate the SQLite database on a regular interval.

We've done this for one of our apps and it worked very well.

One thing to keep in mind then is that you should enable gzip compression on the webserver to minimize the data transfer. Remember that you have to do some extra stuff to use gzip compression with NSURLConnection:

http://www.bigevilempire.com/codelog/entry/nsurlconnection-with-gzip/

Solution 5

you can use REST server and RESTKit.

View more solutions

Comments

[Julian]

ASIHTTPRequest already handles gzip compression if you set the setAllowCompressedResponse to YES so if you use this library then you don't really have to do any NSURLConnection fiddling. By the way, I'm using Pieter's suggestion in one app and it works like a dream. allseeing-i.com/ASIHTTPRequest/How-to-use#gzip

[sdjuan]

I like this approach. Thanks!