php - 如何将秒表结果(数组)推送到第一个表结果(数组)

Question

rent_property (table name)

id     fullName    propertyName

1       A          House Name1

2       B          House Name2

3       C          House Name3

4       D          House Name4

rent_amenity (table name)

rentamenityId         rentPropertyId       amenityName

1                         1                 Lift
2                         1                 Gym

3                         2                 Power backup
4                         4                 Gym

my sql query

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

my dynamic function

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 查询，我应该得到两条记录，而且我也得到了正确的结果，但我无法达到预期的结果，请参阅下面我将发布当前我得到的结果我的预期结果是什么

Current Out Put

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

My Expected Results

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

在这里，我想根据我的属性(property)从第二个表(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

但是，这将为您的属性(property)中的每个设施返回一行，因此您必须在循环遍历各行时处理每个属性(property)并检索其设施的句柄。

另一种选择是使用单个查询来检索属性，然后使用第二个查询来检索返回的属性的便利设施。