How to sync SQLite on Android to MySQL DB?

In this post, I am going to explain how to sync data present in Android SQLite database to remote MySQL DB. In most of the Android applications, the data present in the local SQLite database will be synced with remote DB in order to perform statistical operations at the server end, maintain backup of the offline data etc.

In such cases, offline data will be synced with remote DB when User explicitly hits ‘Sync’ button or asynchronously by the application itself (in particular interval) when Internet connectivity is available.

Prerequisite:

You must have knowledge of SQLite before you start creating the application. Have a glance at the below articles which will make you understand much about SQLite

What is SQLite?

Android SQLite CRUD example

If you are looking for tutorial on Syncing data from Android SQLite to Remote MySQL DB, please follow this link. Otherwise proceed with below tutorial.

Video Demo

Steps involved in Syncing SQLite with MySQL:

By looking at the video demo, you got to know what are we going to develop.

About Application

  • It is a simple application which accepts Users’ names and store them in SQLite DB.
  • When ‘Sync’ action button in Action Bar is pressed, application syncs up data present in SQLite DB with remote MySQL DB.

Here are the steps involved in SQLite and MySQL Sync up:

Sync Steps

Developing the application involves two parts:

  1. Php/MySQL
  2. Android

You can download source code from here if you don’t want to create Application from scratch, otherwise please proceed with below listings.

Php/MySQL

Do follow below sections to create Php Classes and also to configure MySQL DB.

Create MySQL DB and Table:

I chose PHP/MySQL combo for creating API to connect and Android device to MySQL DB since the configuration is simple and easy and also it will be easy for you to start with.

I will write separate posts on how to handle Sync up using Java and .Net technologies.

  1. Create database called ‘db’ in phpMyAdmin or create it through command:
    create database db
  2. Select database db in phpMyAdmin or select it through command:
    use db
  3. Create table called ‘users’ in phpMyAdmin by pasting below SQL script in SQL Query box:
    CREATE TABLE IF NOT EXISTS 'users' (
      'Id' int(11) NOT NULL,
      'Name' varchar(100) NOT NULL,
      PRIMARY KEY (`Id`)
    )

Build PHP Classes

Create following PHP Classes and place them under ‘www/sqlitemysqlsync’ folder which will provide interface between Android application and MySQL DB:

sqlite mysql sync phps

config.php:

<?php
/**
 * DB configuration variables
 */
define("DB_HOST", "localhost");
define("DB_USER", "UserName");
define("DB_PASSWORD", "PWD");
define("DB_DATABASE", "db");
?>

db_connect.php:

<?php

class DB_Connect {

    // constructor
    function __construct() {

    }

    // destructor
    function __destruct() {
        // $this->close();
    }

    // Connecting to database
    public function connect() {
        require_once 'config.php';
        // connecting to mysql
        $con = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
        // selecting database
        mysql_select_db(DB_DATABASE);

        // return database handler
        return $con;
    }

    // Closing database connection
    public function close() {
        mysql_close();
    }

} 
?>

db_functions.php:

<?php

class DB_Functions {

    private $db;

    //put your code here
    // constructor
    function __construct() {
        include_once './db_connect.php';
        // connecting to database
        $this->db = new DB_Connect();
        $this->db->connect();
    }

    // destructor
    function __destruct() {

    }

    /**
     * Storing new user
     * returns user details
     */
    public function storeUser($Id,$User) {
        // Insert user into database
        $result = mysql_query("INSERT INTO users VALUES($Id,'$User')");

        if ($result) {
			return true;
        } else {
			if( mysql_errno() == 1062) {
				// Duplicate key - Primary Key Violation
				return true;
			} else {
				// For other errors
				return false;
			}            
        }
    }
	 /**
     * Getting all users
     */
    public function getAllUsers() {
        $result = mysql_query("select * FROM users");
        return $result;
    }
}

?>

insertuser.php:

<?php
include_once './db_functions.php';
//Create Object for DB_Functions clas
$db = new DB_Functions(); 
//Get JSON posted by Android Application
$json = $_POST["usersJSON"];
//Remove Slashes
if (get_magic_quotes_gpc()){
$json = stripslashes($json);
}
//Decode JSON into an Array
$data = json_decode($json);
//Util arrays to create response JSON
$a=array();
$b=array();
//Loop through an Array and insert data read from JSON into MySQL DB
for($i=0; $i<count($data) ; $i++)
{
//Store User into MySQL DB
$res = $db->storeUser($data[$i]->userId,$data[$i]->userName);
	//Based on inserttion, create JSON response
	if($res){
		$b["id"] = $data[$i]->userId;
		$b["status"] = 'yes';
		array_push($a,$b);
	}else{
		$b["id"] = $data[$i]->userId;
		$b["status"] = 'no';
		array_push($a,$b);
	}
}
//Post JSON response back to Android Application
echo json_encode($a);
?>

viewusers.php:

<html>
<head><title>View Users</title>
<style>
body {
  font: normal medium/1.4 sans-serif;
}
table {
  border-collapse: collapse;
  width: 20%;
   margin-left: auto;
    margin-right: auto;
}
tr > td {
  padding: 0.25rem;
  text-align: center;
  border: 1px solid #ccc;
}
tr:nth-child(even) {
  background: #FAE1EE;
}
tr:nth-child(odd) {
  background: #edd3ff;
}
tr#header{
background: #c1e2ff;
}
div.header{
padding: 10px;
background: #e0ffc1;
width:30%;
color: #008000;
margin:5px;
}
div.refresh{
margin-top:10px;
width: 5%;
margin-left: auto;
margin-right: auto;
}
div#norecord{
margin-top:10px;
width: 15%;
margin-left: auto;
margin-right: auto;
}
</style>
<script>
function refreshPage(){
location.reload();
}
</script>
</head>
<body>
<center>
<div class="header">
Android SQLite and MySQL Sync Results
</div>
</center>
<?php
    include_once 'db_functions.php';
    $db = new DB_Functions();
    $users = $db->getAllUsers();
    if ($users != false)
        $no_of_users = mysql_num_rows($users);
    else
        $no_of_users = 0;
?>
<?php
    if ($no_of_users > 0) {
?>
<table>
<tr id="header"><td>Id</td><td>Username</td></tr>
<?php
    while ($row = mysql_fetch_array($users)) {
?> 
<tr>
<td><span><?php echo $row["Id"] ?></span></td>
<td><span><?php echo $row["Name"] ?></span></td>
</tr>
<?php } ?>
</table>
<?php }else{ ?>
<div id="norecord">
No records in MySQL DB
</div>
<?php } ?>
<div class="refresh">
<button onclick="refreshPage()">Refresh</button>
</div>
</body>
</html>

Types of JSON

JSON posted to insertuser.php will look like below. JSON array contains array of Objects. Each object has User’s name and User’s Id.

[
{"userName":"Ram","userId":"1"},
{"userName":"Rahul","userId":"2"},
{"userName":"Sandy","userId":"3"},
{"userName":"Linda","userId":"4"}
]

JSON returned by insertuser.php (after inserting Users in MySQL table) will look like below. JSON array contains array of Objects. Each object has User’s Id and Sync status.

[
{"id":"1","status":"yes"},
{"id":"2","status":"yes"},
{"id":"3","status":"no"},
{"id":"4","status":"yes"}
]

Android Project

Step 1: Create Android Application Project

  • Create new android project [File >> New >> Android Application Project] with project name SQLiteMySQLSync
  • Enter package name as ‘com.prgguru.example’
  • Choose Minimum Required SDK, Target SDK and Compile with as shown in the below screen-print. If you don’t have latest SDK installed, they will not be populated in the dropdownlist, I would recommend you to update Android SDK manager with latest SDKs. Confused on choosing these options? Take a look at Minimum Required SDK – Target SDK – Compile With post.
  • Select Theme as ‘Holo Light with Dark Action Bar’ which will create Action Bar by default in Android 3.0 and above.
  • Click Next button and finally click ‘Finish’ to create project

ActionBar_1

Step 2: Add libraries to project

Add below third party libraries into project’s ‘lib’ folder. You can also download them if you don’t have them with you.

1. Android Asynchronous Http Client – An asynchronous callback-based Http client for Android built on top of Apache’s HttpClient libraries which is used by Pinterest, Instagram etc.,. Download
2. Google – GSON – Gson is a Java library that can be used to convert Java Objects into their JSON representation. Download

Step 3: Design Screens

We are going to have two activities:

MainActivity – Home Screen of the Application (Launch screen)
NewUser – Insertion of User into SQLite takes place in the Activity

Before creating screens, make sure below String resources are added. Add string resources to strings.xml present under /res/values folder.

strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="app_name">SQLiteMySQLSync</string>
    <string name="hello_world">Hello world!</string>
    <string name="action_settings">Settings</string>
	<string name="title">Users</string>
	<string name="plus">"+"</string>
	<string name="adduser">"Add User"</string>
	<string name="user">"User"</string>
	<string name="hint">"Enter User's Name"</string>
	<string name="save">"Save"</string>
	<string name="cancel">"Cancel"</string>
	<string name="adduseractivitylabel">"Add User Activity"</string>
</resources>

Create three layout XMLs under /res/layout folder:

activity_main.xml

Home Screen layout

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity" >

    <RelativeLayout
        android:id="@+id/relativeLayout1"
        android:layout_width="fill_parent"
        android:layout_height="40dp"
        android:background="#cc0000"
        android:orientation="vertical" >

        <TextView
            android:id="@+id/textView1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginLeft="5dp"
            android:text="@string/title"
            android:textAppearance="?android:attr/textAppearanceLarge"
            android:textColor="#FFFFFF" />

        <Button
            android:id="@+id/button1"
            android:layout_width="41dp"
            android:layout_height="40dp"
            android:layout_alignParentRight="true"
            android:layout_alignParentTop="true"
            android:background="#ffd700"
            android:onClick="addUser"
            android:text="@string/plus"
            android:textColor="#cc0000"
            android:textSize="30sp" />

    </RelativeLayout>
     <RelativeLayout
        android:id="@+id/relativeLayout1"
        android:layout_width="fill_parent"
        android:layout_height="match_parent"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/relativeLayout1"
        android:orientation="vertical"
        android:layout_marginTop="40dp">

    <ListView
         android:id="@android:id/list"
         android:layout_width="match_parent"
         android:layout_height="wrap_content"
         android:layout_alignParentLeft="true">
     </ListView>
     </RelativeLayout>

</RelativeLayout>

add_new_user.xml

Adding User screen layout

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="#CCCCCC"
    android:orientation="vertical"
    android:paddingTop="1dp" >

    <TextView
        android:id="@+id/textView1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="#cc0000"
        android:padding="5dp"
        android:text="@string/adduser"
        android:textAppearance="?android:attr/textAppearanceLarge"
        android:textColor="#FFFFFF" />

       <RelativeLayout
     android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:background="#FFFFFF"
        android:orientation="vertical"
        android:padding="10dp" >

    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true"
        android:layout_marginLeft="24dp"
        android:layout_marginTop="30dp"
        android:text="@string/user" />

    <EditText
        android:id="@+id/userName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/textView1"
        android:layout_below="@+id/textView1"
        android:ems="10" android:hint="@string/hint">

        <requestFocus />
    </EditText>
    <Button
        android:id="@+id/btnadd"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/userName"
        android:layout_below="@+id/userName"
        android:layout_marginTop="32dp"
        android:text="@string/save"      
    	android:onClick="addNewUser" />
 <Button
        android:id="@+id/btnCancel"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/userName"
        android:layout_toRightOf="@+id/btnadd"
        android:layout_marginTop="32dp"
        android:text="@string/cancel"      
    	android:onClick="cancelAddUser" />

</RelativeLayout>

</LinearLayout>

view_user_entry.xml

Layout for ListView which is used in Home screen. Will discuss about it more in the below sections. For now create it under /res/layout folder.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:orientation="vertical" >
    	<TextView
        android:id="@+id/userId"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:visibility="gone" />

       <TextView
           android:id="@+id/userName"
           android:layout_width="fill_parent"
           android:layout_height="wrap_content"
           android:layout_marginBottom="5dp"
           android:layout_marginTop="5dp"
           android:paddingLeft="6dip"
           android:paddingTop="6dip"
           android:textColor="#008000"
           android:textSize="17sp"
           android:textStyle="bold" />

</LinearLayout>

Step 4: Create Action Bar Menu

Save Refresh icons under respective drawable folders:

mdpi hdpi xhdpi
ic_action_refresh ic_action_refresh ic_action_refresh

Create main.xml under /res/menu folder and fill it with below code:

<menu xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    tools:context="com.prgguru.actionbar.MainActivity" >

    <item
        android:id="@+id/refresh"
        android:icon="@drawable/ic_action_refresh"
        android:orderInCategory="100"
        app:showAsAction="always"/>

</menu>

Step 5: DBController.java – DB Handler Class

We are done with Layout designing, let us jump into Coding.

Create DBController.Java under the package com.prgguru.example and fill it with below code. It handles DB related operations.

package com.prgguru.example;

import java.util.ArrayList;
import java.util.HashMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;

public class DBController  extends SQLiteOpenHelper {

	public DBController(Context applicationcontext) {
        super(applicationcontext, "androidsqlite.db", null, 1);
    }
	//Creates Table
	@Override
	public void onCreate(SQLiteDatabase database) {
		String query;
		query = "CREATE TABLE users ( userId INTEGER PRIMARY KEY, userName TEXT, udpateStatus TEXT)";
        database.execSQL(query);
	}
	@Override
	public void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {
		String query;
		query = "DROP TABLE IF EXISTS users";
		database.execSQL(query);
        onCreate(database);
	}
	/**
	 * Inserts User into SQLite DB
	 * @param queryValues
	 */
	public void insertUser(HashMap<String, String> queryValues) {
		SQLiteDatabase database = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("userName", queryValues.get("userName"));
		values.put("udpateStatus", "no");
		database.insert("users", null, values);
		database.close();
	}

	/**
	 * Get list of Users from SQLite DB as Array List
	 * @return
	 */
	public ArrayList<HashMap<String, String>> getAllUsers() {
		ArrayList<HashMap<String, String>> wordList;
		wordList = new ArrayList<HashMap<String, String>>();
		String selectQuery = "SELECT  * FROM users";
	    SQLiteDatabase database = this.getWritableDatabase();
	    Cursor cursor = database.rawQuery(selectQuery, null);
	    if (cursor.moveToFirst()) {
	        do {
	        	HashMap<String, String> map = new HashMap<String, String>();
	        	map.put("userId", cursor.getString(0));
	        	map.put("userName", cursor.getString(1));
                wordList.add(map);
	        } while (cursor.moveToNext());
	    }
	    database.close();
	    return wordList;
	}

	/**
	 * Compose JSON out of SQLite records
	 * @return
	 */
	public String composeJSONfromSQLite(){
		ArrayList<HashMap<String, String>> wordList;
		wordList = new ArrayList<HashMap<String, String>>();
		String selectQuery = "SELECT  * FROM users where udpateStatus = '"+"no"+"'";
	    SQLiteDatabase database = this.getWritableDatabase();
	    Cursor cursor = database.rawQuery(selectQuery, null);
	    if (cursor.moveToFirst()) {
	        do {
	        	HashMap<String, String> map = new HashMap<String, String>();
	        	map.put("userId", cursor.getString(0));
	        	map.put("userName", cursor.getString(1));
	        	wordList.add(map);
	        } while (cursor.moveToNext());
	    }
	    database.close();
		Gson gson = new GsonBuilder().create();
		//Use GSON to serialize Array List to JSON
		return gson.toJson(wordList);
	}

	/**
	 * Get Sync status of SQLite
	 * @return
	 */
	public String getSyncStatus(){
	    String msg = null;
	    if(this.dbSyncCount() == 0){
	    	msg = "SQLite and Remote MySQL DBs are in Sync!";
	    }else{
	    	msg = "DB Sync neededn";
	    }
	    return msg;
	}

	/**
	 * Get SQLite records that are yet to be Synced
	 * @return
	 */
	public int dbSyncCount(){
		int count = 0;
		String selectQuery = "SELECT  * FROM users where udpateStatus = '"+"no"+"'";
	    SQLiteDatabase database = this.getWritableDatabase();
	    Cursor cursor = database.rawQuery(selectQuery, null);
	    count = cursor.getCount();
	    database.close();
		return count;
	}

	/**
	 * Update Sync status against each User ID
	 * @param id
	 * @param status
	 */
	public void updateSyncStatus(String id, String status){
		SQLiteDatabase database = this.getWritableDatabase();	 
		String updateQuery = "Update users set udpateStatus = '"+ status +"' where userId="+"'"+ id +"'";
		Log.d("query",updateQuery);		
		database.execSQL(updateQuery);
		database.close();
	}
}

Step 6: MainActivity.java – Home Screen Activity

MainActivity – Home Screen displays list of Users already inserted in SQLite DB.

MainAcitivty.java is well commented, so by reading the comments you will understand the meaning of it. In case, if you still have question discuss it right away.

package com.prgguru.example;

import java.util.ArrayList;
import java.util.HashMap;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.app.ProgressDialog;
import android.content.Intent;
import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.Toast;

import com.loopj.android.http.AsyncHttpClient;
import com.loopj.android.http.AsyncHttpResponseHandler;
import com.loopj.android.http.RequestParams;

public class MainActivity extends ActionBarActivity {
	//DB Class to perform DB related operations
	DBController controller = new DBController(this);
	//Progress Dialog Object
	ProgressDialog prgDialog;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		//Get User records from SQLite DB 
		ArrayList<HashMap<String, String>> userList =  controller.getAllUsers();
		//
		if(userList.size()!=0){
			//Set the User Array list in ListView
			ListAdapter adapter = new SimpleAdapter( MainActivity.this,userList, R.layout.view_user_entry, new String[] { "userId","userName"}, new int[] {R.id.userId, R.id.userName});
			ListView myList=(ListView)findViewById(android.R.id.list);
			myList.setAdapter(adapter);
			//Display Sync status of SQLite DB
			Toast.makeText(getApplicationContext(), controller.getSyncStatus(), Toast.LENGTH_LONG).show();
		}
		//Initialize Progress Dialog properties
		prgDialog = new ProgressDialog(this);
		prgDialog.setMessage("Synching SQLite Data with Remote MySQL DB. Please wait...");
		prgDialog.setCancelable(false);
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main, menu);
		return true;
	}

	@Override
	public boolean onOptionsItemSelected(MenuItem item) {
		// Handle action bar item clicks here. The action bar will
		// automatically handle clicks on the Home/Up button, so long
		// as you specify a parent activity in AndroidManifest.xml.
		int id = item.getItemId();
		//When Sync action button is clicked
		if (id == R.id.refresh) {
			//Sync SQLite DB data to remote MySQL DB
			syncSQLiteMySQLDB();
			return true;
		}
		return super.onOptionsItemSelected(item);
	}
	//Add User method getting called on clicking (+) button
	public void addUser(View view) {
		Intent objIntent = new Intent(getApplicationContext(), NewUser.class);
		startActivity(objIntent);
	}

	public void syncSQLiteMySQLDB(){
		//Create AsycHttpClient object
		AsyncHttpClient client = new AsyncHttpClient();
		RequestParams params = new RequestParams();
		ArrayList<HashMap<String, String>> userList =  controller.getAllUsers();
		if(userList.size()!=0){
			if(controller.dbSyncCount() != 0){
				prgDialog.show();
				params.put("usersJSON", controller.composeJSONfromSQLite());
				client.post("http://192.168.2.4:9000/sqlitemysqlsync/insertuser.php",params ,new AsyncHttpResponseHandler() {
					@Override
					public void onSuccess(String response) {
						System.out.println(response);
						prgDialog.hide();
						try {
							JSONArray arr = new JSONArray(response);
							System.out.println(arr.length());
							for(int i=0; i<arr.length();i++){
								JSONObject obj = (JSONObject)arr.get(i);
								System.out.println(obj.get("id"));
								System.out.println(obj.get("status"));
								controller.updateSyncStatus(obj.get("id").toString(),obj.get("status").toString());
							}
							Toast.makeText(getApplicationContext(), "DB Sync completed!", Toast.LENGTH_LONG).show();
						} catch (JSONException e) {
							// TODO Auto-generated catch block
							Toast.makeText(getApplicationContext(), "Error Occured [Server's JSON response might be invalid]!", Toast.LENGTH_LONG).show();
							e.printStackTrace();
						}
					}

					@Override
					public void onFailure(int statusCode, Throwable error,
						String content) {
						// TODO Auto-generated method stub
						prgDialog.hide();
						if(statusCode == 404){
							Toast.makeText(getApplicationContext(), "Requested resource not found", Toast.LENGTH_LONG).show();
						}else if(statusCode == 500){
							Toast.makeText(getApplicationContext(), "Something went wrong at server end", Toast.LENGTH_LONG).show();
						}else{
							Toast.makeText(getApplicationContext(), "Unexpected Error occcured! [Most common Error: Device might not be connected to Internet]", Toast.LENGTH_LONG).show();
						}
					}
				});
			}else{
				Toast.makeText(getApplicationContext(), "SQLite and Remote MySQL DBs are in Sync!", Toast.LENGTH_LONG).show();
			}
		}else{
				Toast.makeText(getApplicationContext(), "No data in SQLite DB, please do enter User name to perform Sync action", Toast.LENGTH_LONG).show();
		}
	}

}

Make sure the IP address has been changed in post method with localhost or IP address of your machine:

client.post("http://192.168.2.4:9000/sqlitemysqlsync/insertuser.php",params ,new AsyncHttpResponseHandler() {

Step 7: NewUser.java

Add User Activity which allows us to add User name into SQLite database. Create NewUser.java under com.prgguru.example and fill it with below code:

package com.prgguru.example;

import java.util.HashMap;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;

public class NewUser extends Activity {
	EditText userName;
	DBController controller = new DBController(this);

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.add_new_user);
		userName = (EditText) findViewById(R.id.userName);
	}

	/**
	 * Called when Save button is clicked 
	 * @param view
	 */
	public void addNewUser(View view) {
		HashMap<String, String> queryValues = new HashMap<String, String>();
		queryValues.put("userName", userName.getText().toString());
		if (userName.getText().toString() != null
				&& userName.getText().toString().trim().length() != 0) {
			controller.insertUser(queryValues);
			this.callHomeActivity(view);
		} else {
			Toast.makeText(getApplicationContext(), "Please enter User name",
					Toast.LENGTH_LONG).show();
		}
	}

    /**
     * Navigate to Home Screen 
     * @param view
     */
	public void callHomeActivity(View view) {
		Intent objIntent = new Intent(getApplicationContext(),
				MainActivity.class);
		startActivity(objIntent);
	}

	/**
	 * Called when Cancel button is clicked
	 * @param view
	 */
	public void cancelAddUser(View view) {
		this.callHomeActivity(view);
	}
}

Step 8: Add Activities in AndroidManifest.xml

 <activity
            android:name="com.prgguru.example.MainActivity"
            android:label="@string/app_name" android:noHistory="true" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />
                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
          <activity
            android:name="com.prgguru.example.NewUser"
            android:label="@string/adduseractivitylabel" android:noHistory="true">
        </activity>

Step 9: Add Permission in AndroidManifest.xml

Don’t forget to add internet permission in AndroidManifest.xml:

    <!-- Permission: Allow application to connect to Internet -->
    <uses-permission android:name="android.permission.INTERNET" />

Syncing process – Quick Explanation

SQLite Table users structure:

Columns

userId – Denotes User ID (Auto incremented)
userName – Name of the user entered via NewUser activity
udpateStatus- Update status (Sync status) of the records in MySQL

  1. Initially New record is inserted in users table with updateStatus as ‘no’
  2. When Sync button is clicked, records with updateStatus as ‘no’ are picked, composes JSON out of the selected records and finally the JSON is posted to insertuser.php via http
  3. The insertuser.php decodes the incoming JSON and creates Array out of it. It then inserts array values into MySQL table and generate response JSON which contains insertion status (yes/no).
  4. Application reads the JSON response and update the users table with status values(yes/no) against the User ID

[pglinkadssmall1]

Error Handling

When the status code returned by insertuser.php is other 200, which means an error might have occurred at server end. Error can be easily handled inside onFailure() method of AsyncHttpClient’s post method as shown below:

@Override
public void onFailure(int statusCode, Throwable error,String content) {
	// TODO Auto-generated method stub
	prgDialog.hide();
	if(statusCode == 404){
		Toast.makeText(getApplicationContext(), "Requested resource not found", Toast.LENGTH_LONG).show();
	}else if(statusCode == 500){
		Toast.makeText(getApplicationContext(), "Something went wrong at server end", Toast.LENGTH_LONG).show();
	}else{
		Toast.makeText(getApplicationContext(), "Unexpected Error occcured! [Most common Error: Device might not be connected to Internet]", Toast.LENGTH_LONG).show();
	}
}

Demo

That’s all. It’s time to test our code.

Run the application using emulator or device by right clicking on the project >> Run as >> Android applicaiton >> Choose emulator or device.



Download Source Code

Entire project is zipped and is available for download. Unzip the downloaded project and to import the project into eclipse, launch eclipse >> File >> Import.. >> Choose downloaded project(How to import android project in eclipse).

Download Source Code

*apk in Android  is the installation file similar to exe in windows.

[pglinkadssmall]

If you feel this article is helpful and interesting please spread a word about it to your friends and colleagues by sharing the article in Facebook or Twitter.


Share

You are always welcome to provide your comments and feedback from comment box.

Reference
Android SQLite
[pgwriteforus]
[pgfeedback]

Related Articles

Author: Udhay

Share This Post On
468 ad
  • pari13

    Hello Sir,

    What about MS-SQL Database ?

    I really want similiar features is with MS-SQL Database .

    Can you provide idea/or link to suggest me?

    Regards,
    Pari..!!

    • Use ASP/C# in the place of PHP and SQL Server in the place of MySQL.

      Construct JSON out of SQLite records in the Android application and send it to ASP page.

      ASP page should parse the JSON and insert the records into SQL Server.

      I already discussed about handling JSON in ASP .Net.

      Take a look at this post: http://programmerguru.com/android-tutorial/android-json-web-service-tutorial/

      • Rasheed

        hi god job
        you have synchronized two values so how i should synchronize more values from sqlite to mysql.
        i have database named as Users having fields {Name,address,age,email,phone#} so how should i pass these five values

  • cris angle

    is is bidirectional sync if not how to achive it
    with PHP mysql and sqlite for offline access and update

  • Hey nice tutorial for beginners and it is clearly explained.
    Thanks man and you have used same name for two different files – db_functions.php for two files so change it i think one is insertuser.php.

  • Hi Cris,

    This example is not bidirectional sync, the sync operation happens only from Android application to MySQL. In case if you want to implement bidirectional sync, all you need to do is when Sync button is clicked it should do two operations sequentially:

    1. Sync must happen from Android application to MySQL
    2. Sync should happen in reverse, from MySQL to Android application

    In both the cases, use JSON as it is easy to transport since it is light-weight and also easy to parse.

  • Thanks much Wasim for the finding. 🙂

  • luisk

    when in get the response is just this [] and length = 0 and the users are not insert can you help me please

    • @disqus_W3xKHOvRha:disqus

      Can you please post the code and error message(if you are getting any) here so that I will look into them and help you out?

      • luisk

        I dont get any error is just that the $_POST in the php is empty because i make this test
        $json = $_POST[“usersJSON”];

        if($_POST){

        if (get_magic_quotes_gpc()){
        $json = stripslashes($json);
        }
        //Decode JSON into an Array
        $data = json_decode($json);
        echo $data;
        }
        else{

        echo “error”;
        }

        in android i get “error” , i dont now why i cant get the post in the php

        • Can you try to print $_POST[‘userJSON”] using echo? Also check whether the Android application is constructing valid JSON and set it in the post variable usersJSON. It just works fine for me.

    • Fasiha Ikram

      i have same issue can you pleas help me out?

      05-22 20:15:04.054: E/SpannableStringBuilder(2550): SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length

      05-22 20:15:04.054: E/SpannableStringBuilder(2550): SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length

      05-22 20:15:09.354: D/AbsListView(2550): Get MotionRecognitionManager

      05-22 20:15:09.834: W/IInputConnectionWrapper(2550): getSelectedText on inactive InputConnection

      05-22 20:15:09.854: W/IInputConnectionWrapper(2550): setComposingText on inactive InputConnection

      05-22 20:15:10.014: W/IInputConnectionWrapper(2550): getExtractedText on inactive InputConnection

      05-22 20:15:10.624: E/WindowManager(2550): Activity com.prgguru.example.MainActivity has leaked window com.android.internal.policy.impl.PhoneWindow$DecorView@419a3ce0 that was originally added here

      05-22 20:15:10.624: E/WindowManager(2550): android.view.WindowLeaked: Activity com.prgguru.example.MainActivity has leaked window com.android.internal.policy.impl.PhoneWindow$DecorView@419a3ce0 that was originally added here

      05-22 20:15:10.624: E/WindowManager(2550): at android.view.ViewRootImpl.(ViewRootImpl.java:419)

      05-22 20:15:10.624: E/WindowManager(2550): at android.view.WindowManagerImpl.addView(WindowManagerImpl.java:322)

      05-22 20:15:10.624: E/WindowManager(2550): at android.view.WindowManagerImpl.addView(WindowManagerImpl.java:234)

      05-22 20:15:10.624: E/WindowManager(2550): at android.view.WindowManagerImpl$CompatModeWrapper.addView(WindowManagerImpl.java:153)

      05-22 20:15:10.624: E/WindowManager(2550): at android.view.Window$LocalWindowManager.addView(Window.java:557)

      05-22 20:15:10.624: E/WindowManager(2550): at android.app.Dialog.show(Dialog.java:277)

      05-22 20:15:10.624: E/WindowManager(2550): at com.prgguru.example.MainActivity.syncSQLiteMySQLDB(MainActivity.java:93)

      05-22 20:15:10.624: E/WindowManager(2550): at com.prgguru.example.MainActivity.onOptionsItemSelected(MainActivity.java:74)

      05-22 20:15:10.624: E/WindowManager(2550): at android.app.Activity.onMenuItemSelected(Activity.java:2612)

      05-22 20:15:10.624: E/WindowManager(2550): at android.support.v4.app.FragmentActivity.onMenuItemSelected(FragmentActivity.java:372)

      05-22 20:15:10.624: E/WindowManager(2550): at android.support.v7.app.ActionBarActivity.superOnMenuItemSelected(ActionBarActivity.java:244)

      05-22 20:15:10.624: E/WindowManager(2550): at android.support.v7.app.ActionBarActivityDelegateICS.onMenuItemSelected(ActionBarActivityDelegateICS.java:164)

      05-22 20:15:10.624: E/WindowManager(2550): at android.support.v7.app.ActionBarActivity.onMenuItemSelected(ActionBarActivity.java:130)

      05-22 20:15:10.624: E/WindowManager(2550): at android.support.v7.app.ActionBarActivityDelegateICS$WindowCallbackWrapper.onMenuItemSelected(ActionBarActivityDelegateICS.java:308)

      05-22 20:15:10.624: E/WindowManager(2550): at com.android.internal.policy.impl.PhoneWindow.onMenuItemSelected(PhoneWindow.java:1061)

      05-22 20:15:10.624: E/WindowManager(2550): at com.android.internal.view.menu.MenuBuilder.dispatchMenuItemSelected(MenuBuilder.java:735)

      05-22 20:15:10.624: E/WindowManager(2550): at com.android.internal.view.menu.MenuItemImpl.invoke(MenuItemImpl.java:149)

      05-22 20:15:10.624: E/WindowManager(2550): at com.android.internal.view.menu.MenuBuilder.performItemAction(MenuBuilder.java:874)

      05-22 20:15:10.624: E/WindowManager(2550): at com.android.internal.view.menu.ActionMenuView.invokeItem(ActionMenuView.java:603)

      05-22 20:15:10.624: E/WindowManager(2550): at com.android.internal.view.menu.ActionMenuItemView.onClick(ActionMenuItemView.java:162)

      05-22 20:15:10.624: E/WindowManager(2550): at android.view.View.performClick(View.java:4191)

      05-22 20:15:10.624: E/WindowManager(2550): at android.view.View$PerformClick.run(View.java:17229)

      05-22 20:15:10.624: E/WindowManager(2550): at android.os.Handler.handleCallback(Handler.java:615)

      05-22 20:15:10.624: E/WindowManager(2550): at android.os.Handler.dispatchMessage(Handler.java:92)

      05-22 20:15:10.624: E/WindowManager(2550): at android.os.Looper.loop(Looper.java:137)

      05-22 20:15:10.624: E/WindowManager(2550): at android.app.ActivityThread.main(ActivityThread.java:4960)

      05-22 20:15:10.624: E/WindowManager(2550): at java.lang.reflect.Method.invokeNative(Native Method)

      05-22 20:15:10.624: E/WindowManager(2550): at java.lang.reflect.Method.invoke(Method.java:511)

      05-22 20:15:10.624: E/WindowManager(2550): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1038)

      05-22 20:15:10.624: E/WindowManager(2550): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:805)

      05-22 20:15:10.624: E/WindowManager(2550): at dalvik.system.NativeStart.main(Native Method)

      05-22 20:15:16.294: I/System.out(2550): [{“id”:”1″,”status”:”no”},{“id”:”2″,”status”:”no”},{“id”:”3″,”status”:”no”}]

      05-22 20:15:16.294: I/System.out(2550): 3

      05-22 20:15:16.304: I/System.out(2550): 1

      05-22 20:15:16.304: I/System.out(2550): no

      05-22 20:15:16.314: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’1′

      05-22 20:15:16.314: I/System.out(2550): 2

      05-22 20:15:16.324: I/System.out(2550): no

      05-22 20:15:16.324: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’2′

      05-22 20:15:16.334: I/System.out(2550): 3

      05-22 20:15:16.334: I/System.out(2550): no

      05-22 20:15:16.344: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’3′

      05-22 20:15:19.964: I/System.out(2550): [{“id”:”1″,”status”:”no”},{“id”:”2″,”status”:”no”},{“id”:”3″,”status”:”no”}]

      05-22 20:15:19.964: I/System.out(2550): 3

      05-22 20:15:19.964: I/System.out(2550): 1

      05-22 20:15:19.964: I/System.out(2550): no

      05-22 20:15:19.974: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’1′

      05-22 20:15:19.984: I/System.out(2550): 2

      05-22 20:15:19.984: I/System.out(2550): no

      05-22 20:15:19.994: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’2′

      05-22 20:15:19.994: I/System.out(2550): 3

      05-22 20:15:19.994: I/System.out(2550): no

      05-22 20:15:20.004: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’3′

      05-22 20:15:21.704: I/System.out(2550): [{“id”:”1″,”status”:”no”},{“id”:”2″,”status”:”no”},{“id”:”3″,”status”:”no”}]

      05-22 20:15:21.704: I/System.out(2550): 3

      05-22 20:15:21.704: I/System.out(2550): 1

      05-22 20:15:21.704: I/System.out(2550): no

      05-22 20:15:21.724: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’1′

      05-22 20:15:21.724: I/System.out(2550): 2

      05-22 20:15:21.724: I/System.out(2550): no

      05-22 20:15:21.734: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’2′

      05-22 20:15:21.734: I/System.out(2550): 3

      05-22 20:15:21.734: I/System.out(2550): no

      05-22 20:15:21.744: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’3′

      05-22 20:15:22.624: W/Choreographer(2550): Already have a pending vsync event. There should only be one at a time.

      05-22 20:15:24.334: I/System.out(2550): [{“id”:”1″,”status”:”no”},{“id”:”2″,”status”:”no”},{“id”:”3″,”status”:”no”}]

      05-22 20:15:24.334: I/System.out(2550): 3

      05-22 20:15:24.334: I/System.out(2550): 1

      05-22 20:15:24.334: I/System.out(2550): no

      05-22 20:15:24.354: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’1′

      05-22 20:15:24.354: I/System.out(2550): 2

      05-22 20:15:24.354: I/System.out(2550): no

      05-22 20:15:24.364: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’2′

      05-22 20:15:24.374: I/System.out(2550): 3

      05-22 20:15:24.374: I/System.out(2550): no

      05-22 20:15:24.394: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’3′

      05-22 20:15:26.424: I/System.out(2550): [{“id”:”1″,”status”:”no”},{“id”:”2″,”status”:”no”},{“id”:”3″,”status”:”no”}]

      05-22 20:15:26.424: I/System.out(2550): 3

      05-22 20:15:26.424: I/System.out(2550): 1

      05-22 20:15:26.424: I/System.out(2550): no

      05-22 20:15:26.434: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’1′

      05-22 20:15:26.444: I/System.out(2550): 2

      05-22 20:15:26.444: I/System.out(2550): no

      05-22 20:15:26.454: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’2′

      05-22 20:15:26.474: I/System.out(2550): 3

      05-22 20:15:26.474: I/System.out(2550): no

      05-22 20:15:26.484: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’3′

      05-22 20:17:00.134: I/System.out(2550): [{“id”:”1″,”status”:”no”},{“id”:”2″,”status”:”no”},{“id”:”3″,”status”:”no”}]

      05-22 20:17:00.134: I/System.out(2550): 3

      05-22 20:17:00.134: I/System.out(2550): 1

      05-22 20:17:00.134: I/System.out(2550): no

      05-22 20:17:00.154: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’1′

      05-22 20:17:00.164: I/System.out(2550): 2

      05-22 20:17:00.164: I/System.out(2550): no

      05-22 20:17:00.174: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’2′

      05-22 20:17:00.174: I/System.out(2550): 3

      05-22 20:17:00.174: I/System.out(2550): no

      05-22 20:17:00.184: D/query(2550): Update users set udpateStatus = ‘no’ where userId=’3′

      05-22 20:20:48.544: D/ActivityThread(4068): setTargetHeapUtilization:0.25

      05-22 20:20:48.544: D/ActivityThread(4068): setTargetHeapIdealFree:8388608

      05-22 20:20:48.544: D/ActivityThread(4068): setTargetHeapConcurrentStart:2097152

      05-22 20:20:48.824: D/AbsListView(4068): Get MotionRecognitionManager

      05-22 20:20:49.264: D/libEGL(4068): loaded /system/lib/egl/libEGL_adreno200.so

      05-22 20:20:49.334: D/libEGL(4068): loaded /system/lib/egl/libGLESv1_CM_adreno200.so

      05-22 20:20:49.354: D/libEGL(4068): loaded /system/lib/egl/libGLESv2_adreno200.so

      05-22 20:20:49.364: I/Adreno200-EGL(4068): : EGL 1.4 QUALCOMM build: AU_LINUX_ANDROID_JB_REL_2.0.3.04.01.02.21.107_msm8625_JB_REL_2.0.3_CL3357771_release_AU (CL3357771)

      05-22 20:20:49.364: I/Adreno200-EGL(4068): Build Date: 02/25/13 Mon

      05-22 20:20:49.364: I/Adreno200-EGL(4068): Local Branch:

      05-22 20:20:49.364: I/Adreno200-EGL(4068): Remote Branch: quic/jb_rel_2.0.3

      05-22 20:20:49.364: I/Adreno200-EGL(4068): Local Patches: NONE

      05-22 20:20:49.364: I/Adreno200-EGL(4068): Reconstruct Branch: AU_LINUX_ANDROID_JB_REL_2.0.3.04.01.02.21.107 + NOTHING

      05-22 20:20:49.794: D/OpenGLRenderer(4068): Enabling debug mode 0

      05-22 20:20:49.974: I/Choreographer(4068): Skipped 57 frames! The application may be doing too much work on its main thread.

      05-22 20:20:55.054: I/Choreographer(4068): Skipped 35 frames! The application may be doing too much work on its main thread.

      05-22 20:20:56.004: I/System.out(4068): [{“id”:”1″,”status”:”no”},{“id”:”2″,”status”:”no”},{“id”:”3″,”status”:”no”}]

      05-22 20:20:56.014: I/System.out(4068): 3

      05-22 20:20:56.014: I/System.out(4068): 1

      05-22 20:20:56.014: I/System.out(4068): no

      05-22 20:20:56.034: D/query(4068): Update users set udpateStatus = ‘no’ where userId=’1′

      05-22 20:20:56.074: I/System.out(4068): 2

      05-22 20:20:56.074: I/System.out(4068): no

      05-22 20:20:56.084: D/query(4068): Update users set udpateStatus = ‘no’ where userId=’2′

      05-22 20:20:56.094: I/System.out(4068): 3

      05-22 20:20:56.094: I/System.out(4068): no

      05-22 20:20:56.104: D/query(4068): Update users set udpateStatus = ‘no’ where userId=’3′

      05-22 20:20:56.774: I/Choreographer(4068): Skipped 46 frames! The application may be doing too much work on its main thread.

      • Try to use Android native keyboard to avoid this issue. Its already discussed here: http://stackoverflow.com/questions/19682982/10-29-110453-626-e-spannablestringbuilder2815-span-exclusive-exclusive

        Take a look.

        • Sophie Marry

          please look at my issue… i posted above i have to sort out this ASAP

      • Sophie Marry

        Hi… have you done with this? i am also getting : Notice: Undefined index: usersJSON in /opt/lampp/htdocs/sqlitemysqlsync/insertuser.php on line 8 [ ] and my log looks like this:

        11-17 11:20:38.327: I/System.out(2258): [{“id”:”1″,”status”:”no”},{“id”:”2″,”status”:”no”},{“id”:”3″,”status”:”no”},{“id”:”4″,”status”:”no”},{“id”:”5″,”status”:”no”},{“id”:”6″,”status”:”no”}]

        11-17 11:20:38.327: I/System.out(2258): 6

        11-17 11:20:38.327: I/System.out(2258): 1

        11-17 11:20:38.327: I/System.out(2258): no

        11-17 11:20:38.337: D/query(2258): Update users set udpateStatus = ‘no’ where userId=’1′

        11-17 11:20:38.337: I/System.out(2258): 2

        11-17 11:20:38.347: I/System.out(2258): no

        11-17 11:20:38.347: D/query(2258): Update users set udpateStatus = ‘no’ where userId=’2′

        11-17 11:20:38.347: D/hwcomposer(148): int IntelDisplayPlaneManager::setZOrderConfig(int, int): set zorder: 3

        11-17 11:20:38.347: E/IMGSRV(148): :0: PVRDRMOpen: TP3, ret = 55

        11-17 11:20:38.347: I/System.out(2258): 3

        11-17 11:20:38.347: I/System.out(2258): no

        11-17 11:20:38.357: D/query(2258): Update users set udpateStatus = ‘no’ where userId=’3′

        11-17 11:20:38.357: I/System.out(2258): 4

        11-17 11:20:38.357: I/System.out(2258): no

        11-17 11:20:38.367: D/query(2258): Update users set udpateStatus = ‘no’ where userId=’4′

        11-17 11:20:38.367: I/System.out(2258): 5

        11-17 11:20:38.367: I/System.out(2258): no

        11-17 11:20:38.377: D/query(2258): Update users set udpateStatus = ‘no’ where userId=’5′

        11-17 11:20:38.377: I/System.out(2258): 6

        11-17 11:20:38.377: I/System.out(2258): no

        11-17 11:20:38.387: D/hwcomposer(148): int IntelDisplayPlaneManager::setZOrderConfig(int, int): set zorder: 0

        11-17 11:20:38.387: D/query(2258): Update users set udpateStatus = ‘no’ where userId=’6′

  • Chandra Shekhar

    nice tutorial
    How to sync in reverse case..i mean from Mysql to sqlite

    • I will publish an article on how to Sync data from MySQL DB to Android soon.

      • Kamran

        Hey Android Guru, I was very nice tutorial for understanding all the procedures of sync between android application and server, have you published the article for reverse case? if so do reply plz

      • Indra

        Waiting for reverse syncing…. kindly update as soon as possible

  • Aakash

    Why my app is crashing after clicking sync button?? please tell me the way to correct it

    • Please analyze the logcat messages.

      • Aakash

        thanks! it worked..
        can you tell how to achieve the bidirectional sync.

  • Siddharth Verma

    when localhost is used in MainActivity.java i get “not connected to internet” error and when i use my IP address i get ” Resource not found”

  • Thanks. I am yet to publish.

  • Done with coding part. Yet to publish the post. Not getting time to work on the post. Will update you soon once it is published.

  • Please make sure you enabled internet in your Android phone and also both phone and machine (where Apache server is running) are connected to same WiFi internet connection.

    • Vinay

      Iam also facing with the same problem
      When i print the status code in Toast it has showed 403 i.e., it has an 403 error code How to solve this..?

  • Luis Pereira

    How can I download the project? When I click to download, the page forwards to a new page login / register … but no such register or log.

    What am I doing wrong?

  • Its fixed now. No login/registration is needed going forward. Happy learning!

  • Rajal Nigam

    Hello Android Guru,

    The tutorial is excellent and helpful, I am stuck at one place as you said, to save the data in MySQL at server end one need to share same internet connection but if I want to code it in such a way that my app will be run from any where but still could save the data on server.

    • Thanks Rajal.
      I just said that because the Php/MySQL application is hosted in localhost but not in server space.Buy domain name and server space, host Php/MySQL application under the server space so that you can access it across the globe. Make sure you configure the domain address accordingly in the code as shown below:

      Localhost: http://192.168.2.4:9000/sqlitemysqlsync/

      Domain:
      http://yourdomainname.com/sqlitemysqlsync/

      Hope it helps you.

      • Rajal Nigam

        Thanks so much for a quick response. I would upload, check and would update this status. Thanks again.

      • Vinay

        Iam having a domain name and also a server space but still in confusing how to give the ip address of my domain.Can you please explore it..?

      • Rajal Nigam

        Hi Android Guru,

        Could you please share a Action Bar Search Filter option for a ListView using “android.support.v7.widget.SearchView”. If possible a working option as I tried lot of tutorial and couldn’t get working.

  • Eric Lim

    Hi sir, im having these errors..

    Description Resource Path Location Type

    The type android.support.v4.app.FragmentActivity cannot be resolved. It is indirectly referenced from required .class files MainActivity.java /MySQLSQLLiteSync/src/com/prgguru/example line 31 Java Problem

    The project was not built since its build path is incomplete. Cannot find the class file for android.support.v4.app.FragmentActivity. Fix the build path then try building this project MySQLSQLLiteSync Unknown Java Problem.

    How can i fix these? Thank you.

  • Vinay

    This tutorial is great When Im running this app on my emulator Its saying unable to connect to the internet even tough its connected to internet,Please help me to solve this

  • Hi Vinay,

    Http error 403 clearly indicates Php files in your local server are forbidden.

    Please take a look at this link for more info:

    http://www.cyberciti.biz/faq/apache-403-forbidden-error-and-solution/

  • Please try to print statusCode in onFailure method of MainActivity Java Class. If you are getting statusCode as 401 or 403 try to resolve the Forbidden or Proxy Authentication Required error.

    Try to debug and see what the issue and where the issue is occuring.

  • Kishan Gandhi

    Excellent Example! Works like a charm! Thank you so much for this wonderful code and explanation. 🙂 🙂 🙂

  • Kishan Gandhi

    Hi, i was wondering what if there is an update operation to be carried out on MySQL database when and user information is changed on Android App Sqlite daabase.
    At the moment we are just inserting values into MySQL db when Sync is clicked. But this is ok for values which are newly inserted. How can i sync the mysql database for some user with updated value? Please help me out with this issue!

  • Very simple, update the Column ‘udpateStatus’ in SQLite ‘users’ table as ‘no’ when User changes the information so that the row will get picked when you press Sync button. You have to take care of checking if the row needs to be inserted or updated (at the time of Sync) in Php script (insertuser.php) by using the Primary key ‘userId’ in users table.

    Update me once you are able to achieve it. 🙂

    • Kishan Gandhi

      Ok Thanks! Will try it out and notify you 🙂

    • Kishan Gandhi

      Hi Admin! I have successfully implemented the UPDATE Sync in my Application.
      Now if there is any NEW Entries and/or Updated Entries, both are getting synchronized with MySQL DB.
      Thanks a lot 🙂 🙂

      • Sophie Marry

        Hello… @Kishan Gandhi can you share your updated code with me, to sync updated data to server and any new entries as well if available, and one more thing i am getting Notice: Undefined index: usersJSON in /opt/lampp/htdocs/sqlitemysqlsync/insertuser.php on line 6[] so what could be the reason and how to solve this ?

  • You don’t need to use IP address in case if you have domain name and also server space.

    Replace IP address in URLs with your domain name, simple as that.

    For ex: http://domainname.com/sqlitemysqlsync/insertuser.php

  • Vinay

    I have tried the app it works fine. But when i tried according to the my requirement its not running the onSuccess() directly after composing to json its executing onFailure().Please help me in solving this

  • HI Vinay,

    Check for http response code in onFailure().
    If response code is:

    404 – Check URL path in post() method (Resource not found error)
    500 – Error in Php/MySql (Internal server error)

    If you are getting http code other than the above said response codes, please fix it accordingly.

  • Wow. That’s great Kishan. 🙂
    Do subscribe to my blog and keep getting updates on new articles I publish.

    Happy learning !!

  • Mahesa Kannygara

    Android guru, It is amazing tutorial , and it’s works !!
    but how if I want to make progress bar with status percent when sync between sqlite and mysql in server ?
    thank you .

  • Mohan Rajan

    Error import android.support.v7.app.ActionBarActivity; i cannot get the action bar activity,please help me.

  • Your Android SDK configured in Eclipse should have all latest support libraries. Please update it in case if it doesn’t have v4 and v7 support libraries.

    Follow this link for more details:

    https://developer.android.com/tools/support-library/setup.html

    • Mohan Rajan

      thanks a lot guru!!!!!

  • Will write separate post on it soon. Keep you posted!

    • Rajal Nigam

      Thanks so much Android Guru for a prompt response.

  • Meerna

    Hello Android Guru,
    I ran application on device, but when I click Save button, there is an error. Do You know what is all about?

  • Abdoul Doulab

    Thanks for the tuto!!! but in insertuser.php you must add test … if(isset($_POST[“usersJSON”])){stuff… echo json_encode($a);}

  • shraddha

    sir I am getting error after synching that the “json response might be invalid.” Please help me with this error.

  • Create five columns respectively in MySQL DB as well. While creating JSON object in Android application consider other three values too.

  • It is because of Nullpointer Exception thrown at line no 29 of NewQuote.java.

  • Yes. But it is upto developers. Since I already know Post parameter which I should pass to Php, I simply skipped that check.

  • Juan Carlos

    Hi, I try de app and in the emulator shows, unfortunally app has stopped, I don’t know where is the mistake, do you have an idea?

    • Please debug and see where exactly the error is thrown.

  • rekian

    excusme progammerguru… i implement ur code of jsonparsng to my project..
    the toast message show me that db synced, but no data store to my mysql database, and the respone data for jumlah_menu is null, cs i set it like that

    here is my code
    anything wrong with my php code maybe?

    <?php
    include_once './db_functions.php';
    //Create Object for DB_Functions clas
    $db = new DB_Functions();
    //Get JSON posted by Android Application
    $json = $_POST["pesanJSON"];
    //Remove Slashes
    if (get_magic_quotes_gpc()){
    $json = stripslashes($json);
    }
    //Decode JSON into an Array
    $data = json_decode($json);
    //Util arrays to create response JSON
    $a=array();
    $b=array();
    //Loop through an Array and insert data read from JSON into MySQL DB
    for($i=0; $istoreDetailPesanan($data[$i]->id_detail_pesan,$data[$i]->id_pesan,$data[$i]->id_menu,$data[$i]->jumlah_menu);
    //Based on inserttion, create JSON response
    if($res){
    $b[“id_detail_pesan”] = $data[$i]->id_detail_pesan;
    $b[“id_pesan”] = $data[$i]->id_pesan;
    $b[“id_menu”] = $data[$i]->id_menu;
    $b[“jumlah_menu”] = $data[$i]->jumlah_menu;
    array_push($a,$b);
    }else{
    $b[“id_detail_pesan”] = $data[$i]->id_detail_pesan;
    $b[“id_pesan”] = $data[$i]->id_pesan;
    $b[“id_menu”] = $data[$i]->id_menu;
    $b[“jumlah_menu”] = ‘null’;
    array_push($a,$b);
    }
    }
    //Post JSON response back to Android Application
    echo json_encode($a);
    ?>

    db = new DB_Connect();
    $this->db->connect();
    }

    /**
    * Storing new user
    * returns user details
    */
    public function storeDetailPesanan($id_detail_pesan,$id_pesan,$id_menu,$jumlah_menu) {
    // Insert user into database
    $result = mysql_query(“INSERT INTO detail_pesan (‘id_detail_pesan’,’id_pesan’,’id_menu’,’jumlah_menu’) VALUES((”,$id_pesan,$id_menu,$jumlah_menu)”);

    if ($result) {
    return true;
    } else {
    if( mysql_errno() == 1062) {
    // Duplicate key – Primary Key Violation
    return true;
    } else {
    // For other errors
    return false;
    }
    }
    }
    /**
    * Getting all users
    */
    public function getAllDetailPesan() {
    $result = mysql_query(“select * FROM detail_pesan”);
    return $result;
    }
    }

    ?>

  • Print status code in onSuccess or onFailure method and see what exactly the error is.
    Thanks.

  • Sophie Marry

    Android Guru 🙂 thank you so much for this amazing tutorial, no doubt, this is the best a developer can get, but i am getting Notice: Undefined index: usersJSON in /opt/lampp/htdocs/sqlitemysqlsync/insertuser.php on line 6[] so what could be the reason and how to solve this ?

  • Anto Gus

    Thanks for your tutorial, it’s very nice and tidy but i can’t launch the application it said that :

    [2014-11-19 08:00:18 – Dex Loader] Unable to execute dex: Multiple dex files define Lcom/loopj/android/http/AsyncHttpClient$1;

    [2014-11-19 08:00:18 – GSONTest] Conversion to Dalvik format failed: Unable to execute dex: Multiple dex files define Lcom/loopj/android/http/AsyncHttpClient$1;

    can you give me the solution for this problem?

  • anneb

    Hi progammerguru,

    How would I implement this without a button and this is done in the background automatically? Could you please give an example of this? So example, the data is synced every 10 secs by itself.

  • jzehn

    I love your tutorial. I am relatively new to Android programming and I’m looking for some advice. I notice that you use HashMap to hold the data from the database. For models that have many fields I was thinking of using a java object. Do you think that the HashMap is more efficient or better to use rather than storing data in a java object? I am use to designing web applictions and I tend to try and separate layers. I don’t know if we are suppose to separate layers in android. Thanks

  • This discussion may help you in identifying difference between Objects and Hashmap –
    http://www.theserverside.com/news/thread.tss?thread_id=26689

    It is upto you, you can create your patterns to separate Applications layers as UI, Business and DAO.

  • nuh

    where i need to change in code if i want to insert more than one field like i want to insert userName,userAddress and contactNumber …i tried to modify the code but the application get crash..

  • Ashish Gupta

    Sir..

    Before starting my discussion. Let me say you i love this article..

    I am facing problem in AsyncHttpResponseHandler class, i have override the 2 methods onSuccess() & onFailure().

    i have 3 parameters in OnSuccess() methods i.e (int i, Header[] headers, byte[] bytes) and u have only one parameter i.e (String response) and also the same problem in onFailure() method.
    i have also attached the Screen Shot so that u can understand easily..
    Please Fix this problem so that i can continue my work..
    waiting for ur reply.
    Thank You.

  • Hnib

    HI Guru,

    Why you use Service to create notification instead do it inside Sample BC Receiver? Thanks.

  • Mark Anthony Solsona Villudo

    [2015-09-15 22:45:13 – Dex Loader] Unable to execute dex: Multiple dex files define Lcom/loopj/android/http/AsyncHttpClient$1;

    [2015-09-15 22:45:13 – SQLiteMySQLSync] Conversion to Dalvik format failed: Unable to execute dex: Multiple dex files define Lcom/loopj/android/http/AsyncHttpClient$1;

    why ????
    please response ,your tutorials is very nice! i hope you reply , ASAP!

  • Djju333

    Your tutorial is great! Could you make a tutorial on how to make an app that sends device’s gps location (in background) to sqlite and then sqlite syncs the data with mysql? Please? 🙂

Join now to get more access to our Android Tutorials

Join now to get more access to our Android Tutorials

Once you Join, you will receive Mail containing latest Android Tutorials once a month !!

I promise I wont spam you !!

You have Successfully Subscribed!

Pin It on Pinterest

Shares