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

Question

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

My question is double.

1) What is the best way to output JSON from PHP so that I can send multiple database tables to the same JSON file? I have 4 data tables that I am trying to send (user, building, room, device). This is how I am currently outputting 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 am afraid that this method is not the right way to send multiple objects.

2) In an iOS application, how can I automatically accept JSON data and insert it into the corresponding local database tables in SQLite?

Thanks for any help.

Answer 1

Yeah Luke's recommendation is good, but you'll be fine with how you export your tables. You may just need to dig deeper into the structure to get what you want - i.e. Your result with the return of the "dictionary of arrays dictionaries", which will then contain data for each table.

How to download them first:

1) NSURLConnection and its delegation methods - you can send an asynchronous request to your web server to receive this file and receive a notification when the data has been downloaded, so the user interface is never blocked in your application.

Here is 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 .

Then you can use the 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 values ​​using valueForKey in dictionaries or objectAtIndex: in arrays, and then map them to the selected local storage, for which I recommend Coredata (or you can use sqlite if you are familiar with this too).

Hope this helps. Horn

Answer 2

On 1. Instead of JSOn, you can use the binary property lists that they originally implemented on the iPhone, and there is a library to turn PHP into a binary Plist https://github.com/rodneyrehm/CFPropertyList

There are many advantages to using binary property lists, they are probably 1/5 of the JSON size, you do not need an external library to parse them, so the whole code is much simpler, etc.

On 2. It is not possible to use the JSON / Plist structure and insert it into the SQL database, since JSON / Plist allows much more flexibility than SQL tables. Thus, you will need to first create the correct tables in your SQLite DB, and then use the regular INSERT to insert data one by one into the database in the same way as with PHP.

Answer 3

I can not talk with 2), but for 1), I would recommend combining your JSON into one array. One of the nice things about JSON (and arrays) is the ability to insert elements as deep as you like.

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

Answer 4

How about preparing a SQLite database on a web server and uploading it to an iOS application? So you do the hard work on the PHP side. If the data is relatively static, you can even set up a scheduled task to create a SQLite database at regular intervals.

We did this for one of our applications, and it worked very well.

Keep in mind that you should enable gzip compression on your web server to minimize data transfer. Remember that you need to do some additional material to use gzip compression with NSURLConnection:

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

Answer 5

you can use REST server and RESTKit.

Answer 6

If you want a more complete solution offered by the stand-alone parsing library, you can take a look at RestKit: http://restkit.org/

The structure wraps the operations of selecting, parsing and mapping useful JSON data into objects. It can handle deeply nested structures and can be directly redirected to Core Data for storage.

At a high level, here is what your send and receive operations will look like in RestKit:

 - (void)loadObjects { [[RKObjectManager sharedManager] loadObjectsAtResourcePath:[@"/path/to/stuff.json" delegate:self]; } - (void)objectLoader:(RKObjectLoader*)loader didLoadObjects:(NSArray*)objects { NSLog(@"These are my JSON decoded, mapped objects: %@", objects); // Mutate and PUT the changes back to the server MyObject* anObject = [objects objectAtIndex:0]; anObject.name = @"This is the new name!"; [[RKObjectManager sharedManager] putObject:anObject delegate:self]; } 

The structure takes care of parsing / encoding JSON in the background thread and allows you to declare how the attributes on the JSON map correspond to the properties of your object. A number of people in the community have been very successful with PHP backends + RestKit.