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.

Jeena · Accepted Answer · 2011-02-21 20:29:55Z

4

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.

answered Feb 21, 2011 at 20:29

Jeena

2,2223 gold badges28 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rog · Accepted Answer · 2011-02-21 20:31:10Z

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

Luke Dennis · Accepted Answer · 2011-02-21 20:20:39Z

2

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,
));

answered Feb 21, 2011 at 20:20

Luke Dennis

14.6k17 gold badges60 silver badges70 bronze badges

Comments

Pieter Claerhout · Accepted Answer · 2011-02-21 20:54:50Z

2

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/

answered Feb 21, 2011 at 20:54

Pieter Claerhout

3585 silver badges14 bronze badges

1 Comment

Julian Over a year ago

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

Kyle Emmanuel · Accepted Answer · 2012-11-27 08:47:32Z

1

you can use REST server and RESTKit.

answered Nov 27, 2012 at 8:47

Kyle Emmanuel

2,2211 gold badge17 silver badges23 bronze badges

Comments

Blake Watters · Accepted Answer · 2011-02-24 13:00:17Z

If you would like a more full-featured solution that what is offered by a standalone parsing library, you may want to take a look at RestKit: http://restkit.org/

The framework wraps the operations of fetching, parsing, and mapping JSON payloads into objects. It can handle deeply nested structures and can map directly back to Core Data for persistence.

At a high level, here's what your fetch & post operations would feel 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 framework takes care of the JSON parsing/encoding on a background thread and let's you declare how attributes in the JSON map to properties on your object. A number of people in the community are working with PHP backends + RestKit with great success.

Collectives™ on Stack Overflow

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

6 Answers 6

Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related