Android - 将数据从 Android SQLite DB 发布到本地主机服务器上的 MySQL DB

Question

我正在做一些作业，我需要在本地主机服务器上启用 SQLite DB 数据到 MySQL DB 的同步。单击按钮时，需要“收集”来自 SQLite 的数据，将其转换为 JSON 并发送到本地主机 MySQL DB 并插入其中。我制作了处理该工作的 Activity，根据学院示例制作了一些 PHP，并在我制作需要存储数据的数据库的地方运行了 wamp 服务器。在我的本地主机上，数据库被命名为Employees_db，其中的表被命名为empees。这是我制作的 android studio 的代码:

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);
}

}

这是我制作并插入 wampserver/www/employes_db (register.php) 的 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}";
?>

当我启动应用程序时，在 SQLite 数据库中插入一行，然后点击同步按钮，然后打开“localhost/employes_db/register.php”，出现“错误”-> 注意:未定义索引:C:\wamp64 中的 id第 9 行的\www\employes_db\register.php <- 其余列(姓名、姓氏、年龄、公司、wtype)也出现相同的错误。有人可以帮忙吗，我的错误在哪里？

Answer 1

undefined index :id表示$_POST['id']不存在。第一个错误是您使用 $_POST 检索数据，而是使用 $_GET。第二个是要使用 $_GET 检索数据，您必须像这样访问您的 URL:localhost/register.php?id=myID。

<小时/>

在您的情况下，您通过 HTTPPost 发送数据，因此只需使用 file_get_contents('php://input') 获取 JSON 字符串，解码 JSON 数据 (json_decode($ myJSONString)) 您正在接收并将其发送到您的第二个数据库。

编辑
如果我正确理解你的问题，你可以这样做:

// 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 脚本:

<?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);
}