如何将秒表结果(数组)推入第一个表结果(数组)

Question

rent_property (表名)

id     fullName    propertyName

1       A          House Name1

2       B          House Name2

3       C          House Name3

4       D          House Name4

rent_amenity (表名)

rentamenityId         rentPropertyId       amenityName

1                         1                 Lift
2                         1                 Gym

3                         2                 Power backup
4                         4                 Gym

我的sql查询

    $sql = "SELECT a.id,a.fullName,a.propertyName FROM rent_property a LEFT JOIN rent_amenity b ON  a.id = b.rentPropertyId WHERE a.city='1' AND a.propertyType IN ( '1','2' ) AND b.amenityName IN ( 'Gym' )  AND a.approveStatus!='Inactive' GROUP BY a.id order by a.id desc";

$result = $this->GetJoinRecord($sql);

我的动态函数

public function GetJoinRecord($query_string){

        $con = $this->DBConnection();
        $query = mysqli_query($con,$query_string);
        if(@mysqli_num_rows($query)>0){

                while($data=mysqli_fetch_assoc($query)){
                    $record[] = $data;
                }
                mysqli_free_result($query);

            }

            mysqli_close($con); 
            return $record;
    }

基于我的表和mysql查询，我应该得到两条记录，而且我得到的是正确的，但是我无法取得预期的结果，请看下面我将张贴我正在得到的结果和预期的结果。

电流输出

 {
"status": "success",
"message": "Data Found.",
"data": {
    "rent_id": [
        {
            "id": "4",
            "fullName": "D",
            "propertyName": "House Name4"
        },
        {
            "id": "1",
            "fullName": "A",
            "propertyName": "House Name1"
        }
    ]
}
}

我的预期结果

{
"status": "success",
"message": "Data Found.",
"data": {
    "rent_id": [
        {
            "id": "4",
            "fullName": "D",
            "propertyName": "House Name4",
             "amenities":[
                    {
                       "rentamenityId":"4",
                       "rentPropertyId":"4",
                       "amenityName":"Gym"
                    }
                 ]
        },
        {
            "id": "1",
            "fullName": "A",
            "propertyName": "House Name1",
            "amenities":[
                    {
                       "rentamenityId":"1",
                       "rentPropertyId":"1",
                       "amenityName":"Lift"
                    },
                    {
                       "rentamenityId":"2",
                       "rentPropertyId":"1",
                       "amenityName":"Gym"
                    }
                 ]
        }
    ]
}
}

在这里，我想根据我的属性从我的第二个table(rent_amenity)中添加设施数组(便民名称)，我不知道如何在第一个数组中推送这些便利设施记录。

Answer 1

您可能希望得到更接近以下内容的东西。

SELECT * 
FROM rent_property a 
LEFT JOIN rent_amenity b ON a.id = b.rentPropertyId 
WHERE a.city='1' AND a.propertyType IN ( '1','2' ) 
/* AND b.amenityName IN ( 'Gym' ) -- Removed to return all properties */
AND a.approveStatus!='Inactive' 
GROUP BY a.id order by a.id desc

然而，这将为您的属性中的每个舒适返回一行，因此您必须在遍历行时处理每个属性并检索处理它的便利设施。

另一种选择是使用单个查询检索属性，然后使用第二个查询检索返回的属性的功能。