使用where子句检索MySQL数据 [英] Retrieve MySQL data using where clauses
问题描述
我想知道如何使用WHERE子句从MySQL获取数据并最终加载到android listView吗?我想根据名称和月份获取日期， timeIn 和 timeOut .到目前为止，这是我尝试过的.
I'm wonder how to use WHERE clauses to get the data from MySQL and finally load to android listView ? I want to get the date, timeIn and timeOut based on the name and month. This is what I have tried so far.
GetData
public void getData(String name, String month) {
class GetDataJSON extends AsyncTask<String, Void, String> {
@Override
protected String doInBackground(String... params) {
DefaultHttpClient httpclient = new DefaultHttpClient(new BasicHttpParams());
HttpPost httppost = new HttpPost("http://192.168.1.7/Android/CRUD/retrieveInformation.php");
// Depends on your web service
httppost.setHeader("Content-type", "application/json");
InputStream inputStream = null;
String result = null;
try {
HttpResponse response = httpclient.execute(httppost);
HttpEntity entity = response.getEntity();
inputStream = entity.getContent();
// json is UTF-8 by default
BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, "UTF-8"), 8);
StringBuilder sb = new StringBuilder();
String line = null;
while ((line = reader.readLine()) != null)
{
sb.append(line + "\n");
}
result = sb.toString();
} catch (Exception e) {
// Oops
}
finally {
try{if(inputStream != null)inputStream.close();}catch(Exception squish){}
}
return result;
}
@Override
protected void onPostExecute(String result){
myJSON=result;
showList();
}
}
GetDataJSON g = new GetDataJSON();
g.execute();
}
protected void showList(){
try {
JSONObject jsonObj = new JSONObject(myJSON);
information = jsonObj.getJSONArray(Config.TAG_RESULTS);
for(int i=0;i<information.length();i++){
JSONObject c = information.getJSONObject(i);
String date = c.getString(Config.TAG_DATE);
String timeIn = c.getString(Config.TAG_TiME_IN);
String timeOut = c.getString(Config.TAG_TIME_OUT);
HashMap<String,String> info = new HashMap<String,String>();
info.put(Config.TAG_DATE, date);
info.put(Config.TAG_TiME_IN, timeIn);
info.put(Config.TAG_TIME_OUT,timeOut);
infoList.add(info);
}
ListAdapter adapter = new SimpleAdapter(
HomePage.this, infoList, R.layout.retrieve_data,
new String[]{Config.TAG_DATE,Config.TAG_TiME_IN,Config.TAG_TIME_OUT},
new int[]{R.id.date,R.id.timeIn,R.id.timeOut}
);
listView.setAdapter(adapter);
} catch (JSONException e) {
e.printStackTrace();
}
}
retrieveInformation.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');
$name = $_GET['name'];
$month = $_GET['month'];
$sql = "select * from information WHERE name= '". $name."' and month = '".$month."'";
$res = mysqli_query($con,$sql);
$result=array();
while($row=mysqli_fetch_array($res)){
array_push($result,array('id'=>$row[0],'name'=>$row[1],'weather'=>$row[2],'date'=>$row[3],'status'=>$row[4],
'time_in'=>$row[5], 'time_out'=>$row[6]));
}
echo (json_encode(array("result"=>$result)));
mysqli_close($con);
?>
我能够通过以下方式将MySQL中的所有数据检索到android listView中
使用我在GetData中发布的代码.现在我该如何找回
数据基于名称和月份?我找不到任何教程
谷歌...
I'm able to retrieve all the data from MySQL into android listView by
using the code which I have posted in GetData. Now how can I retrieve
data based on name and month ? I can't find any tutorial from
google...
推荐答案
您似乎想将名称/月份作为参数传递给PHP文件. 最简单的方法是在URL字符串中传递参数(简单的方法):
It looks like you want to pass name/month as parameters to your PHP file. The easiest way you could do this is by passing the parameters in the URL string (the easy way):
HttpPost httppost = new HttpPost('http://192.168.1.7/Android/CRUD/retrieveInformation.php?name=SomeName&month=SomeMonth')
然后，名称和月份被视为PHP文件中的$ _GET变量.
Then, name and month are seen as the $_GET variables you have in your PHP file.
但是，由于您希望对代码进行正确的编码，因此您应该执行以下操作:
But, because you want your code to be properly encoded, you would do something like this instead:
URI uri = new URIBuilder()
.setScheme("http")
.setHost("192.168.1.7")
.setPath("/Android/CRUD/retrieveInformation.php")
.addParameter("name", name)
.addParameter("month", month)
.build();
HttpGet httpget = new HttpGet( uri );
请注意，我在第二个示例中使用的是HttpGet，而不是HttpPost
Note that I am using HttpGet in the second example rather than HttpPost
关于您的PHP代码的快速建议，这样您就不必将所有索引重新映射到它们的键名:
A quick suggestion on your PHP code so you don't have to remap all your indexes to their key names:
while($row=mysqli_fetch_assoc($res)){
$result[] = $row;
}
mysqli_fetch_assoc将把数组的键设置为列名.这将发送所有列.如果您不想只发送所有列，也不想只发送7列，则应将选择查询修改为:
mysqli_fetch_assoc will set the key of array to the column name. This will send ALL the columns. If you don't want to send all the columns and only the 7 columns, you should modify your select query to this:
$sql = "select id, name, weather, date, status, time_in, time_out from information WHERE name= '". $name."' and month = '".$month."'";
此外，还要在选择查询之前清理$ name和$ month:
And, also sanitize the $name and $month before your select query:
$name = mysqli_escape_string($name);
$month = mysqli_escape_string($month);
以下是您的代码已更新，以反映所做的修改:
Here is your code updated to reflect the modifications:
<?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');
$name = $_GET['name'];
$month = $_GET['month'];
$months = [
'January' => '01',
'February' => '02',
'March' => '03',
'April' => '04',
'May' => '05',
'June' => '06',
'July' => '07',
'August' => '08',
'September' => '09',
'October' => '10',
'November' => '11',
'December' => '12',
];
if (!isset($months[ $month ])) {
die("Invalid month");
}
$month = $months[ $month ];
$name = mysqli_escape_string($con, $name);
$month = mysqli_escape_string($con, $month);
$sql = "select * from information WHERE name= '". $name."' and month = '".$month."'";
$res = mysqli_query($con,$sql);
$result=array();
while($row=mysqli_fetch_assoc($res)){
$result[] = $row;
}
echo (json_encode(array("result"=>$result)));
mysqli_close($con);
?>
这篇关于使用where子句检索MySQL数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
