I'm new to php and now trying to retrieve the data from MySQL to android.
我是php的新手，现在正试图将数据从MySQL检索到android。
This is my work_details table
这是我的work_details表
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.
在RetrieveTotalHours函数中，我想通过php检索从MySQL到android的最小id时间和最大id超时，最后使用下面的代码来获取总小时数。
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.
假设ID为3，所以我想得到timeIn，其中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
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
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)
假设ID（twd）是8，我应该在timeIn中得到21:52，在timeOut得到1:52。 MiNtimeIn应显示21:52（id 3），MaXtimeOut应显示1:52（id 4）
2 个解决方案
#1
1
@John,
@约翰，
Check my answers about this errors:
检查我对这个错误的答案：
How do I solved java.lang.String cannot converted to JSON object?- 我如何解决java.lang.String无法转换为JSON对象？
org.json.JSONException: Value <HTML><HEAD><STYLE of type java.lang.String cannot be converted to JSONObject- org.json.JSONException：值 <类型java.lang.string的style无法转换为jsonobject< li>
Here us the issue:
我们在这里问题：
echo json_encode($res);
You are encoding the $res variable.
您正在编码$ res变量。
$res = mysqli_query($con,$sql);
mysqli_query() returns a mysqli_result object.
mysqli_query（）返回一个mysqli_result对象。
It is the resultset, you cannot encode them into JSON.
它是结果集，您不能将它们编码为JSON。
You need to fetch it to a PHP array first like you did in the loop.
您需要首先将其提取到PHP数组，就像在循环中一样。
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);
您没有将该数组用于JSON编码，因此echo json_encode（$ res）;是不正确的应该是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 jsonObject = new JSONObject(json);
It should be a json array:
它应该是一个json数组：
JSONArray jsonArray = new JSONArray(json);
#2
2
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.
你创建了很多数组，当你在while循环中进行推送时，你在$ result数组中推送数组，然后在你回显时将所有结果放在另一个数组中。而这更复杂。
#1
1
@John,
@约翰，
Check my answers about this errors:
检查我对这个错误的答案：
How do I solved java.lang.String cannot converted to JSON object?- 我如何解决java.lang.String无法转换为JSON对象？
org.json.JSONException: Value <HTML><HEAD><STYLE of type java.lang.String cannot be converted to JSONObject- org.json.JSONException：值 <类型java.lang.string的style无法转换为jsonobject< li>
Here us the issue:
我们在这里问题：
echo json_encode($res);
You are encoding the $res variable.
您正在编码$ res变量。
$res = mysqli_query($con,$sql);
mysqli_query() returns a mysqli_result object.
mysqli_query（）返回一个mysqli_result对象。
It is the resultset, you cannot encode them into JSON.
它是结果集，您不能将它们编码为JSON。
You need to fetch it to a PHP array first like you did in the loop.
您需要首先将其提取到PHP数组，就像在循环中一样。
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);
您没有将该数组用于JSON编码，因此echo json_encode（$ res）;是不正确的应该是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 jsonObject = new JSONObject(json);
It should be a json array:
它应该是一个json数组：
JSONArray jsonArray = new JSONArray(json);
#2
2
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.
你创建了很多数组，当你在while循环中进行推送时，你在$ result数组中推送数组，然后在你回显时将所有结果放在另一个数组中。而这更复杂。