Retrieve Mysql Data Using Where Clauses

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 i

Solution 1:

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

Try this

HttpPosthttppost=newHttpPost("http://192.168.1.7/Android/CRUD/retrieveInformation.php?name="+name+"&month="+month);

Hope this helps :)

Solution 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):

HttpPosthttppost=newHttpPost('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:

URIuri=newURIBuilder()
    .setScheme("http")
    .setHost("192.168.1.7")
    .setPath("/Android/CRUD/retrieveInformation.php")
    .addParameter("name", name)
    .addParameter("month", month)
    .build();
HttpGethttpget=newHttpGet( 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) ordie('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);

?>