本文介绍了使用where子句检索MySQL数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想知道如何使用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数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-26 08:15