Android SQLite Database sync with MySQL online. Do I understand it right? - Developers

Hey, so I am making an app in which when you launch it for first time, it gives choice: register, login or skip. Skip is obviously use as offline user, so all data is stored on device in SQLite database (done) I finished my app and I am just left with adding Login/registration which is also done, it registers user in local sqlite database (different one from skipped users) So I plan to sync it with online and all user records to be saved online on db. So I want if user deletes app and logins from different android device, all his records and data will be downloaded and he can keep using it. All entries/records have userID field which saves username of the user (not id as int, but username as text) So from what I understand the way I would go with this is: Setup mysql databases with tables same as on phone. (done) When user clicks login -> validate user -> get all records and entries from mySQL database using PHP request on hosted .php file on my website using GET method for username parsing. Fetch and save all data that website returns from database (entries), save to local SQLite database = done? When user inserts new entry -> save on local SQLite database, send PHP request using GET method with variables/data to add to MYSQL = Done? But I am left with two questions: 1) Is there perhaps any pre-made/built in classes/libraries to help me with my task? (Instead of opening browser to user in app, I want this to happen in background) 2) What if user has no internet while inserting record? I will end up having local database up to date but online out out of date... I had small solution idea for this: add new column in entries table in SQLite Boolean called "SYNCED", the default value would be false. So when user adds record, if it sends php request and adds record into mysql database (and returns true (I am sure there is way to check if it was added or not) in worst case I can just use if internet connection exists) then it would change that column to SYNCED = true. Would that be a way or is there more efficient way? My second idea for this online sync was a bit unrealistic and quite terrible: 1) sent request, make PHP server fetch the required data from main database file, create new database file with only those records and that data, and make app download that database and use it as local DB. - Which I assume is very shit and un-efficient way? Any suggestions for this?

Anybody?

It's quite a simple concept. You will want to have an API in the middle which abstracts all the MySQL database stuff, this is a mixture of GET and POST serving either JSON or XML responses as these can be easily parsed. For offline mode, I don't see the problem with flagging unsynced data and just pushing it to the server when a connection is available. You might want to batch all the data together and send it in a single request. Android has built in stuff for sending data over HTTP and there's likely going to be a whole load of JSON parsing libraries for Java that can be used with Android.

[QUOTE=SteveUK;40366539]It's quite a simple concept. You will want to have an API in the middle which abstracts all the MySQL database stuff, this is a mixture of GET and POST serving either JSON or XML responses as these can be easily parsed. For offline mode, I don't see the problem with flagging unsynced data and just pushing it to the server when a connection is available. You might want to batch all the data together and send it in a single request. Android has built in stuff for sending data over HTTP and there's likely going to be a whole load of JSON parsing libraries for Java that can be used with Android.[/QUOTE] Any chance you can throw me some link which will get me into this "built in stuff fro sending data over HTTP" ? Thanks

[url]http://www.androidsnippets.com/executing-a-http-post-request-with-httpclient[/url]

All works great, except that 000webhost is being a dick. [code]04-21 18:51:38.746: D/RESPONSE(17780): SUCCESS 04-21 18:51:38.746: D/RESPONSE(17780):  04-21 18:51:38.746: D/RESPONSE(17780): <script type="text/javascript" src="http://stats.hosting24.com/count.php"></script> 04-21 18:51:38.746: D/RESPONSE(17780):  [/code] But I can either get other hosting or filter it. Thanks for the help.

[QUOTE=arleitiss;40368708]All works great, except that 000webhost is being a dick. [code]04-21 18:51:38.746: D/RESPONSE(17780): SUCCESS 04-21 18:51:38.746: D/RESPONSE(17780):  04-21 18:51:38.746: D/RESPONSE(17780): <script type="text/javascript" src="http://stats.hosting24.com/count.php"></script> 04-21 18:51:38.746: D/RESPONSE(17780):  [/code] But I can either get other hosting or filter it. Thanks for the help.[/QUOTE] If you're using PHP you can avoid that by putting [php]<?php exit; ?>[/php] at the end of your code (source: [url]http://stackoverflow.com/questions/2268868/webhoster-inserts-a-javascript-which-brokes-my-code-how-to-remove-it[/url])