从PHP MySQL的特定列获得最大的ID和最小ID [英] Get max ID and min ID from specific column in MySQL php
问题描述
我是新来的 PHP 现在尝试从的MySQL 数据到Android。
I'm new to php and now trying to retrieve the data from MySQL to android.
这是我的 work_details 表
![在这里输入的形象描述](\"http://i.stack.imgur.com/9OeQK.png\")
在 RetrieveTotalHours 的功能，我想找回在分钟ID时间在和最大ID超时从 MySQL的到 PHP 来Android和最终使用下面的code得到总的时间。
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.
假设ID为3，所以我想在 timeIn ，其中 ID = 3 ，超时其中 ID = 25 。这是我到目前为止已经试过。
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";
错误
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
编辑
![在这里输入的形象描述](\"http://i.stack.imgur.com/JBU6y.png\")
String MiNtimeIn = c.getString(Configs.TAG_IN);
String MaXtimeOut=c.getString(Configs.TAG_OUT);
假设ID（TWD）是8，我要在超时得到timeIn 21时52分和1点52分。
MiNtimeIn应显示21:52（ID 3），MaXtimeOut应显示1:52（ID 4）
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)
推荐答案
@约翰，
检查这个错误，我的答案：
Check my answers about this errors:
- <$c$c>How做我解决java.lang.String中不能转换成JSON对象？
-
org.json.JSONException：值＆LT; HTML＆GT;＆LT; HEAD＆GT;＆LT;类型的Java风格.lang.String不能转换为JSONObject的
下面我们该问题：
echo json_encode($res);
您的编码 $水库变量。
$res = mysqli_query($con,$sql);
mysqli_query（）返回 mysqli_result 对象。
这是该结果，你不能连接code 他们到 JSON 。
It is the resultset, you cannot encode them into JSON.
您需要将它第一次提取到 PHP 数组就像你在循环一样。
You need to fetch it to a PHP array first like you did in the loop.
您没有使用该数组为 JSON 编码，那么回声json_en code（$水库）; 是不正确的它应该是回声json_en code（$结果）;
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);
您还可以简化这个code：
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);
此错误是因为你试图解析它作为一个JSONObject：
This error is because you are trying to parse it as a JSONObject:
JSONObject jsonObject = new JSONObject(json);
这应该是一个JSON数组：
It should be a json array:
JSONArray jsonArray = new JSONArray(json);
这篇关于从PHP MySQL的特定列获得最大的ID和最小ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
