dtcyv3985
dtcyv3985
2016-01-10 15:48

如何在MySQL表中获取最小ID值和最大ID值

已采纳

This is work_details table. Now I want to retrieve the timeIn and timeOut data from MySQL to android.

enter image description here

 public void RetrieveTotalHours( final String ID)
    {
        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);
            //iD = c.getString(Configs.TAG_ID);
            String MiNtimeIn = c.getString(Configs.TAG_IN);
            String MaXtimeOut=c.getString(Configs.TAG_OUT);
            Log.e("A",MiNtimeIn);
            Log.e("S", MaXtimeOut);

            //total.setText(MiNtimeIn);

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

retrieveTotalHours.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('timeIn'=>$row[0],'timeOut'=>$row[1]));
  }

 echo json_encode($result);

mysqli_close($con);

?>

Assume the ID (twd) holds 8, so String MiNtimeIn suppose display 21:52(id 3) and String MaXtimeOut display 1:52 (id 4) but I get

01-10 23:40:09.350  28936-28936/com.example.project.myapplication D/TextLayoutCache﹕ Enable myanmar Zawgyi converter
01-10 23:40:09.420  28936-28936/com.example.project.myapplication W/System.err﹕ org.json.JSONException: Value [{"timeOut":"23:52:00","timeIn":"21:52:00"},{"timeOut":"01:52:00","timeIn":"21:52:00"}] of type org.json.JSONArray cannot be converted to JSONObject
01-10 23:40:09.420  28936-28936/com.example.project.myapplication W/System.err﹕ at org.json.JSON.typeMismatch(JSON.java:111)
01-10 23:40:09.420  28936-28936/com.example.project.myapplication W/System.err﹕ at org.json.JSONObject.<init>(JSONObject.java:159)
01-10 23:40:09.420  28936-28936/com.example.project.myapplication W/System.err﹕ at org.json.JSONObject.<init>(JSONObject.java:172)
01-10 23:40:09.420  28936-28936/com.example.project.myapplication W/System.err﹕ at com.example.project.myapplication.GUI.Edit_WorkDetails.showHours(Edit_WorkDetails.java:248)
01-10 23:40:09.420  28936-28936/com.example.project.myapplication W/System.err﹕ at com.example.project.myapplication.GUI.Edit_WorkDetails.access$000(Edit_WorkDetails.java:46)
01-10 23:40:09.420  28936-28936/com.example.project.myapplication W/System.err﹕ at com.example.project.myapplication.GUI.Edit_WorkDetails$1GetHours.onPostExecute(Edit_WorkDetails.java:232)
01-10 23:40:09.420  28936-28936/com.example.project.myapplication W/System.err﹕ at com.example.project.myapplication.GUI.Edit_WorkDetails$1GetHours.onPostExecute(Edit_WorkDetails.java:220)

How can I retrieve the timeIn from min id and timeOut from max id where twd = 8 ?

Thanks.

Edited

 private void showHours(String json) {
        try {

            JSONArray array;
            for(int n = 0; n < array.length(); n++)
            {
                JSONObject c = array.getJSONObject(0);
                String MiNtimeIn = c.getString(Configs.TAG_IN);
                String MaXtimeOut=c.getString(Configs.TAG_OUT);
                Log.e("A",MiNtimeIn);
                Log.e("S", MaXtimeOut);
            }

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

Edited

enter image description here

Assume twd is 10, I suppose to get 1:50:00 and 6:51:00, but I get

01-11 02:00:09.900    2872-2872/com.example.project.myapplication E/A﹕ 05:51:00
01-11 02:00:09.900    2872-2872/com.example.project.myapplication E/D﹕ 06:51:00

It seems like it retrieves one row data only...

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • douzi8127 douzi8127 5年前

    type org.json.JSONArray cannot be converted to JSONObject

    Means response JSON String contains JSONArray of JSONObject. so convert JSON String to JSONArray then iterate for getting all JSONObjects from it like:

           JSONArray array=new JSONArray(json);
           JSONObject jsonObject = array.getJSONObject(array.length()-1);
           String MiNtimeIn = jsonObject.optString(Configs.TAG_IN);
           String MaXtimeOut=jsonObject.optString(Configs.TAG_OUT);
           Log.e("A",MiNtimeIn);
           Log.e("S", MaXtimeOut);
    
    点赞 评论 复制链接分享