Android - posting data from Android SQLite DB to MySQL DB on localhost server

Question

I'm doing some assignment, and I need to enable sync of SQLite DB data to MySQL DB on localhost server. On button click, data from SQLite needs to be "gathered", converted to JSON and sent to localhost MySQL DB and inserted there. I made Activity that handles that job, made some PHP according to academy example, and have wamp server running where I made database in which data needs to be stored. On my localhost database is named employes_db , and table within is named employes. Here is the code from android studio which I made:

package com.EmDatabase;

import android.app.Activity;
import android.content.Intent;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Toast;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import java.io.BufferedReader;
import java.io.DataOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DataSyncManager extends Activity {

Database db;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.sync_options);
    db = new Database(this);
    db.getWritableDatabase();
}

public void syncToServer(View v) throws JSONException {
    List<Employe> employes = db.selectAll();
    JSONObject objEmployes = new JSONObject();
    JSONArray employesData = new JSONArray();
    for (Employe employe : employes) {
        int id = employe.getId();
        String name = employe.getName();
        String surname = employe.getSurname();
        int age = employe.getAge();
        String company = employe.getCompany();
        String wtype = employe.getWorktype();
        JSONObject empData = new JSONObject();
        empData.put("id", id);
        empData.put("name", name);
        empData.put("surname", surname);
        empData.put("age", age);
        empData.put("company", company);
        empData.put("worktype", wtype);
        employesData.put(empData);
    }
    objEmployes.put("all_employes", employesData);
    String result = objEmployes.toString();
    System.out.println(result);
    UploadJsonStringTask task = new UploadJsonStringTask();
    task.execute(
            new String[] { "http://10.0.2.2/employes_db/register.php", result}
    );
}

public void syncFromServer(View v) {

}

private class UploadJsonStringTask extends AsyncTask<String, Void, String> {

    @Override
    protected String doInBackground(String... params) {
        String response = "";
        Map<String,String> queries = new HashMap<String, String>(1);
        queries.put("all_employes", params[1]);
        try {
            response += postHttpContent(params[0],queries);
        } catch (IOException e) {
            Log.e("error", e.toString());
        }
        return response;
    }
    @Override
    protected void onPostExecute(String result) {
        Toast.makeText(getApplicationContext(), result, Toast.LENGTH_LONG).show();
    }

    public String postHttpContent(String urlString, Map<String, String> queries) throws IOException {
        String response = "";
        URL url = new URL(urlString);
        HttpURLConnection httpConnection = (HttpURLConnection) url.openConnection();
        httpConnection.setDoInput(true);
        httpConnection.setDoOutput(true);
        httpConnection.setUseCaches(false);
        httpConnection.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");

        String postData = "";
        for (String key : queries.keySet()) {
            postData += "&" + key + "=" + queries.get(key);
        }
        postData = postData.substring(1);
        DataOutputStream postOut = new DataOutputStream(httpConnection.getOutputStream());
        postOut.writeBytes(postData);
        postOut.flush();
        postOut.close();

        int responseCode = httpConnection.getResponseCode();
        if (responseCode == HttpURLConnection.HTTP_OK) {
            String line;
            BufferedReader br = new BufferedReader(new InputStreamReader(httpConnection.getInputStream()));
            while ((line = br.readLine()) != null) {
                response += line;
            }
        } else {
            response = "";
            throw new IOException();
        }
        return response + " *** Uploaded!";
    }
}

public void goBack(View v) {
    Intent in= new Intent(DataSyncManager.this,MainActivity.class);
    startActivity(in);
}

}

And here is the PHP file I made and inserted into wampserver/www/employes_db (register.php):

<?php
$id = 0;
$name = "";
$surname = "";
$age = 0;
$company = "";
$worktype = "";
$conn = new mysqli("localhost","root","","employes_db");
$conn->query("insert into employes values (null,'".$_POST['id']."','".$_POST['name']."','".$_POST['surname']."','".$_POST['age']."','".$_POST['company']."','".$_POST['worktype']."')");
if(!$conn->error) echo "{status:0}"; else echo "{status:-1}";
?>

When I launch app, insert one row in SQLite database, than hit sync button, and I open "localhost/employes_db/register.php" I get "errors" -> Notice: Undefined index: id in C:\wamp64\www\employes_db\register.php on line 9 <- And same error for rest of columns(name,surname,age,company,wtype). May someone help, where is my mistake?

Tyrmos · Accepted Answer · 2017-02-07 14:19:55Z

1

Undefined index: id means $_POST['id'] does not exist. The first mistake is that you use $_POST to retrieve data, use $_GET instead. The second one is that to retrieve the data using $_GET you've to access your URL like this: localhost/register.php?id=myID.

In your case you are sending the data via HTTPPost so just use file_get_contents('php://input') to get your JSON string, decode the JSON data (json_decode($myJSONString)) you are receiving and send it to your second database.

EDIT
If i understood your problem correctly you could do it like this:

// get all employees from your first database and write them into a JSONArray

List<Employe> employes = db.selectAll();
JSONObject objEmployes = new JSONObject();
JSONArray employesData = new JSONArray();
for (Employe employe : employes) {
    int id = employe.getId();
    String name = employe.getName();
    String surname = employe.getSurname();
    int age = employe.getAge();
    String company = employe.getCompany();
    String wtype = employe.getWorktype();
    JSONObject empData = new JSONObject();
    empData.put("id", id);
    empData.put("name", name);
    empData.put("surname", surname);
    empData.put("age", age);
    empData.put("company", company);
    empData.put("worktype", wtype);
    employesData.put(empData);
}

// open connection
URL url = new URL("yourUrl.com/yourPhpScript.php");
url.openConnection();

// send JSONArray to your second database
String dataToSend = employesData.toString();
OutputStream os = urlConnection.getOutputStream();
BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(os, "UTF-8"));
writer.write(dataToSend);
writer.flush();
writer.close();

PHP Script:

<?php
// connect to database
$link = mysqli_connect($hostname, $username, $password);
mysqli_select_db($link, "myDatabase");

// get the JSONArray the app sends
$contents = file_get_contents('php://input');

$jsonArray = json_decode($contents, true);
$jsonCount = count($jsonArray);

for ($i = 0; $i < $jsonCount; $i++) {
    $item = $jsonArray[$i];
    $itemName = utf8_decode($item['name']);
    // parse the other json attributes here like the one above

    $query = "INSERT INTO employees VALUES('$itemName', 'addOtherValuesHere...')";
    mysqli_query($link, $query);
}

edited Feb 7, 2017 at 14:19

answered Feb 7, 2017 at 9:20

Tyrmos

3874 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dimness Over a year ago

I need to use POST insted of GET because I am transfering data from my database, so I won't be sending one, two or three data but much more, and it dependes how many data I insert in database before I hit sync button. Can You explain how to set file_get_contents... in my case, and where, cauze I'm not good with PHP, I wrote only this code for my assignment using example I found in my Academy.

Tyrmos Over a year ago

@Mystiq I added example code, thats how i would solve your problem, hope it helps you and i understood you correctly

Dimness Over a year ago

I used your PHP script, but kept my code from app, because when I put url.openConnection(); it gets red, like no method... I got one step further, but now I get another "warning"...Here it is: Notice: Undefined variable: localhost in C:\wamp64\www\employes_db\register.php on line 3 same for root, and for password...and these warnings aswell: Warning: mysqli_connect(): (HY000/1045): Access denied for user ''@'localhost' (using password: NO) in C:\wamp64\www\employes_db\register.php on line 3 You have any idea about this?

Dimness Over a year ago

and one more warning, couldn't put it in previous comment.. Warning: mysqli_select_db() expects parameter 1 to be mysqli, boolean given in C:\wamp64\www\employes_db\register.php on line 4

Dimness Over a year ago

Can someone tell me how to get data from mysql driver and write them in android using the class I made for sending data to mysql, if that's possible... I want to write whole code in app than deal with PHP.

Collectives™ on Stack Overflow

Android - posting data from Android SQLite DB to MySQL DB on localhost server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related