如何将两个数据库表转换为多维JSON文件?

Rawland Hustle

MYSQL中的表格

上图显示了我的MYSQL数据库中的两个表。一张桌子上有设施,另一张桌子上有开放时间。当然,实际上我的数据库中有很多记录,但是结构是相同的。

我想将每个“设施”都变成Google地图上的标记。标记应包含有关设施的一些信息。为此,我需要使用PHP创建数据的JSON表示形式。JSON文件将通过JQUERY提取。最近几天,我一直在苦苦挣扎的问题是将两个表中的数据合并到一个多维JSON文件中,如下所示。

如何合并PHP中的两个数组(或MYSQL中的两个表,如果我应该这样做的话)以合并下面的JSON文件?

    [{
    "id": "1",
    "name": "Facility One",
    "city": "Texas",
    "postalCode": "34928",
    "postalCity": "Texas",
    "streetAdress": "Big Street 82",
    "phone": "555-948 342",
    "website": "www.facilityone.com",
    "lat": "32.329484",
    "lng": "87.027483",
    "openHours": [{
        "weekday": "1",
        "open": "06:00:00",
        "close": "22:00:00"
    }, {
        "weekday": "2",
        "open": "06:00:00",
        "close": "22:00:00"
    }, {
        "weekday": "3",
        "open": "06:00:00",
        "close": "22:00:00"
    }, {
        "weekday": "4",
        "open": "06:00:00",
        "close": "22:00:00"
    }, {
        "weekday": "5",
        "open": "06:00:00",
        "close": "22:00:00"
    }, {
        "weekday": "6",
        "open": "08:00:00",
        "close": "20:00:00"
    }, {
        "weekday": "7",
        "open": "10:00:00",
        "close": "20:00:00"
    }]
}, {
    "id": "2",
    "name": "Facility Two",
    "city": "Chicago",
    "postalCode": "49384",
    "postalCity": "Chicago",
    "streetAdress": "Small Street 48",
    "phone": "555-329 094",
    "website": "www.facilitytwo.com",
    "lat": "59.928384",
    "lng": "95.293875",
    "openHours": [{
        "weekday": "1",
        "open": "07:30:00",
        "close": "22:00:00"
    }, {
        "weekday": "2",
        "open": "07:30:00",
        "close": "22:00:00"
    }, {
        "weekday": "3",
        "open": "07:30:00",
        "close": "22:00:00"
    }, {
        "weekday": "4",
        "open": "07:30:00",
        "close": "22:00:00"
    }, {
        "weekday": "5",
        "open": "07:30:00",
        "close": "22:00:00"
    }, {
        "weekday": "6",
        "open": "09:00:00",
        "close": "20:00:00"
    }, {
        "weekday": "7",
        "open": "10:00:00",
        "close": "18:00:00"
    }]
}]
Rawland Hustle

谢谢你们的建议!我真的很感激他们。这是我追求的解决方案:

$result1=mysqli_query($conn,$sql1);

$MainArray = array();
$Facilities = array();
$OpeningHours = array();

while ($row1=mysqli_fetch_assoc($result1)) {

    $Facilities['FacilityId'] = $row1['FacilityId'];
    $Facilities['FacilityName'] = $row1['FacilityName'];
    $Facilities['FacilityCity'] = $row1['FacilityCity'];
    $Facilities['FacilityPostalCode'] = $row1['FacilityPostalCode'];
    $Facilities['FacilityPostalCity'] = $row1['FacilityPostalCity'];
    $Facilities['FacilityStreetAddress'] = $row1['FacilityStreetAddress'];
    $Facilities['FacilityPhoneNumber'] = $row1['FacilityPhoneNumber'];
    $Facilities['FacilityWebsite'] = $row1['FacilityWebsite'];
    $Facilities['lat'] = $row1['lat'];
    $Facilities['lng'] = $row1['lng'];
    $Facilities['OpeningHours'] = array();

    $sql2 = "SELECT * FROM OpeningHours WHERE OpeningHoursFacility = " .$row1['FacilityId']."";
    $result2=mysqli_query($conn,$sql2);

    while ($row2=mysqli_fetch_assoc($result2)) {

        $OpeningHours['OpeningHoursWeekday'] = $row2['OpeningHoursWeekday'];
        $OpeningHours['OpeningHour'] = $row2['OpeningHour'];
        $OpeningHours['ClosingHour'] = $row2['ClosingHour'];
        array_push($Facilities['OpeningHours'],$OpeningHours);
    }    

    array_push($MainArray,$Facilities);
}

$jsonData = json_encode(array('Facilities' => $MainArray), JSON_PRETTY_PRINT);

echo $jsonData; 

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

weka,如何将数据库mysql表转换为arff文件

如何将firbease的DataSnapshot转换为json文件或如何将Firebase数据库检索为json文件

如何将JSON文件转换为SQLite数据库

如何将数据库表转换为“数据透视表” DataTable?

如何将Excel表转换为关系数据库表设计?

如何将两个数据帧转换为另一个数据帧?

如何将这些数据从SQL数据库表转换为dict?

如何将两个数据拆分为表

如何通过php正确地将多个数据库记录转换为json

如何将数据库数据转换为json字符串

如何将数据库表上的行ID转换为枚举?

Prisma:如何将具有多个 FK 的数据库表转换为 Prisma 模式?

如何将数据库表值转换为Java类值?

如何将两个列表转换为一个数据列表,将一个列表转换为一个数据框?

如何将数据库行转换为结构

C#如何将数据从两个Windows窗体传递到一个数据库?

蟒蛇。如何将sqlite3数据库转换为Excel文件

如何将具有“太多列”的大型 .csv 文件转换为 SQL 数据库

Codeigniter:如何将数据库结果转换为JSON

SQL Server - 如何将 JSON 转换为关系数据库

如何将JSON列表转换为CSV文件中的两个不同列?

如何将多维键值转换为单个数组?

如何将多维数组转换为单个数组

Springbatch从两个数据库表中读取数据并写入XML文件

如何将MSSQL数据库转换为Android的SQLite数据库

从两个数据库表生成嵌套的json数据

R-将数据框转换为两个数据透视表

将数据库转换为JSON

如何在python或R中以编程方式将多个数据库文件转换为csv?