如何在php中插入一个数组循环到MySql数据库

new9mtju 于 2023-02-07 发布在 PHP

关注(0)|答案(2)|浏览(115)

我试图将$hour_prices数组插入到MySQL中，但我不知道如何在循环中使用数组。
据我所知，我将不得不内爆数组，因为MySql不理解数组数据。但没有插入到数据库。
据我所知，我将不得不内爆数组，因为MySql不理解数组数据。但没有插入到数据库。我有它的工作与关联数组虽然。
我的数组如下所示：

<?php
    // Brooker pricelist
    $url1 = 'https://api.energidataservice.dk/dataset/Elspotprices?start=2023-01-22T00%3A00&end=2023-01-23T00%3A00&columns=HourDK%2C%20PriceArea%2C%20SpotPriceDKK&filter=%7B%22PriceArea%22%3A%20%22DK2%22%7D';
    // Grid pricelist
    $url2 = 'https://api.energidataservice.dk/dataset/DatahubPricelist?start=2023-01-22T00%3A00&end=2023-01-23T00%3A00&filter=%7B%22ChargeOwner%22%3A%20%22TREFOR%20El-net%20A%2FS%22%2C%20%22Note%22%3A%20%22Nettarif%20C%20time%22%7D&limit=1&timezone=DK';

    $json1 = file_get_contents($url1);
    $json2 = file_get_contents($url2);
    $dataset_1 = json_decode($json1, true);
    $dataset_2 = json_decode($json2, true);

    for ($hour = 0; $hour < 24; $hour++) {
        $hour_prices = array(
            $dataset_1['records'][$hour]['HourDK'] // HourDK
            , $dataset_1['records'][$hour]['PriceArea'] // PriceArea
            , $dataset_1['records'][$hour]['SpotPriceDKK'] // SpotPriceDKK
            , $dataset_2['records'][0]['Price' . ($hour + 1)] // GridPrice
        );
    echo "</br>";
    print_r(array($hour_prices));

    include "config.php";

    }
    $sql = "INSERT INTO elpriser (HourDK, PriceArea, SpotPriceDKK, GridPrice) values ";
    $sql .= implode(',', $hour_prices);
    mysqli_query($con, $sql);
    
    echo "</br>";
    echo "</br>";
    print_r(array($hour_prices));

    $sql = mysqli_query($con,"SELECT * FROM elpriser");

    while($row = mysqli_fetch_assoc($sql)){
        $HourDK = $row['HourDK'];
        $PriceArea = $row['PriceArea'];
        $SpotPriceDKK = $row['SpotPriceDKK'];
        $GridPrice = $row['GridPrice'];

        echo "Timepris : ".$HourDK.", Region : ".$PriceArea.", Pris elbørs : ".$SpotPriceDKK.", Pris elnet : ".$GridPrice."<br>";
    }
    ?>

php

来源：https://stackoverflow.com/questions/75341877/how-do-you-insert-an-array-loop-imploded-to-mysql-database-in-php

2条答案

按热度按时间

kb5ga3dv1#

使用带绑定参数的预准备语句，然后在循环中为参数绑定到的变量赋值。

$stmt = $con->prepare("INSERT INTO elpriser (HourDK, PriceArea, SpotPriceDKK, GridPrice) values (?, ?, ?, ?)");
$stmt->bind_param("ssff", $hour, $area, $spotprice, $price);

foreach ($hour_prices as $row) {
    list($hour, $area, $spotprice, $price) = $row;
    $stmt->execute();
}

赞(0）回复(0）举报 2023-02-07

ebdffaop2#

正如我所看到的，如果我正确地理解了您的答案，查询应该在$hour循环内执行。
此外，您的查询中还遗漏了括号。
如果include "config.php";包括db配置，则它最好在环路之外

...  
       for ($hour = 0; $hour < 24; $hour++) {
            $hour_prices = array(
                $dataset_1['records'][$hour]['HourDK'] // HourDK
                , $dataset_1['records'][$hour]['PriceArea'] // PriceArea
                , $dataset_1['records'][$hour]['SpotPriceDKK'] // SpotPriceDKK
                , $dataset_2['records'][0]['Price' . ($hour + 1)] // GridPrice
            );
        echo "</br>";
        print_r(array($hour_prices));
    
        include "config.php";

        $sql = "INSERT INTO elpriser (HourDK, PriceArea, SpotPriceDKK, GridPrice) values ";
        $sql .= '("' .implode('","', $hour_prices) . '")';
    
        }

...

赞(0）回复(0）举报 2023-02-07

我来回答

如何在php中插入一个数组循环到MySql数据库

2条答案

相关问题

热门标签

最新问答