java - 从 MySQL php 中的特定列获取最大 ID 和最小 ID

Question

我是 php 的新手现在尝试从 MySQL 检索数据到安卓。

这是我的work_details 表

在RetrieveTotalHours 函数中，我想从MySQL 中检索min id time-in 和ma​​x id time-out通过php到android最后使用下面的代码获取总小时数。

假设 ID 是 3 ，所以我想得到 timeIn where id=3 , timeOut 其中id=25 .到目前为止，这是我尝试过的。

  public void RetrieveTotalHours( final String ID) // ID(twd)=3
    {
        class GetHours extends AsyncTask<Void,Void,String> {
            ProgressDialog loading;
            @Override
            protected void onPreExecute() {
                super.onPreExecute();
                loading = ProgressDialog.show(getActivity(),"Fetching...","Wait...",false,false);
            }

            @Override
            protected void onPostExecute(String s) {
                super.onPostExecute(s);
                loading.dismiss();
                showHours(s);
            }

            @Override
            protected String doInBackground(Void... params) {
                RequestHandler rh = new RequestHandler();
                String s = rh.sendGetRequestParam(Configs.RETRIEVE_HOURS,ID);
                return s;
            }
        }
        GetHours ge = new GetHours();
        ge.execute();

    }
    private void showHours(String json) {
        try {
            JSONObject jsonObject = new JSONObject(json);
            JSONArray result = jsonObject.getJSONArray(Configs.TAG_JSON_ARRAY);
            JSONObject c = result.getJSONObject(0);
            String MiNtimeIn = c.getString(Configs.TAG_IN); 
            String MaXtimeOut=c.getString(Configs.TAG_OUT);

            long difference = 0;
            if (MiNtimeIn > MaXtimeOut) {
            difference = (MaXtimeOut + (24 * 60) - MiNtimeIn) - (1 * 60);
            minutes = (int) (difference % 60);
            hours = (int) ((difference / 60) % (24 * 60));
            totalHours.setText(("Total hours : " + hours + ":" + minutes));

           } else {
            // .....
          }

            total.setText(hours);

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

Retrieve_hours.php

<?php
  define('HOST','127.0.0.1:3307');
  define('USER','root');
  define('PASS','');
  define('DB','androiddb');

  $con = mysqli_connect(HOST,USER,PASS,DB) or die('unable to connect');

  $twd= $_GET['id'];

 $sql = "select timeIn, timeOut from work_details WHERE twd = '".$twd."' AND id IN
 (SELECT MIN(id) FROM work_details WHERE twd ='".$twd."' UNION SELECT MAX(id) FROM work_details WHERE twd='".$twd."')";

  $res = mysqli_query($con,$sql);

  $result=array();


  while($row=mysqli_fetch_array($res)){
      array_push($result,array('id'=>$row[0],'timeIn'=>$row[1],'timeOut'=>$row[2]));
  }

 echo json_encode($res);

mysqli_close($con);

?>

配置

    public static final String TAG_IN="timeIn";
    public static final String TAG_OUT="timeOut";

错误

01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ org.json.JSONException: Value <br of type java.lang.String cannot be converted to JSONObject
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at org.json.JSON.typeMismatch(JSON.java:111)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at org.json.JSONObject.<init>(JSONObject.java:159)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at org.json.JSONObject.<init>(JSONObject.java:172)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at com.example.project.myapplication.GUI.Edit_WorkDetails.showHours(Edit_WorkDetails.java:248)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at com.example.project.myapplication.GUI.Edit_WorkDetails.access$000(Edit_WorkDetails.java:46)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at com.example.project.myapplication.GUI.Edit_WorkDetails$1GetHours.onPostExecute(Edit_WorkDetails.java:232)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at com.example.project.myapplication.GUI.Edit_WorkDetails$1GetHours.onPostExecute(Edit_WorkDetails.java:220)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at android.os.AsyncTask.finish(AsyncTask.java:632)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at android.os.AsyncTask.access$600(AsyncTask.java:177)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:645)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at android.os.Handler.dispatchMessage(Handler.java:102)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at android.os.Looper.loop(Looper.java:146)
01-10 19:31:47.355    1298-1298/com.example.project.myapplication W/System.err﹕ at android.app.ActivityThread.main(ActivityThread.java:5602)
01-10 19:31:47.360    1298-1298/com.example.project.myapplication W/System.err﹕ at java.lang.reflect.Method.invokeNative(Native Method)
01-10 19:31:47.360    1298-1298/com.example.project.myapplication W/System.err﹕ at java.lang.reflect.Method.invoke(Method.java:515)
01-10 19:31:47.360    1298-1298/com.example.project.myapplication W/System.err﹕ at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1283)
01-10 19:31:47.360    1298-1298/com.example.project.myapplication W/System.err﹕ at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1099)
01-10 19:31:47.360    1298-1298/com.example.project.myapplication W/System.err﹕ at dalvik.system.NativeStart.main(Native Method)
01-10 19:31:47.380    1298-1298/com.example.project.myapplication D/AbsListView﹕ Get MotionRecognitionManager

已编辑

 String MiNtimeIn = c.getString(Configs.TAG_IN); 
 String MaXtimeOut=c.getString(Configs.TAG_OUT);

假设 ID(twd) 是 8，我应该得到 21:52 的 timeIn 和 1:52 的 timeOut。MiNtimeIn 应该显示 21:52(id 3)，MaXtimeOut 应该显示 1:52(id 4)

Answer 1

替换此部分:

$result=array();
while($row=mysqli_fetch_array($res)){
    array_push($result,array('id'=>$row[0],'timeIn'=>$row[1],'timeOut'=>$row[2]));
}
echo (json_encode(array("result"=>$result)));

与:

echo json_encode($res);

您创建了很多数组，当您在 while 循环中进行推送时，您将数组推送到 $result 数组中，然后当您回显时，您将所有结果放入另一个数组中。这更复杂。