Retrieve MySQL data using where clauses

Question

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);

?>

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...

score 4 · Answer 1 · answered Jan 01 '16 at 10:15

4

You didn't put name and month in your HTTP request.

Try this

HttpPost httppost = new HttpPost("http://192.168.1.7/Android/CRUD/retrieveInformation.php?name="+name+"&month="+month);

Hope this helps :)

answered Jan 01 '16 at 10:15

Ye Min Htut

2,904
15
28

Do I need to change/add anything in `getData` ? – John Joe Jan 01 '16 at 10:17
I don't think so. It might work if you've changed the URL. – Ye Min Htut Jan 01 '16 at 10:21
1

@John Joe: Ye Min Htut is (correctly!) saying that if you're going to do a `$_GET['name'];` in your PHP ... then you'd darn well better have a parameter `name=xyz` in your URL. The same with "month": both parameters need to be specified in the URL. ALSO: look at Clayton's response. He's saying the same thing. Clayton also (correctly!) points out that you might wish to use `URIBuilder`, and you might use `HttpGet()` instead of "HttpPost()". – paulsm4 Jan 01 '16 at 10:29
Yes. It's correct but you should sanitize your inputs (name and month) which are used as variables in sql query in order to protect sql injection. – Ye Min Htut Jan 01 '16 at 11:06
Thanks, will go to have a look – John Joe Jan 01 '16 at 11:14

Clay · Accepted Answer · 2016-01-01T14:17:34.480

2

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')

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 );

Note that I am using HttpGet in the second example rather than HttpPost

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 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."'";

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);

?>

edited Jan 01 '16 at 14:17

answered Jan 01 '16 at 10:21

Clay

4,700
3
33
49

you would replace where you have the `httppost` variable defined with my second code snippet – Clay Jan 01 '16 at 10:46
Although your PHP appears to be fine, I added some suggestions to your PHP so you can learn how to better use it (basically use mysqli_fetch_assoc instead of mysqli_fetch_array) – Clay Jan 01 '16 at 10:57
Thanks. Can you help me one more thing ? Currently my month is display January. How can I convert the month to 01 ? – John Joe Jan 01 '16 at 11:08
also added on how to sanitize your variables. Ideally, in Android you should convert January to a 01 just for the request. – Clay Jan 01 '16 at 11:10
Because I have insert my data by this format `2016-01-01` and my month is display as `January`..How can I convert to 01? – John Joe Jan 01 '16 at 11:12
I posted a code example where it does the conversion in PHP. I also put it in all the changes/suggestions I mentioned earlier. I don't know how to do it in Android. – Clay Jan 01 '16 at 11:16
Look at the `$months` variable. It remaps each month to the appropriate number. – Clay Jan 01 '16 at 11:17
Thanks..will try it later. Anything will let you know :) – John Joe Jan 01 '16 at 11:18
I use your php code but no data get retrieved. I think is because of the month :/ – John Joe Jan 01 '16 at 13:19
what does the data look like in the table? is the month column an int? or a varchar? – Clay Jan 01 '16 at 14:13
1

The problem has been solved . Actually I have a date column and want to retrieve the month from the date column. – John Joe Jan 01 '16 at 14:16
1

same thing here too http://php.net/manual/en/mysqli.real-escape-string.php - that's another answer where you're giving unclear and misleading information on mysqli escape functions – Funk Forty Niner Jan 01 '16 at 14:16
@JohnJoe I made a mistake with mysqli_escape_string, it needs to include the $con variable. Updated my example. – Clay Jan 01 '16 at 14:18
Is it a must to use `mysqli_escape_string` ? – John Joe Jan 01 '16 at 14:27
@JohnJoe not a requirement but it's a potentially serious problem if you don't - for example, someone could delete your data. Using PHP's PDO library (or other database libraries) makes it easier to deal with. Anyway, the choice is up to you but it would be wise to use it. Also, if someone enters an apostrophe for the name value, it would break your query and return no results (imagine someone's name is O'Malley or something). – Clay Jan 01 '16 at 14:33
Thanks for telling me all this :) – John Joe Jan 01 '16 at 14:34

Retrieve MySQL data using where clauses

2 Answers2

Linked