如何将多维数组的嵌套数组插入mysql

我有一个多维数组，我想插入到mysql数据库。一切都很好，但是我想要一个更好的解决方案，因为行在嵌套数组后面重复
这是我的json

{
    "results": [
        {
            "id": 48728,
            "name": "MOVIES AT THE PARK @ GIBA GORGE",
            "url": "Some URL",
            "imageUrl": "Some Path",
            "dateCreated": "2018-06-08T09:18:59.717",
            "lastModified": "2018-06-26T14:20:45.0214921",
            "startDate": "2018-07-28T17:00:00",
            "endDate": "2018-07-28T22:00:00",
            "venue": {
                "id": 0,
                "name": "Giba Gorge Mountain Bike Park",
                "addressLine1": "110 Stockville Road",
                "addressLine2": "",
                "latitude": -29.8276051,
                "longitude": 30.781735700000013
            },
            "locality": {
                "levelOne": "South Africa",
                "levelTwo": "KwaZulu-Natal",
                "levelThree": "Clifton Canyon"
            },
            "organiser": {
                "id": 0,
                "name": "Ultra Glow SA ",
                "phone": "0822603351",
                "mobile": "0828927837",
                "facebookUrl": "",
                "twitterHandle": "",
                "hashTag": "UGMOVIES",
                "organiserPageUrl": "some url"
            },
            "categories": [
                {
                    "id": 3,
                    "name": "Film & Media"
                },
                {
                    "id": 12,
                    "name": "Food & Drink"
                }
            ],
            "tickets": [
                {
                    "id": 98655,
                    "name": "ADULT TICKET",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 100,
                    "salesStart": "2018-06-26T11:52:00",
                    "salesEnd": "2018-07-28T17:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                },
                {
                    "id": 98656,
                    "name": "UNDER 12",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 80,
                    "salesStart": "2018-06-26T11:53:00",
                    "salesEnd": "2018-07-28T17:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                }
            ],
            "schedules": [
            ],
            "refundFeePayableBy": 0
        },
        {
            "id": 51681,
            "name": "ULTRA GLOW COLOUR CRUZ @ RIETVLEI ZOO FARM",
            "url": "some url",
            "imageUrl": "some path",
            "dateCreated": "2018-06-26T12:12:07.3",
            "lastModified": "2018-06-28T15:22:24.1579751",
            "startDate": "2018-08-12T10:00:00",
            "endDate": "2018-08-12T14:00:00",
            "venue": {
                "id": 0,
                "name": "Rietvlei Zoo Farm",
                "addressLine1": "101 Swartkoppies Road",
                "addressLine2": "",
                "latitude": -26.3117147,
                "longitude": 28.07989120000002
            },
            "locality": {
                "levelOne": "South Africa",
                "levelTwo": "Gauteng",
                "levelThree": "Johannesburg South"
            },
            "organiser": {
                "id": 0,
                "name": " Ultra Glow South Africa",
                "phone": "0822603351",
                "mobile": "0828927837",
                "facebookUrl": "",
                "twitterHandle": "",
                "hashTag": "",
                "organiserPageUrl": "some url"
            },
            "categories": [
                {
                    "id": 60,
                    "name": "Trail Running"
                },
                {
                    "id": 5,
                    "name": "Sports & Fitness"
                }
            ],
            "tickets": [
                {
                    "id": 98735,
                    "name": "ADULT EARLY BIRD",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 150,
                    "salesStart": "2018-06-26T12:47:00",
                    "salesEnd": "2018-08-12T10:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                },
                {
                    "id": 98736,
                    "name": "UNDER 12 - EARLY BIRD",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 120,
                    "salesStart": "2018-06-26T12:47:00",
                    "salesEnd": "2018-08-12T10:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                }
            ],
            "schedules": [
            ],
            "refundFeePayableBy": 0
        }
    ],
    "pageSize": 10,
    "pages": 1,
    "records": 2,
    "extras": null,
    "message": null,
    "statusCode": 0
}

我已尝试使用以下代码将相关数据插入数据库

<?php
$connect= mysqli_connect("localhost","root","","result");
$jsondata=file_get_contents("result.json");
$json= json_decode($jsondata,true);
$results=$json['results'];
$n= sizeof($results);
for($i=0;$i<$n;$i++){

$row=$results[$i];
foreach($row['tickets'] as $key => $value){

 $sql="INSERT into 
 event(name,url,imageUrl,dateCreated,eventName,addressLine1,addressLine2,ticketNa me,price) 
 VALUES('".$row["name"]."','".$row["url"]."','".$row["imageUrl"]."','".$row["dateCreated"]."','".$row["venue"]["name"]."','".$row["venue"]["addressLine1"]."','".$row["venue"]["addressLine2"]."','".$value["name"]."','".$value["price"]."')";

    mysqli_query($connect,$sql);
    }
}

echo "events data inserted";
?>

这会将相应的数据输入到我的数据库中，但由于嵌套数组的缘故 tickets 用各自的钥匙 name 以及 price 同样的事件被两次发布到我的数据库中 MOVIES AT THE PARK @ GIBA GORGE 价格 100 一排一排 MOVIES AT THE PARK @ GIBA GORGE 价格 80 在另一排。。。我必须显示这些数据在未来作为一个事件名称的一部分，他们的门票类型和价格作为一个表。。。你有没有其他办法，我可以让它更好，而不是有两个相同的事件行？
提前谢谢
对于那些一直建议我不要使用for和foreach循环的人，这是我在删除for循环和一个foreach循环之后的更新代码，它给了我一个错误，即未定义门票下的名称和价格索引

<?php
$connect= mysqli_connect("localhost","root","","result");
$jsondata=file_get_contents("result.json");
$json= json_decode($jsondata,true);
$results=$json['results'];

foreach($results as $key => $result){

    $sql="INSERT into `event(name,url,imageUrl,dateCreated,eventName,addressLine1,addressLine2,ticketName,price) VALUES('".$result["name"]."','".$result["url"]."','".$result["imageUrl"]."','".$result["dateCreated"]."','".$result["venue"]["name"]."','".$result["venue"]["addressLine1"]."','".$result["venue"]["addressLine2"]."','".$result["tickets"]["name"]."','".$result["tickets"]["price"]."')";`
    mysqli_query($connect,$sql);
}

echo "events data inserted";
?>

因此，我使用两个foreach循环进一步更新了代码，一个循环遍历顶级数组“results”，另一个循环遍历嵌套数组“tickets”

<?php
$connect= mysqli_connect("localhost","root","","result");
$jsondata=file_get_contents("result.json");
$json= json_decode($jsondata,true);
$results=$json['results'];

foreach($results as $key => $result){
    foreach($result["tickets"] as $k => $v){
    $sql="INSERT into event(name,url,imageUrl,dateCreated,eventName,addressLine1,addressLine2,ticketName,price) VALUES('".$result["name"]."','".$result["url"]."','".$result["imageUrl"]."','".$result["dateCreated"]."','".$result["venue"]["name"]."','".$result["venue"]["addressLine1"]."','".$result["venue"]["addressLine2"]."','".$v["name"]."','".$v["price"]."')";`
    mysqli_query($connect,$sql);
    }
}

echo "events data inserted";
?>

它遍历嵌套数组，因此我的代码中没有错误。现在唯一的问题是，由于“tickets”数组中有两种票证类型成人和儿童，我的表上有两个事件
因此，根据这里的许多建议，我需要为事件和票证创建两个单独的表。如果有人能告诉我如何连接这两个表，以便能够以html格式显示我的事件信息，票证类型和价格显示在html表标签中，我将不胜感激。提前感谢

发生这种情况的主要原因是看下面的代码：

$row=$results[$i];
foreach($row['tickets'] as $key => $value){

    $sql="INSERT into event(name,url,imageUrl,dateCreated,eventName,addressLine1,addressLine2,ticketNa me,price) VALUES('".$row["name"]."','".$row["url"]."','".$row["imageUrl"]."','".$row["dateCreated]."','".$row["venue"]["name"]."','".$row["venue"]["addressLine1"]."','".$row["venue"]["addressLine2"]."','".$value["name"]."','".$value["price"]."')";

    mysqli_query($connect,$sql);
}

这个 $row 包含一个主条目和 $row['tickets'] 包含2个条目，您将在循环中使用$row main contents来获取票证。这就是为什么你得到2个条目。最好的方法是使用一个foreach循环，而不是使用for和foreach。
编辑：
实际上，我建议您将表规范化，将其分为两个表，一个用于存储事件信息，另一个用于使用票证信息。结构如下：

event(id,name,url,imageUrl,addressLine1,addressLine2,dateCreated);
event_tickets(id,event_id,ticketName,price,dateCreated);

这将帮助您轻松地维护信息。您还可以将代码修改为以下内容：

$sql="INSERT into event(name,url,imageUrl,addressLine1,addressLine,2dateCreated) VALUES ('".$row['name']."','".$row['url']."','".$row['imageUrl']."','".$row['venue']['addressLine1']."','".$row['venue']['addressLine2']."','".date('Y-m-d h:i:s')."');"
mysqli_query($connect, $sql);
$event_id = mysqli_insert_id($connect);

然后可以使用foreach在event\u tickets表中插入票证信息。
希望这有帮助

如何将多维数组的嵌套数组插入mysql

2条答案

相关问题

热门标签

最新问答