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.

Answer 1

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.

Answer 2

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