Android Sync SQLite Database with Server using PHP and MySQL

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.

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.

mysql database

  • 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.

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.

postman

  • 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.

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.

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.

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.

Building Interface

MainActivity

  • Now inside activity_main.xml write the following code.

  • The above code will generate the following output.
Android Sync SQLite Database with Server
Android Sync SQLite Database with Server
  • 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.

  • 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.

  • Now we will create an Adapter for our ListView.

Adapter

  • Create a class NameAdapter.java and write the following code.

Coding MainActivity

  • Now lets come inside MainActivity.java and write the following code.

  • 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.

Adding Receiver in Manifest

  • Add the following code in your AndroidManifest.xml file inside application tag.

Registering Receiver

  • You also need to register the receiver. So add the following line inside onCreate() method of your MainActivity.java file.

  • 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 mysql
    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 🙂

95 thoughts on “Android Sync SQLite Database with Server using PHP and MySQL”

  1. 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?

    Reply
    • 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.

      Reply
    • 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”.

      Reply
  2. 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??

    Reply
  3. 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?

    Reply
  4. 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. 🙁

    Reply
    • It means you are not making a POST Request from your Android codes. Probably you are rather making GET or other request.

      Reply
    • 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.

      Reply
  5. 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?

    Reply
  6. 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.

    Reply
  7. 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.

    Reply
  8. 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.

    Reply
  9. 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 ?

    Reply
  10. i always get this output {“error”:true,”message”:”Invalid request”} when i run the php file in my localhost. Can you help me?

    Reply
  11. 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.

    Reply
  12. Hey Belal!

    I’m becoming a fan of yours!
    Very well explained and easy to follow!
    Congratulations!

    Greetings from Argentina!
    Jose

    Reply
  13. 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

    Reply
  14. 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

    Reply
  15. 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?

    Reply
  16. 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

    Reply
  17. 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

    Reply
  18. 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

    Reply
  19. 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..

    Reply
    • 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();

      }

      Reply
      • 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);

        }

        Reply
        • 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);

          }

          Reply
  20. 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.

    Reply
  21. 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.

    Reply
  22. hello. your work is amazing. it works on mine. however. i want to send data from mysql to sqlite. just reverse sequence in yours.

    Reply
  23. 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

    Reply
  24. 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

    Reply
  25. 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.. 🙂

    Reply
  26. 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

    Reply
  27. 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?

    Reply
  28. 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

    Reply
  29. 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?

    Reply
  30. Thanks for the tutorial. Now I’d like yo update the data in mysql database after updating mysqlite database… How can I achieve that?

    Reply
  31. good tutorial..but when record inserted from mysql, it should automatically updated in listview also.How can I do it…please help

    Reply
  32. 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.

    Reply
  33. 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.

    Reply

Leave a Comment