PHP 网络服务 : Get Multiple Records from MySQL and encode it in JSON array

Question

我是使用 MySQL 的 PHP Web 服务的新手。我关注this教程。我使用 phpmyadmin 创建了一张表 -> loan_applications。目前，我在表中有 3 条与 id 1 相关的记录。我想检索所有 3 条记录，并希望将其编码为 json。

我尝试了多种方法并尝试谷歌搜索，但无法获得正确的 json 数组作为响应。这是我的 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);
}
?>  

这是我的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;
    }
}
}
?>   

这是我收到的回复:

{"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"}  

我只得到一条记录，但我需要与 id 1 关联的所有 3 条记录。我尝试了很多，但无法获取。

Answer 1

这里有很多问题

1 - 虽然不确定，但 Currenlty I have 3records in table related to id 1 似乎是不正确的陈述。如果 id 为主键，则 1 个 id 不能有 3 条记录

2 - $stmt->get_result()->fetch_assoc(); 将始终返回一行，要获取多行或行集合，您需要执行以下操作

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        printf ("%s (%s)\n", $row["Name"], $row["CountryCode"]);
    }

    /* free result set */
    $result->free();
}

3 - 从以下代码可以清楚地看出您实际上只发送回一行

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

你应该这样做

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