php - 如何从mysql数据库连接多个表并在android中使用 View 列表显示

Question

嘿!我想知道如何显示mysql数据库中多个表的数据以及如何在android中使用 View 列表显示数据...

不幸的是我的 php 遇到了这个错误:

警告:mysqli_query(): (23000/1052):第 11 行 C:\xampp\htdocs\elba\TRYreportclockIN.php 中的 where 子句中的列“empUsername”不明确

警告:mysqli_fetch_array() 期望参数 1 为 mysqli_result， bool 值在 C:\xampp\htdocs\elba\TRYreportclockIN.php 第 14 行给出{“结果”:[]}

情况是我有两张 table :

-考勤详情

-出勤详情打卡

从表出勤详细信息中，我想要该表中的所有数据，例如:

-atdId

-atdClockIn

-empId

-emp用户名

从表出勤详细时钟输出我只想要数据:

-atdClockOut

这是我的 TRYreportclockIN.php :

<?php 

    $empUsername= $_GET['empUsername'];

require_once('connectDB.php');

    $con = new mysqli($server, $user, $pass, $db);

$sql = "SELECT i.*, o.atdClockOut FROM attendancedetail i join attendancedetailclockout o on i.empUsername=o.empUsername WHERE empUsername='$empUsername' ";
$res=mysqli_query($con,$sql);

$result=array();
while($row=mysqli_fetch_array($res))
{
        array_push($result,array
        ('atdId'=>$row[0],
        'atdClockIn'=>$row[1],
        'empId'=>$row[2],
        'empUsername'=>$row[3],
        'atdClockOut'=>$row[1]));
}

echo json_encode(array("result"=>$result));

mysqli_close($con);
?>

activity_tryreportclock_in.xml

<?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:layout_width="match_parent"
android:layout_height="match_parent"   
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
android:orientation="vertical"
android:paddingBottom="@dimen/activity_vertical_margin"   
tools:context=".TRYreportclockIn">


<ListView
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:id="@+id/listView" />
</LinearLayout>

activity_list_item.xml

<?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"
android:padding="10dp"
android:paddingLeft="10dp"
android:paddingRight="10dp" >


<TextView
    android:id="@+id/atdId"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:paddingBottom="2dip"
    android:paddingTop="6dip"
    android:textStyle="bold" />


<TextView
    android:id="@+id/atdClockIn"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:paddingBottom="2dip"
    android:textStyle="bold"/>

<TextView
    android:id="@+id/atdClockOut"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:paddingBottom="2dip"
    android:textStyle="bold"/>


<TextView
    android:id="@+id/empId"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:gravity="left"
    android:textStyle="bold" />

<TextView
    android:id="@+id/empUsername"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:gravity="left"
    android:textStyle="bold" />

    </LinearLayout>

TRYreportclockIn.java

package com.atiemt.elba_employee;

import android.os.AsyncTask;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.params.BasicHttpParams;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashMap;


public class TRYreportclockIn extends AppCompatActivity {

String myJSON;

private static final String TAG_RESULTS="result";
private static final String TAG_ATDID = "atdId";
private static final String TAG_ATDCLOCKIN ="atdClockIn";
private static final String TAG_ATDCLOCKOUT = "atdClockOut";
private static final String TAG_EMPID ="empId";
private static final String TAG_EMPUSERNAME ="empUsername";

JSONArray peoples = null;

ArrayList<HashMap<String, String>> personList;

ListView list;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_tryreportclock_in);
    list = (ListView) findViewById(R.id.listView);
    personList = new ArrayList<HashMap<String,String>>();
    getData();
}


protected void showList(){
    try {
        JSONObject jsonObj = new JSONObject(myJSON);
        peoples = jsonObj.getJSONArray(TAG_RESULTS);

        for(int i=0;i<peoples.length();i++){
            JSONObject c = peoples.getJSONObject(i);
            String atdId = c.getString(TAG_ATDID);
            String atdClockIn = c.getString(TAG_ATDCLOCKIN);
            String atdClockOut = c.getString(TAG_ATDCLOCKOUT);
            String empId = c.getString(TAG_EMPID);
            String empUsername = c.getString(TAG_EMPUSERNAME);

            HashMap<String,String> persons = new HashMap<String,String>();

            persons.put(TAG_ATDID,atdId);
            persons.put(TAG_ATDCLOCKIN,atdClockIn);
            persons.put(TAG_ATDCLOCKOUT,atdClockOut);
            persons.put(TAG_EMPID,empId);
            persons.put(TAG_EMPUSERNAME,empUsername);

            personList.add(persons);
        }

        ListAdapter adapter = new SimpleAdapter(
                TRYreportclockIn.this, personList, R.layout.activity_list_item,
                new String[]{TAG_ATDID,TAG_ATDCLOCKIN,TAG_ATDCLOCKOUT,TAG_EMPID,TAG_EMPUSERNAME},
                new int[]{R.id.atdId, R.id.atdClockIn, R.id.atdClockOut, R.id.empId, R.id.empUsername}
        );

        list.setAdapter(adapter);

    } catch (JSONException e) {
        e.printStackTrace();
    }

}

public void getData(){
    class GetDataJSON extends AsyncTask<String, Void, String>{

        @Override
        protected String doInBackground(String... params) {
            DefaultHttpClient httpclient = new DefaultHttpClient(new BasicHttpParams());
            HttpPost httppost = new HttpPost("http://192.168.1.22/elba/TRYreportclockIN.php?empUsername=");

            // Depends on your web service
            httppost.setHeader("Content-type", "application/json");

            InputStream inputStream = null;
            String result = null;
            try {
                HttpResponse response = httpclient.execute(httppost);
                HttpEntity entity = response.getEntity();

                inputStream = entity.getContent();
                // json is UTF-8 by default
                BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, "UTF-8"), 8);
                StringBuilder sb = new StringBuilder();

                String line = null;
                while ((line = reader.readLine()) != null)
                {
                    sb.append(line + "\n");
                }
                result = sb.toString();
            } catch (Exception e) {
                // Oops
            }
            finally {
                try{if(inputStream != null)inputStream.close();}catch(Exception squish){}
            }
            return result;
        }

        @Override
        protected void onPostExecute(String result){
            myJSON=result;
            showList();
        }
    }
    GetDataJSON g = new GetDataJSON();
    g.execute();
}


}

我不太确定我在 android studio 和 php 中的编码...

Answer 1

您遇到的问题是，不同的表中有两列具有相同的名称，当您使用联合查询时，MySQL 不知道要使用哪一列。

您必须在 where 子句中使用点符号，就像在联接中一样

SELECT i.*, o.atdClockOut FROM attendancedetail i join attendancedetailclockout o on i.empUsername=o.empUsername WHERE o.empUsername='$empUsername' 

<小时/>

注意:您的代码容易受到 SQL 注入(inject)攻击:您必须在查询中使用用户输入之前验证用户输入，并且应该使用准备好的语句。请参阅How can I prevent SQL injection in PHP?