Ketan Sethi Ketan Sethi - 3 months ago 22
PHP Question

How to get multiple rows of data from mysql through JSON using Volley

I am trying to get the output of my below mentioned php file into my android app through JSON but i am getting only the first entry in my android app.I tried doing the below steps as well i can but i am able to get only first output but rest of the output is Not visible in my app

<?php
define('HOST','XXXX');
define('USER','XXXX');
define('PASS','XXXX');
define('DB','XXXX');
$con = mysqli_connect(HOST,USER,PASS,DB) or die('Unable to Connect');
if($_SERVER['REQUEST_METHOD']=='GET'){
$customerEmail = $_GET['customerEmail'];
$sql = "SELECT `amount`, `customerEmail`, `CCAvenueOrder_id` FROM ` OrderAborted` WHERE customerEmail='".$customerEmail."'";
$r = mysqli_query($con,$sql);
while($res = mysqli_fetch_array($r))
{
$result = array();
array_push($result,array(
"customerEmail"=>$res['customerEmail'],
"CCAvenueOrder_id"=>$res['CCAvenueOrder_id'],
"amount"=>$res['amount']
)
);
echo json_encode(array("result"=>$result));
}
mysqli_close($con);
}
?>


JSON code

private void getData() {
String id = editTextId.getText().toString().trim();
if (id.equals("")) {
Toast.makeText(this, "Please enter an id", Toast.LENGTH_LONG).show();
return;
}
loading = ProgressDialog.show(this,"Please wait...","Fetching...",false,false);

String url = Constants.DATA_URL+editTextId.getText().toString().trim();

StringRequest stringRequest = new StringRequest(url, new Response.Listener<String>() {
@Override
public void onResponse(String response) {
loading.dismiss();
showJSON(response);
}
},
new Response.ErrorListener() {
@Override
public void onErrorResponse(VolleyError error) {
Toast.makeText(OrderHistory.this,error.getMessage().toString(),Toast.LENGTH_LONG).show();
}
});

RequestQueue requestQueue = Volley.newRequestQueue(this);
requestQueue.add(stringRequest);
}

private void showJSON(String response){
String name="";
String address="";
String vc = "";
try {
JSONObject jsonObject = new JSONObject(response);
JSONArray result = jsonObject.getJSONArray(Constants.JSON_ARRAY);
JSONObject collegeData = result.getJSONObject(0);
name = collegeData.getString(Constants.KEY_NAME);
address = collegeData.getString(Constants.KEY_ADDRESS);
vc = collegeData.getString(Constants.KEY_VC);
} catch (JSONException e) {
e.printStackTrace();
}
textViewResult.setText("Name:\t"+name+"\nAddress:\t" +address+ "\nVice Chancellor:\t"+ vc);
}

Answer

Quite simply dont send the data till you have completed the loop and move the array initialisation outside the loop also

<?php 
    define('HOST','XXXX');
    define('USER','XXXX');
    define('PASS','XXXX');
    define('DB','XXXX'); 
    $con = mysqli_connect(HOST,USER,PASS,DB) or die('Unable to Connect'); 
    if($_SERVER['REQUEST_METHOD']=='GET'){ 
        $customerEmail = $_GET['customerEmail']; 
        $sql = "SELECT `amount`, `customerEmail`, `CCAvenueOrder_id` FROM `OrderAborted` WHERE customerEmail='".$customerEmail."'"; 
        $r = mysqli_query($con,$sql);

        $result = array();
        while($res = mysqli_fetch_array($r))
        { 
            //$result = array();
            $result[] = array(
                            "customerEmail"=>$res['customerEmail'],
                            "CCAvenueOrder_id"=>$res['CCAvenueOrder_id'],
                            "amount"=>$res['amount']
                            );
            //echo json_encode(array("result"=>$result));
        }

        echo json_encode(array("result"=>$result));
        mysqli_close($con); 
    }
?>

You can also simplify the loop as well as you are getting exactly the array you manually build from the resultset, if you use mysqli_fetch_assoc() instead of mysqli_fetch_array()

<?php 
    define('HOST','XXXX');
    define('USER','XXXX');
    define('PASS','XXXX');
    define('DB','XXXX'); 
    $con = mysqli_connect(HOST,USER,PASS,DB) or die('Unable to Connect'); 
    if($_SERVER['REQUEST_METHOD']=='GET'){ 
        $customerEmail = $_GET['customerEmail']; 
        $sql = "SELECT `amount`, `customerEmail`, `CCAvenueOrder_id` FROM `OrderAborted` WHERE customerEmail='".$customerEmail."'"; 
        $r = mysqli_query($con,$sql);

        $result = array();
        while($res = mysqli_fetch_assoc($r))
        { 
            $result[] = $res;
            //echo json_encode(array("result"=>$result));
        }

        echo json_encode(array("result"=>$result));
        mysqli_close($con); 
    }
?>

And even simpify that again if you use mysqli_fetch_all()

<?php 
    define('HOST','XXXX');
    define('USER','XXXX');
    define('PASS','XXXX');
    define('DB','XXXX'); 
    $con = mysqli_connect(HOST,USER,PASS,DB) or die('Unable to Connect'); 
    if($_SERVER['REQUEST_METHOD']=='GET'){ 
        $customerEmail = $_GET['customerEmail']; 
        $sql = "SELECT `amount`, `customerEmail`, `CCAvenueOrder_id` FROM `OrderAborted` WHERE customerEmail='".$customerEmail."'"; 
        $r = mysqli_query($con,$sql);

        $result = mysqli_fetch_all($r, MYSQLI_ASSOC);

        echo json_encode(array("result"=>$result));
    }
?>

And to get rid of the SQL Injection vector

<?php 
    define('HOST','XXXX');
    define('USER','XXXX');
    define('PASS','XXXX');
    define('DB','XXXX'); 
    $con = mysqli_connect(HOST,USER,PASS,DB) or die('Unable to Connect'); 
    if($_SERVER['REQUEST_METHOD']=='GET'){ 
        //$customerEmail = $_GET['customerEmail']; 

        $sql = "SELECT `amount`, `customerEmail`, `CCAvenueOrder_id` 
                FROM `OrderAborted` 
                WHERE customerEmail=?"; 

        $stmt = mysqli_prepare($con,$sql);

        $stmt = mysqli_stmt_bind_param ($stmt, 's', $_GET['customerEmail']);

        $status = mysqli_execute($stmt);

        $r = mysqli_stmt_get_result ($stmt);

        $result = mysqli_fetch_all($r, MYSQLI_ASSOC);

        echo json_encode(array("result"=>$result));
    }
?>