1

I was brainstorming how I should handle this. This is how the application is going to work:

  1. User enters multiple pieces of data into app
  2. App stores data into SQLite database
  3. User hits SYNC button and app will pass all new/updated/deleted info to site PHP and update MYSQL.

I was thinking I can do a loop where it sends (and receive) one row at a time to mysql or I can use a string builder to build a XML, and pass (and receive) the XML string to PHP to process. The xml will have tag data specifying if the element is to be added, deleted or updated.

I figure the XML is a better option, but I'm coming here for opinions how I should push multiple rows to be added/deleted or updated to my MYSQL because I feel there's probably a more efficient/easier way of doing this.

Thanks!

--UPDATE--

Here's some helpful links I found of JSONArrays for those seeking similar information as I am about Android SQLite to PHP MYSQL.

  1. Nice tutorial about JSONArrays in PHP: http://webhole.net/2009/08/31/how-to-read-json-data-with-php/
  2. Another tutorial about JSONArrays in Java: http://www.androidcompetencycenter.com/2009/10/json-parsing-in-android/
Anthony Honciano
  • 1,683
  • 5
  • 24
  • 30
  • Related: http://stackoverflow.com/questions/163079/sqlite-export – OMG Ponies May 29 '11 at 18:25
  • 1
    I'm sorry, I don't understand how this is related to sqlite-export? Because I'm trying to create a syncrhonizing structure, not just dump the SQLite database into MYSQL. If I did SQLite dumps, wouldn't that overrite MYSQL tables (according to what I'm reading)? Unless the export can be specific? – Anthony Honciano May 29 '11 at 18:31
  • What you want and what functionality actually exists are often two different things. Doesn't mean someone has created it for you, either... – OMG Ponies May 29 '11 at 18:34
  • I didn't expect anybody has created it. I probably should rephrase what I'm looking for. I'm looking to send and receive database add/delete/update information between SQLite and MYSQL. I was thinking of building an XML file in a string and pass it to Android and web server. – Anthony Honciano May 29 '11 at 18:36

1 Answers1

3

Ah, opinions. Painful things though: everyone has one and everyone thinks their opinion is better than the next persons'.

I've implemented a system that is pretty much identical: I used JSON for it though. There is no intrinsic issue with using XML: whatever translation layer you are comfortable with is probably fine. JSON was (for me) a bit more compact than XML, required less code on both sides (json_decode is your friend) and seemed to me to be an easier row to hoe than using XML. However PHP's simplexml would probably work fine as well.

If you're doing this from scratch you might want to look at one of the systems with automatic data syncing like Mobile Couchbase (see http://www.couchbase.com/products-and-services/mobile-couchbase): would require a fair bit more tooling and a bigger server/client resource footprint but might get you there faster.

Femi
  • 64,273
  • 8
  • 118
  • 148
  • I'm looking up the JSON for Java, it looks easy enough to use. However I can't seem to figure out how to add in like 3 rows of data from SQLite? Would I just add in the array like a key value or row value before adding the column data? – Anthony Honciano May 29 '11 at 22:01
  • 2
    In my case, I send a JSON array to the server. Each row in the array is a JSON object: the object has an *action* key (the value is either DELETE or UPDATE) and a *data* key (the value is a JSON object with the SQLite data: each column has a key/value with column name/column value). I do a create/update on the server based on a server id column in the data field, return the server id (if its a CREATE) and then write that back into the SQLite table (for future updates). – Femi May 29 '11 at 22:07
  • I think I get this, I looked up some examples of JSON Arrays... It's like having a highly organized array in an array...yes? – Anthony Honciano May 30 '11 at 01:17
  • Yep, its a set of nested JSON objects inside an array, with one entry in the array for each row that needs to be synchronized. – Femi May 30 '11 at 01:49
  • For any newbs like me, I found some very helpful links that allowed me to better understand how to get a highly structured JSONArray in PHP and Java via Android. Please see above updates. – Anthony Honciano May 30 '11 at 02:59
  • BTW @Femi, you're the bomb! This is definitely simpler then structuring a XML document and much easier to parse. – Anthony Honciano May 30 '11 at 03:00
  • Glad it helps. The XML parsing can be made to work, but you're right: this is simpler (which was one reason why I did it). Initially wanted to try using SyncML, but just decided to pass: too much hassle there. This is definitely less full featured, but far cleaner to get started. – Femi May 30 '11 at 03:03