I am newbie to PHP webservices using MySQL. I follow this tutorial. I created one table -> loan_applications using phpmyadmin. Currenlty I have 3 records in table related to id 1. I would like to retrive all 3 records and would like to encode it in json.
I tried multiple way and tried googling but unable to get proper json array in response. Here is my get_applications_list.php
<?php
require_once 'include/DB_Functions.php';
$db = new DB_Functions();
// json response array
$response = array();
if (isset($_GET['id'])) {
// receiving the post params
$id = $_GET['id'];
$applications = $db -> getApplicationsList($id);
if ($applications) {
// got applications successfully
$response["status"] = 0;
$response["id"] = $applications["id"];
$response["application_id"] = $applications["application_id"];
$response["requested_amount"] = $applications["requested_amount"];
$response["interest_per_day"] = $applications["interest_per_day"];
$response["gst"] = $applications["gst"];
$response["tenure"] = $applications["tenure"];
$response["processing_fees"] = $applications["processing_fees"];
$response["amount_user_get"] = $applications["amount_user_get"];
$response["amount_user_pay"] = $applications["amount_user_pay"];
$response["application_latitude"] = $applications["application_latitude"];
$response["application_longitude"] = $applications["application_longitude"];
$response["application_status"] = $applications["application_status"];
$response["created_at"] = $applications["created_at"];
$response["updated_at"] = $applications["updated_at"];
$response["message"] = "Applications details fetched successfully";
echo json_encode($response);
} else {
// applications failed to store
$response["status"] = 1;
$response["message"] = "Unknown error occurred in getting details!";
echo json_encode($response);
}
} else {
// receiving the post params
$response["status"] = 2;
$response["message"] = "Required parameters is missing!";
echo json_encode($response);
}
?>
Here is my DB_Functions.php
<?php
class DB_Functions {
private $conn;
// constructor
function __construct() {
require_once 'DB_Connect.php';
// connecting to database
$db = new Db_Connect();
$this->conn = $db->connect();
}
// destructor
function __destruct() {
}
public function getApplicationsList($id){
$stmt = $this->conn->prepare("SELECT * FROM loan_applications WHERE id = ?");
$stmt->bind_param("s", $id);
$stmt->execute();
$applications = $stmt->get_result()->fetch_assoc();
$stmt->close();
if($applications){
return $applications;
}else {
return false;
}
}
}
?>
Here is response which I am getting :
{"status":0,"id":1,"application_id":1,"requested_amount":5000,"interest_per_day":"0.50","gst":18,"tenure":28,"processing_fees":"5.00","amount_user_get":4705,"amount_user_pay":5700,"application_latitude":"9.999999999","application_longitude":"9.999999999","application_status":1,"created_at":"2018-10-10 21:45:17","updated_at":"0000-00-00 00:00:00","message":"Applications details fetched successfully"}
I am getting only one record but i need all 3 record which associated with id 1. I tried lot but unable to get.
So multiple problems here
1 - Although unsure but Currenlty I have 3 records in table related to id 1 seems incorrect statement. If id is primary key, you cannot have 3 records against one id
2 - $stmt->get_result()->fetch_assoc(); will always return one row, to get multiple rows or collection of rows you will need to do it like following
if ($result = $mysqli->query($query)) {
/* fetch associative array */
while ($row = $result->fetch_assoc()) {
printf ("%s (%s)
", $row["Name"], $row["CountryCode"]);
}
/* free result set */
$result->free();
}
3 - Its quite clear from following code that you are actually sending only one row back
if ($applications) {
// got applications successfully
$response["status"] = 0;
$response["id"] = $applications["id"];
$response["application_id"] = $applications["application_id"];
$response["requested_amount"] = $applications["requested_amount"];
$response["interest_per_day"] = $applications["interest_per_day"];
$response["gst"] = $applications["gst"];
$response["tenure"] = $applications["tenure"];
$response["processing_fees"] = $applications["processing_fees"];
$response["amount_user_get"] = $applications["amount_user_get"];
$response["amount_user_pay"] = $applications["amount_user_pay"];
$response["application_latitude"] = $applications["application_latitude"];
$response["application_longitude"] = $applications["application_longitude"];
$response["application_status"] = $applications["application_status"];
$response["created_at"] = $applications["created_at"];
$response["updated_at"] = $applications["updated_at"];
$response["message"] = "Applications details fetched successfully";
echo json_encode($response);
}
You should do it like this
$applications = getAllApplications(); //returns array of applications
$response['applications'] = $applications; // if they keys you want to send and database fields are same you don't need to set them separately
return json_encode($response);