Get max ID and min ID from specific column in MySQL php

Question

I'm new to php and now trying to retrieve the data from MySQL to android.

This is my work_details table

In RetrieveTotalHours function, I want to retrieve the min id time-in and max id time-out from MySQL to android through php and finally use the code below to get the total hours.

Assume the ID is 3 , so I want to get the timeIn where id=3, timeOut where id=25. This is what I've tried so far.

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

?>

Configs

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

Error

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

Edited

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

Assume the ID(twd) is 8, I should get 21:52 in timeIn and 1:52 in timeOut. MiNtimeIn should display 21:52(id 3), MaXtimeOut should display 1:52(id 4)

Answer 1

replace this section:

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

with:

echo json_encode($res);

you create a lot of arrays, when you make push in while loop you push array in $result array, then when you echo you put all results in another array. and this is more complex.