Lets learn Android Sync SQLite Database with Server. Assume we have to send some information from the application to our webserver and internet is not available on the device at a particular time. So instead of giving error to the user that internet is not available we can store the data to SQLite and send it later automatically when the internet is available. And this is what we are going to learn in this Android Sync SQLite Database with Server Tutorial.
Table of Contents
Android Sync Sqlite Database with Server Demo
- You can first see what we are going to learn in this video where I am showing the application.
- Now lets move ahead and start Android Sync Sqlite Database with Server.
Creating Web Service and MySQL Database
Creating Database
- I have the following database. I am using XAMPP you can use anything you want.
- So we have the database table. Now we will create a php script that will handle the insertion to the database.
Creating Web Service
Creating Script
- Create folder in your root directory (in my case it is htdocs).
- Now create a php file inside the folder, I have created saveName.php. And write the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | <?php /* * Database Constants * Make sure you are putting the values according to your database here */ define('DB_HOST','localhost'); define('DB_USERNAME','root'); define('DB_PASSWORD',''); define('DB_NAME', 'android'); //Connecting to the database $conn = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME); //checking the successful connection if($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } //making an array to store the response $response = array(); //if there is a post request move ahead if($_SERVER['REQUEST_METHOD']=='POST'){ //getting the name from request $name = $_POST['name']; //creating a statement to insert to database $stmt = $conn->prepare("INSERT INTO names (name) VALUES (?)"); //binding the parameter to statement $stmt->bind_param("s", $name); //if data inserts successfully if($stmt->execute()){ //making success response $response['error'] = false; $response['message'] = 'Name saved successfully'; }else{ //if not making failure response $response['error'] = true; $response['message'] = 'Please try later'; } }else{ $response['error'] = true; $response['message'] = "Invalid request"; } //displaying the data in json format echo json_encode($response); |
Testing Script
- Now its time to test the script we created. So in my case the URL is http://localhost/SqliteSync/saveName.php
- I am using POSTMAN to test the script and you can see it in below screenshot.
- As you can see the script is working fine. Now lets move ahead in android project.
Android Sync SQLite Database with Server
Creating Android Project
- Create a new project.
- I have created AndroidMySQLSync with an Empty Activity.
Adding Permissions
- We need the following permissions so first add these to AndroidManifest.xml.
1 2 3 4 | <uses-permission android:name="android.permission.INTERNET" /> <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" /> |
Adding Dependencies
- For network requests I am going to use Volley. So add the following line inside dependencies block of your app level build.gradle file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | dependencies { compile fileTree(dir: 'libs', include: ['*.jar']) androidTestCompile('com.android.support.test.espresso:espresso-core:2.2.2', { exclude group: 'com.android.support', module: 'support-annotations' }) compile 'com.android.support:appcompat-v7:25.0.1' //add this line compile 'com.android.volley:volley:1.0.0' testCompile 'junit:junit:4.12' } |
Handling SQLite Operations
- In this case we have to use both SQLite and MySQL. So a class named DatabaseHelper.java and write the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | package net.simplifiedcoding.androidmysqlsync; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; /** * Created by Belal on 1/27/2017. */ public class DatabaseHelper extends SQLiteOpenHelper { //Constants for Database name, table name, and column names public static final String DB_NAME = "NamesDB"; public static final String TABLE_NAME = "names"; public static final String COLUMN_ID = "id"; public static final String COLUMN_NAME = "name"; public static final String COLUMN_STATUS = "status"; //database version private static final int DB_VERSION = 1; //Constructor public DatabaseHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); } //creating the database @Override public void onCreate(SQLiteDatabase db) { String sql = "CREATE TABLE " + TABLE_NAME + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_NAME + " VARCHAR, " + COLUMN_STATUS + " TINYINT);"; db.execSQL(sql); } //upgrading the database @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { String sql = "DROP TABLE IF EXISTS Persons"; db.execSQL(sql); onCreate(db); } /* * This method is taking two arguments * first one is the name that is to be saved * second one is the status * 0 means the name is synced with the server * 1 means the name is not synced with the server * */ public boolean addName(String name, int status) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues contentValues = new ContentValues(); contentValues.put(COLUMN_NAME, name); contentValues.put(COLUMN_STATUS, status); db.insert(TABLE_NAME, null, contentValues); db.close(); return true; } /* * This method taking two arguments * first one is the id of the name for which * we have to update the sync status * and the second one is the status that will be changed * */ public boolean updateNameStatus(int id, int status) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues contentValues = new ContentValues(); contentValues.put(COLUMN_STATUS, status); db.update(TABLE_NAME, contentValues, COLUMN_ID + "=" + id, null); db.close(); return true; } /* * this method will give us all the name stored in sqlite * */ public Cursor getNames() { SQLiteDatabase db = this.getReadableDatabase(); String sql = "SELECT * FROM " + TABLE_NAME + " ORDER BY " + COLUMN_ID + " ASC;"; Cursor c = db.rawQuery(sql, null); return c; } /* * this method is for getting all the unsynced name * so that we can sync it with database * */ public Cursor getUnsyncedNames() { SQLiteDatabase db = this.getReadableDatabase(); String sql = "SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_STATUS + " = 0;"; Cursor c = db.rawQuery(sql, null); return c; } } |
Handling Volley RequestQueue
- We are going to use Volley for http request. So for this we will create a singleton class.
- Create a class named VolleySingleton and write the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | package net.simplifiedcoding.androidmysqlsync; import android.content.Context; import android.graphics.Bitmap; import android.support.v4.util.LruCache; import com.android.volley.Request; import com.android.volley.RequestQueue; import com.android.volley.toolbox.ImageLoader; import com.android.volley.toolbox.Volley; /** * Created by Belal on 21/09/16. */ public class VolleySingleton { private static VolleySingleton mInstance; private RequestQueue mRequestQueue; private static Context mCtx; private VolleySingleton(Context context) { mCtx = context; mRequestQueue = getRequestQueue(); } public static synchronized VolleySingleton getInstance(Context context) { if (mInstance == null) { mInstance = new VolleySingleton(context); } return mInstance; } public RequestQueue getRequestQueue() { if (mRequestQueue == null) { // getApplicationContext() is key, it keeps you from leaking the // Activity or BroadcastReceiver if someone passes one in. mRequestQueue = Volley.newRequestQueue(mCtx.getApplicationContext()); } return mRequestQueue; } public <T> void addToRequestQueue(Request<T> req) { getRequestQueue().add(req); } } |
Building Interface
MainActivity
- Now inside activity_main.xml write the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | <?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:id="@+id/activity_main" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" android:paddingBottom="@dimen/activity_vertical_margin" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" tools:context="net.simplifiedcoding.androidmysqlsync.MainActivity"> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="horizontal"> <EditText android:id="@+id/editTextName" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_weight="3" android:hint="Enter a name" /> <Button android:id="@+id/buttonSave" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_weight="1" android:text="Save" /> </LinearLayout> <ListView android:id="@+id/listViewNames" android:layout_width="match_parent" android:layout_height="wrap_content"></ListView> </LinearLayout> |
- The above code will generate the following output.
- As you can see we have an EditText, a Button and a ListView.
- Now let me tell you what we are going to do. We will save the Name from EditText and we will also display the saved name in ListView with the Sync Status. So the next part is designing a layout for our Custom ListView.
ListView
- Create an xml file inside layout directory. I have created names.xml.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | <?xml version="1.0" encoding="utf-8"?> <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:padding="@dimen/activity_horizontal_margin" android:layout_width="match_parent" android:layout_height="match_parent"> <TextView android:text="Name" android:layout_alignParentLeft="true" android:id="@+id/textViewName" android:layout_width="wrap_content" android:layout_height="wrap_content" /> <ImageView android:background="@drawable/success" android:layout_alignParentRight="true" android:id="@+id/imageViewStatus" android:layout_width="wrap_content" android:layout_height="wrap_content" /> </RelativeLayout> |
- As you can see we have a TextView to display the name and an ImageView to display the status.
- Download the icons from the below link, we have two images to display queued or synced.
[download id=”4043″]
- You have to copy the downloaded icons inside drawable folder.
Building ListView
Model Class
- Now create a class Name.java and write the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | package net.simplifiedcoding.androidmysqlsync; /** * Created by Belal on 1/27/2017. */ public class Name { private String name; private int status; public Name(String name, int status) { this.name = name; this.status = status; } public String getName() { return name; } public int getStatus() { return status; } } |
- Now we will create an Adapter for our ListView.
Adapter
- Create a class NameAdapter.java and write the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | package net.simplifiedcoding.androidmysqlsync; import android.content.Context; import android.view.LayoutInflater; import android.view.View; import android.view.ViewGroup; import android.widget.ArrayAdapter; import android.widget.ImageView; import android.widget.TextView; import java.util.List; /** * Created by Belal on 1/27/2017. */ public class NameAdapter extends ArrayAdapter<Name> { //storing all the names in the list private List<Name> names; //context object private Context context; //constructor public NameAdapter(Context context, int resource, List<Name> names) { super(context, resource, names); this.context = context; this.names = names; } @Override public View getView(int position, View convertView, ViewGroup parent) { //getting the layoutinflater LayoutInflater inflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE); //getting listview itmes View listViewItem = inflater.inflate(R.layout.names, null, true); TextView textViewName = (TextView) listViewItem.findViewById(R.id.textViewName); ImageView imageViewStatus = (ImageView) listViewItem.findViewById(R.id.imageViewStatus); //getting the current name Name name = names.get(position); //setting the name to textview textViewName.setText(name.getName()); //if the synced status is 0 displaying //queued icon //else displaying synced icon if (name.getStatus() == 0) imageViewStatus.setBackgroundResource(R.drawable.stopwatch); else imageViewStatus.setBackgroundResource(R.drawable.success); return listViewItem; } } |
Coding MainActivity
- Now lets come inside MainActivity.java and write the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 | package net.simplifiedcoding.androidmysqlsync; import android.Manifest; import android.app.ProgressDialog; import android.content.BroadcastReceiver; import android.content.Context; import android.content.Intent; import android.content.IntentFilter; import android.content.pm.PackageManager; import android.database.Cursor; import android.net.ConnectivityManager; import android.support.v4.content.ContextCompat; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.ListView; import android.widget.Toast; import com.android.volley.AuthFailureError; import com.android.volley.Request; import com.android.volley.RequestQueue; import com.android.volley.Response; import com.android.volley.VolleyError; import com.android.volley.toolbox.StringRequest; import com.android.volley.toolbox.Volley; import org.json.JSONException; import org.json.JSONObject; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class MainActivity extends AppCompatActivity implements View.OnClickListener { /* * this is the url to our webservice * make sure you are using the ip instead of localhost * it will not work if you are using localhost * */ public static final String URL_SAVE_NAME = "http://192.168.1.107/SqliteSync/saveName.php"; //database helper object private DatabaseHelper db; //View objects private Button buttonSave; private EditText editTextName; private ListView listViewNames; //List to store all the names private List<Name> names; //1 means data is synced and 0 means data is not synced public static final int NAME_SYNCED_WITH_SERVER = 1; public static final int NAME_NOT_SYNCED_WITH_SERVER = 0; //a broadcast to know weather the data is synced or not public static final String DATA_SAVED_BROADCAST = "net.simplifiedcoding.datasaved"; //Broadcast receiver to know the sync status private BroadcastReceiver broadcastReceiver; //adapterobject for list view private NameAdapter nameAdapter; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); //initializing views and objects db = new DatabaseHelper(this); names = new ArrayList<>(); buttonSave = (Button) findViewById(R.id.buttonSave); editTextName = (EditText) findViewById(R.id.editTextName); listViewNames = (ListView) findViewById(R.id.listViewNames); //adding click listener to button buttonSave.setOnClickListener(this); //calling the method to load all the stored names loadNames(); //the broadcast receiver to update sync status broadcastReceiver = new BroadcastReceiver() { @Override public void onReceive(Context context, Intent intent) { //loading the names again loadNames(); } }; //registering the broadcast receiver to update sync status registerReceiver(broadcastReceiver, new IntentFilter(DATA_SAVED_BROADCAST)); } /* * this method will * load the names from the database * with updated sync status * */ private void loadNames() { names.clear(); Cursor cursor = db.getNames(); if (cursor.moveToFirst()) { do { Name name = new Name( cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_NAME)), cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_STATUS)) ); names.add(name); } while (cursor.moveToNext()); } nameAdapter = new NameAdapter(this, R.layout.names, names); listViewNames.setAdapter(nameAdapter); } /* * this method will simply refresh the list * */ private void refreshList() { nameAdapter.notifyDataSetChanged(); } /* * this method is saving the name to ther server * */ private void saveNameToServer() { final ProgressDialog progressDialog = new ProgressDialog(this); progressDialog.setMessage("Saving Name..."); progressDialog.show(); final String name = editTextName.getText().toString().trim(); StringRequest stringRequest = new StringRequest(Request.Method.POST, URL_SAVE_NAME, new Response.Listener<String>() { @Override public void onResponse(String response) { progressDialog.dismiss(); try { JSONObject obj = new JSONObject(response); if (!obj.getBoolean("error")) { //if there is a success //storing the name to sqlite with status synced saveNameToLocalStorage(name, NAME_SYNCED_WITH_SERVER); } else { //if there is some error //saving the name to sqlite with status unsynced saveNameToLocalStorage(name, NAME_NOT_SYNCED_WITH_SERVER); } } catch (JSONException e) { e.printStackTrace(); } } }, new Response.ErrorListener() { @Override public void onErrorResponse(VolleyError error) { progressDialog.dismiss(); //on error storing the name to sqlite with status unsynced saveNameToLocalStorage(name, NAME_NOT_SYNCED_WITH_SERVER); } }) { @Override protected Map<String, String> getParams() throws AuthFailureError { Map<String, String> params = new HashMap<>(); params.put("name", name); return params; } }; VolleySingleton.getInstance(this).addToRequestQueue(stringRequest); } //saving the name to local storage private void saveNameToLocalStorage(String name, int status) { editTextName.setText(""); db.addName(name, status); Name n = new Name(name, status); names.add(n); refreshList(); } @Override public void onClick(View view) { saveNameToServer(); } } |
- Now if you will run the application it will save the name to MySQL and SQLite with the sync or unsynced status.
- But to send the unsynced names to the server automatically we have to detect the Network Status of the phone. For this we need one more broadcast receiver.
Detecting Network State
Creating Broadcast Receiver
- Create a class named NetworkStateChecker.java and write the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | package net.simplifiedcoding.androidmysqlsync; import android.content.BroadcastReceiver; import android.content.Context; import android.content.Intent; import android.database.Cursor; import android.net.ConnectivityManager; import android.net.NetworkInfo; import com.android.volley.AuthFailureError; import com.android.volley.Request; import com.android.volley.Response; import com.android.volley.VolleyError; import com.android.volley.toolbox.StringRequest; import org.json.JSONException; import org.json.JSONObject; import java.util.HashMap; import java.util.Map; /** * Created by Belal on 1/27/2017. */ public class NetworkStateChecker extends BroadcastReceiver { //context and database helper object private Context context; private DatabaseHelper db; @Override public void onReceive(Context context, Intent intent) { this.context = context; db = new DatabaseHelper(context); ConnectivityManager cm = (ConnectivityManager) context.getSystemService(Context.CONNECTIVITY_SERVICE); NetworkInfo activeNetwork = cm.getActiveNetworkInfo(); //if there is a network if (activeNetwork != null) { //if connected to wifi or mobile data plan if (activeNetwork.getType() == ConnectivityManager.TYPE_WIFI || activeNetwork.getType() == ConnectivityManager.TYPE_MOBILE) { //getting all the unsynced names Cursor cursor = db.getUnsyncedNames(); if (cursor.moveToFirst()) { do { //calling the method to save the unsynced name to MySQL saveName( cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_ID)), cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_NAME)) ); } while (cursor.moveToNext()); } } } } /* * method taking two arguments * name that is to be saved and id of the name from SQLite * if the name is successfully sent * we will update the status as synced in SQLite * */ private void saveName(final int id, final String name) { StringRequest stringRequest = new StringRequest(Request.Method.POST, MainActivity.URL_SAVE_NAME, new Response.Listener<String>() { @Override public void onResponse(String response) { try { JSONObject obj = new JSONObject(response); if (!obj.getBoolean("error")) { //updating the status in sqlite db.updateNameStatus(id, MainActivity.NAME_SYNCED_WITH_SERVER); //sending the broadcast to refresh the list context.sendBroadcast(new Intent(MainActivity.DATA_SAVED_BROADCAST)); } } catch (JSONException e) { e.printStackTrace(); } } }, new Response.ErrorListener() { @Override public void onErrorResponse(VolleyError error) { } }) { @Override protected Map<String, String> getParams() throws AuthFailureError { Map<String, String> params = new HashMap<>(); params.put("name", name); return params; } }; VolleySingleton.getInstance(context).addToRequestQueue(stringRequest); } } |
Adding Receiver in Manifest
- Add the following code in your AndroidManifest.xml file inside application tag.
1 2 3 4 5 6 7 | <receiver android:name=".NetworkStateChecker"> <intent-filter> <action android:name="android.net.conn.CONNECTIVITY_CHANGE" /> </intent-filter> </receiver> |
Registering Receiver
- You also need to register the receiver. So add the following line inside onCreate() method of your MainActivity.java file.
1 2 3 | registerReceiver(new NetworkStateChecker(), new IntentFilter(ConnectivityManager.CONNECTIVITY_ACTION)); |
- Now you can run your application.
Testing the Application
- Run your application. And try saving the name when internet is available also turn off the internet and again try saving your name.
Android Sync SQLite Database with Server - When the internet will be available again the data will be automatically sent to MySQL.
Download Source Code
- You can get the source code from the following GitHub repository.
So thats it for this android sync sqlite database with server tutorial friends. Feel free to leave your comments if you are having any troubles making the project. Also follow the steps carefully as the post is very long. And if you found this helpful please favor us by sharing this post in your social network. Thank You 🙂
Hi, my name is Belal Khan and I am a Google Developers Expert (GDE) for Android. The passion of teaching made me create this blog. If you are an Android Developer, or you are learning about Android Development, then I can help you a lot with Simplified Coding.
Great work i m just looking that……………..
Thanks Sir
Hey Brother you Have Worked on this Can you send me your Code on siddharthshingade37@gmail.com
Thanks
This has saved me
Hi there! I tried your code. I was able to launch the app on my ADV (on android studio) and to save data in local (on sqllite) but I was not able to sync data from sqlite to my mysql server. Maybe I wrong something….What should I verify?
I have the same issue. If you find an answer please share.
I also have the same issue. Please share if you find any solution.
Have you changed the URL to your PHP file?
If not, you might change them in app > main > java > MainActivity.java. Refer line 44.
Change to your ip address.
I am still unable to find the problem. Data is getting saved only in sqlite. I have also changed the url to my php file
something goes wtong with the previous comment…
upd:
1. Close code in the savename.php file with tag:
?>
2. Fix following constants according to your hosting params. I.e.:
‘DB_HOST’ , ‘subdomain.domain.ext’ <— url where your db created
'DB_USERNAME' , 'u0100125_admin' <— db user with admin rights
'DB_PASSWORD' , 'mySimpleTestPassw0rd' <— db admin's password
'DB_NAME' , 'u0100125_names_db' prepare(“INSERT INTO t_users (name) VALUES (?)”); ”
where:
“your_table_name” – the name of the table in the DB, which contains column “your_column_name”
in this example table name is “names”, column name is “name”.
Make sure that your MYSQL server can be accessed Remotely, I had the same problem but after fixing that it worked.
Thanks for the code, Its work on my localhost but why can not work when i am upload to cpanel server the data can not update can you tell me why sir??
Thanks Belal for your article, it works like it should. Now I have to understand the code 🙂
Hello thanks for your code, a question how would the synchronization of Mysql to Sqlite?
I have a situation where I want to initialize my app with data from mysql only when the app is installed for the first time. The problem I run into however is that, when using volley in the onCreate() of SQLiteOpenHelper class, the app starts before the network request completes. This results in blank screen for the first time. What can I do to make sure the UI only runs after network request is complete?
Hi! Why is it not working in me? I changed the IP address but still, {“error”:true,”message”:”Invalid request”} keep on appearing. Please someone help me. 🙁
It means you are not making a POST Request from your Android codes. Probably you are rather making GET or other request.
Check to ensure that you can insert records into your table through PHPMyadmin in the same way that the application does. I noticed that the script was trying to insert a new record with only the name field populated. My table constraints would nor allow me to do this until dropped the ID and the STATUS fields. The table constraint was preventing NULL values in the ID field.
PS. In my testing, I could see that the app will not let you save records if the PHP file is not authenticating to the DB or if there are any problems with the SQL statement. This helped me narrow the issue down to the actual database insert.
Hello,
firstly thanks for your good article.
at the moment everything works apart from couple of bugs. i try this app on real device. when there is internet connection it syncs without any trouble. for test purpose i switched off internet and then i added some name as a result it didnt sysnc, so far everything is as expected. then i switched on wifi and it added data to mysql straighaway however it added two time. in that case what cause the double entry?
Same probleme . any solution!!?
Any chance you could assist in modifying your code to allow more information to be added? This is exactly what i have been trying to do, but i need to have something like name, address, phone, email added to the database, but the “sync” screen is fine just displaying a list of names with sync buttons next to it.
Hi,
When I turn off the internet it saves well in sqlite, but when I start it stores me duplicate records in mysql. please help.
Dear Ruvany Valencio,
Did you solve this method.if you solved kindly tell how to avoid duplicate Record in My sql
have you solved this issue?
Is your problem solved
Dear Belal Khan nice tutorial thankyou.
When i am offline(Sqlite) to datas entry and saved in listview successfully .When internet in connection on the values added in two times to mysql how to avoid duplicate.
{“error”:true,”message”:”Please try later”}.i am getting this error.please help
I cant seem to find the Server Source Code? Is it missing from the github repo?
Where is registerReceiver from?
its Greats Job … Bro Lot of Thank u ….
But i have Question how to sync multiple fields i have large table .. with above 50+ columns .
how to solve ?
have you find how to sync multiple fields i have large table, f you solved kindly tell me how .
i have the same problem
have you find the solution, if you solved please tell me how to do it.
have you find the solution, if yes Please let me know. Thank u
i always get this output {“error”:true,”message”:”Invalid request”} when i run the php file in my localhost. Can you help me?
Make sure you are performing a POST request.
I am also getting the same error as Neeraj {“error”:true,”message”:”Invalid request”} .I have used POST request and I am unable to find the problem. Please HELP : (
How add another data in listviews? i tride? but nothing///
hi, how can update data in mysql and Sqlite
Wow show, but how could I send text + image captured from the camera?
Would be cool, a code in github with text sync + sqlite images for mysql.
Great Tutorial. Thanks for sharing.
how to check which api call and sync data from database in android
Hi,
When I turn off the internet it saves well in sqlite, but when I start it stores me duplicate records in mysql. please help.
Hey Belal!
I’m becoming a fan of yours!
Very well explained and easy to follow!
Congratulations!
Greetings from Argentina!
Jose
I m working on an app project task management which has timer for task which should work offline also but m confused which data should be stored offline as there may be many task and projects
Good day.
I have liked your post on facebook and google plus, but the like buttons for facebook or google plus does not work at all.
How do you get the github repository
I checked it is working.. something wrong on your side.. but no worries here is the link for you
https://github.com/probelalkhan/android-sqlite-mysql-sync-example
Please give the link to php file.There is an error of undifined index at line 27 when i copy php code.Please help
Hi,
Can you please upload a tutorials for Synchronize the data of Server and Sqlite Database using Content Provider
yeah, the php script ain’t working for me, returning an error….and I have no idea how to fix that
Great work! Belal Khan numer one forever! Thanks Sir
Hi, Postman show me this:
Fatal error: Call to a member function bind_param() on boolean in
/opt/lampp/htdocs/android/saveName.php on line
33
Can someone help?
$stmt->bind_param(“s”, $name);
here “s” for string
because $name is string type.
Thanks, man it’s working fine.
your style of explaining is very well.
To those wamp user who are getting error in LAN put Require all granted in hhtpd-vhost.conf file
Its awesome..
but i want to sync my 2 column from database so.. can you please help me … what to do …?
Hi. Great tutorial. Have you been able to fix the duplicate entries created after sync with server?
Thanks
Hello Bilal very nice tutorial can you please help in an issue i want to retrieve the name value back to edittext by clicking on any row of listview. can you please provide me a code for listview onitemclicklistner because i am unavle to get the data back. I hope you will respond to my request.
Salam
Good Day,
I have a strange issue, the app is working like intended but there is a bug somewhere.
If I turn on Airplane mode and add 3 – 4 names and switch Airplane mode off, only one name is being synced to the MySQL server, but all other names are being marked as uploaded, if I check the SQLite database on the local device, all of them have been marked with a 1 on the table Status? Any idea on why this would happen?
Like always, your assistance would be highly appreciated.
Kind Regards,
GoRo
Hello can you create a tutorial where we can upload an image too. it will be very helpful
Dear Belal,
Thank you so much for posting this article.Can you please post multiple fields sync from sqlite to MySQL remote server..
I used you article. Its working fine but whenever i am try to sync multiple fields its throwing error please help me..
Just make sure your PHP POST have all these fields as $XXX
Code:
StringRequest stringRequest = new StringRequest(Request.Method.POST, url_create_product,
new Response.Listener() {
@Override
public void onResponse(String ServerResponse) {
// Hiding the progress dialog after all task complete.
progressDialog.dismiss();
String insertSQL = “INSERT INTO GPSPLOTS \n” +
“(outletno, outletname, lati, longi, dserial, GPS, GPSA, ACC, datecap, syncedd)\n” +
“VALUES \n” +
“(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);”;
mDatabase.execSQL(insertSQL, new String[]{outletno, outletname, lati, longi, dserial, GPS, GPSA, ACC, GPSDate,NAME_SYNCED_WITH_SERVER});
finish();
}
protected Map getParams() {
// Creating Map String Params.
Map params = new HashMap();
// Adding All values to Params.
params.put(“OutletNo”, outletno);
params.put(“Latitude”, lati);
params.put(“Longitude”, longi);
params.put(“DeviceSERIAL”, dserial);
params.put(“GPS”, GPS);
params.put(“NEWADDR”, GPSA);
params.put(“ACC”, ACC);
return params;
}
};
// Creating RequestQueue.
RequestQueue requestQueue = Volley.newRequestQueue(MainActivity.this);
// Adding the StringRequest object into requestQueue.
requestQueue.add(stringRequest);
}
The above you should edit according to your needs, below is the full code for your assistance.
Full Code:
private void addGPSCustomer() {
//SQL Import
EditText tb = (EditText) findViewById(R.id.OutletNo);
TextView txtLatitude = (TextView) findViewById(R.id.txtLatitude1);
TextView txtLongitude = (TextView) findViewById(R.id.txtLongitude1);
TextView serial = (TextView) findViewById(R.id.serial);
TextView gcdo = (TextView) findViewById(R.id.geoc);
TextView addressup = (TextView) findViewById(R.id.addressup);
TextView Cutna = (TextView) findViewById(R.id.customernamep);
final String outletno = tb.getText().toString();
final String outletname = Cutna.getText().toString().trim();
final String lati = txtLatitude.getText().toString();
final String longi = txtLongitude.getText().toString();
final String dserial = serial.getText().toString();
final String GPS = gcdo.getText().toString();
final String GPSA = addressup.getText().toString();
final String ACC = accu.getText().toString();
//getting the current time for joining date
Calendar cal = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat(“yyyy-MM-dd hh:mm:ss”);
final String GPSDate = sdf.format(cal.getTime());
//validating the inptus
requestQueue = Volley.newRequestQueue(MainActivity.this);
progressDialog = new ProgressDialog(MainActivity.this);
progressDialog.setMessage(“Please Wait, We are Inserting Your Data on Server”);
progressDialog.show();
// Creating string request with post method.
StringRequest stringRequest = new StringRequest(Request.Method.POST, url_create_product,
new Response.Listener() {
@Override
public void onResponse(String ServerResponse) {
// Hiding the progress dialog after all task complete.
progressDialog.dismiss();
// Showing response message coming from server.
// Toast.makeText(MainActivity.this, ServerResponse, Toast.LENGTH_LONG).show();
String insertSQL = “INSERT INTO GPSPLOTS \n” +
“(outletno, outletname, lati, longi, dserial, GPS, GPSA, ACC, datecap, syncedd)\n” +
“VALUES \n” +
“(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);”;
//using the same method execsql for inserting values
//this time it has two parameters
//first is the sql string and second is the parameters that is to be binded with the query
mDatabase.execSQL(insertSQL, new String[]{outletno, outletname, lati, longi, dserial, GPS, GPSA, ACC, GPSDate,NAME_SYNCED_WITH_SERVER});
// Toast.makeText(MainActivity.this, “- ” + outletno + ” ” + outletname + ” -\n\n”+”Added Successfully”, Toast.LENGTH_LONG).show();
finish();
}
},
new Response.ErrorListener() {
@Override
public void onErrorResponse(VolleyError volleyError) {
// Hiding the progress dialog after all task complete.
progressDialog.dismiss();
// Showing error message if something goes wrong.
// Toast.makeText(MainActivity.this, volleyError.toString(), Toast.LENGTH_LONG).show();
String insertSQL = “INSERT INTO GPSPLOTS \n” +
“(outletno, outletname, lati, longi, dserial, GPS, GPSA, ACC, datecap, syncedd)\n” +
“VALUES \n” +
“(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);”;
//using the same method execsql for inserting values
//this time it has two parameters
//first is the sql string and second is the parameters that is to be binded with the query
mDatabase.execSQL(insertSQL, new String[]{outletno, outletname, lati, longi, dserial, GPS, GPSA, ACC, GPSDate,NAME_NOT_SYNCED_WITH_SERVER});
// Toast.makeText(MainActivity.this, “- ” + outletno + ” ” + outletname + ” -\n\n”+”Added Successfully”, Toast.LENGTH_LONG).show();
finish();
}
}) {
@Override
protected Map getParams() {
// Creating Map String Params.
Map params = new HashMap();
// Adding All values to Params.
params.put(“OutletNo”, outletno);
params.put(“Latitude”, lati);
params.put(“Longitude”, longi);
params.put(“DeviceSERIAL”, dserial);
params.put(“GPS”, GPS);
params.put(“NEWADDR”, GPSA);
params.put(“ACC”, ACC);
return params;
}
};
// Creating RequestQueue.
RequestQueue requestQueue = Volley.newRequestQueue(MainActivity.this);
// Adding the StringRequest object into requestQueue.
requestQueue.add(stringRequest);
}
PHP File to be aligned with your SQL, here is mine:
send me the code to delete the inserted data in the database and the same to reflect the deletetion in the app
got any solution to this?
how to add 2 column ?? i always get error .
how about SQL server? how to implement it?
Great tutorial.
Thanks for sharing.
Can you show how to make bidirectional sync?
thanks again.
Is there any example of how can i Decide on wich Broadcast Receiver to register if I Edit a Name and want to update ther server on MSQL isntead of Inserting?
I have manage to edit but don;t now How to do it with the Broadcast Receiver.
HI, does anyone know how to sync more than one Table?
I Need to find out how to do that,because I have an app that needs to download at least 5 tables when network service is avalible.
I appreciate your advices.
hello. your work is amazing. it works on mine. however. i want to send data from mysql to sqlite. just reverse sequence in yours.
hi, what is public static final String DATA_SAVED_BROADCAST = “net.simplifiedcoding.datasaved”;
in mainactivity for?thanks sir.
What is String DATA_SAVED_BROADCAST = “net.simplifiedcoding.datasaved”?
hai belan khan,
nice tutorial working fine thanks.
Now SQLite to MYSQL Synchronization.
i need MYSQL to SQLite SYNC how to do this kindly post 1 tutorial.
thanks
hai belan khan,
nice tutorial working fine thanks.
Now SQLite to MYSQL Synchronization.
i need MYSQL to SQLite SYNC how to do this kindly post 1 tutorial.
thanks
hello i tried your tutorial and it works as per my requirement…
But can u show me how can i send text + imgae to sqlite and server using volley ? Thanks in advance.. 🙂
hai belan khan,
nice tutorial working fine thanks.
But some mobile Update same entries Duplicate to store my sql how to resolve this issue.
thanks
I am Having the same problem here.The app is sending duplicate to the remote server
I was able to solve the issue of duplicates as below:
@Override
public void onResume() {
super.onResume();
registerReceiver(networkStateReceiver, new IntentFilter(android.net.ConnectivityManager.CONNECTIVITY_ACTION));
}
@Override
public void onPause() {
unregisterReceiver(networkStateReceiver);
super.onPause();
}
@Override
protected void onDestroy() {
super.onDestroy();
if(receiver !=null) {
unregisterReceiver(networkStateReceiver);
}
Read more on this here
https://stackoverflow.com/questions/6169059/android-event-for-internet-connectivity-state-change
I have a set of data to send, but I can only send the next one when the previous response arrives. How can I make this request synchronously?
Hey dude thanks for making this , i use your code in making our thesis and thank you so much.
how to connect my local host xampp server mysql
you have example use retrofit2? from sqlite to post retrofit?
Can you make for update too not just insert?
how to add field in saveName.php? so not just only name. so i can input name, address, number phone etc.
i confused in editing rest api in file saveName.php
hi Sir,
Do you have a tutorial for
How to save listview data with multiple columns in Mysql database with one single click.
plz tell me how the android the truthspy application work
and tell how to create my own spy app
Awesome tutorial
Thank you
It was great tutorial .But in my case I could not get values saved in mysql. Values are being saved only in sqlite .Can anyone help ..PLEASE?
Thanks for the tutorial. Now I’d like yo update the data in mysql database after updating mysqlite database… How can I achieve that?
good tutorial..but when record inserted from mysql, it should automatically updated in listview also.How can I do it…please help
I tried the code and it is working perfectly.But the only problem is it sync the data only when we start the app again after switching on the internet.
Is there any way through which we don’t need to start the app again and when we switch on the data data will e synced automatically to the mysql server.
Hello Belal
First of all thanks for the tutorial. Working absolutely fine .But the problem is that Connectivity change is deprecated from Android 7 or above. So it’s not working in Nougat and above. How to achieve the same functionality for the devices that target Android 7 or above.
I will update this tutorial soon.
when
hi belal this was sensational experiment , can you please update the tutorial with above 7.0 version like above nougut
how to android sync ROOM DB with server MySQL and PHP?
pls make a tutorial on that.
Have you found a tutorial for this ?
Thank for tuttoriel
Thank for this tuttoriel
Is this one way or two way? You say its from sqlite to mysql.
I need Mysql to sqlite sync like google drive sync system laptop folder to drive folder.
if yours failed to upload data to mysql ,this is due to rescurity reasons.
got your project , app, src then create a folder call xml inside src then create a file call network-security-config.xml
past the below inside it.
put here your ip eg 10.0.0.100 or your domain name
then go to androidManifest.xml and past this inside the application tag
android:networkSecurityConfig=”@xml/network_security_config”
sorry for the late comment
Thank you
How about an update with Room db and WorkManager maybe ?