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)

@GoudaElalfy I get `W/System.err﹕ org.json.JSONException: Value — John Joe, Jan 10 '16 at 11:13

score 2 · Answer 1 · answered Jan 10 '16 at 11:20

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.

score 1 · Accepted Answer · edited May 23 '17 at 12:15

@John,

Check my answers about this errors:

How do I solved java.lang.String cannot converted to JSON object?
org.json.JSONException: Value <HTML><HEAD><STYLE of type java.lang.String cannot be converted to JSONObject

Here us the issue:

echo json_encode($res);

You are encoding the $res variable.

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

mysqli_query() returns a mysqli_result object.

It is the resultset, you cannot encode them into JSON.

You need to fetch it to a PHP array first like you did in the loop.

You did not use that array for the JSON encoding so echo json_encode($res); is not correct it should have been echo json_encode($result);

You can also simplify this code:

$res = mysqli_query($con,$sql);
$data=array();
while($row=mysqli_fetch_array($res)){
    $data[] = $row;
}
echo json_encode($data);

This error is because you are trying to parse it as a JSONObject:

JSONObject jsonObject = new JSONObject(json);

It should be a json array:

JSONArray jsonArray = new JSONArray(json);

sir can you help ? https://stackoverflow.com/questions/35068372/get-error-when-trying-to-send-new-password-to-e-mail-by-php — John Joe, Jan 28 '16 at 17:54

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

2 Answers2

Linked