Sync SQLite/Android database table to remote MySQL database table (via PHP)

Question

I'm trying to make an SQLite database on the first run of a program, with a single table containing 7 rows, which is intended to match up with a table on a MySQL database on a remote server. So my thoughts are:

(1) On first application run only, create the database, the table, and the number of rows with the desired entries.

(2) On all runs (including the first one) poll a remote MySQL database (over PHP) that contains a table with a timestamp column, and if the timestamp conditions are met (i.e. a new entry has been placed in the database), all entries after that will be added into the SQLite database on the Android phone.

If someone can point me in the right direction or give me some basic code for any aspects of this, that'd be great - thanks.

Solution

Well, there aren't any magic bullets to sync android with a database in the cloud. Since you are only dealing with 7 rows, I would use an AsyncTask and http client to send a get request to your php page and return the 7 rows and timestamps as a json document. Then I would query your local sqlite db and get the rows and timestamps out of there. You can then iterate through the 7 rows and update the sqlite as required. That would be pretty easy.

If you are willing to use a different database, you might want to look into couchDB, they have an android client (in beta) that will sync with a couchDB in the cloud. You could create a couchDB at cloudant.com for free. Go google "couchbase android" if you wish to pursue this method.