Im trying to insert the $hour_prices array in to MySQL. But im not sure how to do that with an array in a loop.

As i understand i will have to implode the array because MySql does not not understand the array data. But nothing is inserted to the database.

As i understand i will have to implode the array because MySql does not not understand the array data. But nothing is inserted to the database. I do have it working with an associative array though.

My arrays looks like this:

Array ( [0] => Array ( [0] => 2023-01-22T23:00:00 [1] => DK2 [2] => 1103.27002 [3] => 0.1397 ) )
Array ( [0] => Array ( [0] => 2023-01-22T22:00:00 [1] => DK2 [2] => 1170.599976 [3] => 0.1397 ) )
Array ( [0] => Array ( [0] => 2023-01-22T21:00:00 [1] => DK2 [2] => 1237.920044 [3] => 0.1397 ) )
Array ( [0] => Array ( [0] => 2023-01-22T20:00:00 [1] => DK2 [2] => 1299.73999 [3] => 0.1397 ) )
Array ( [0] => Array ( [0] => 2023-01-22T19:00:00 [1] => DK2 [2] => 1481.709961 [3] => 0.1397 ) )
Array ( [0] => Array ( [0] => 2023-01-22T18:00:00 [1] => DK2 [2] => 1503.290039 [3] => 0.1397 ) )
Array ( [0] => Array ( [0] => 2023-01-22T17:00:00 [1] => DK2 [2] => 1428.300049 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T16:00:00 [1] => DK2 [2] => 1272.369995 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T15:00:00 [1] => DK2 [2] => 1143.52002 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T14:00:00 [1] => DK2 [2] => 1124.77002 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T13:00:00 [1] => DK2 [2] => 892.580017 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T12:00:00 [1] => DK2 [2] => 807.849976 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T11:00:00 [1] => DK2 [2] => 925.390015 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T10:00:00 [1] => DK2 [2] => 1023.960022 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T09:00:00 [1] => DK2 [2] => 900.099976 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T08:00:00 [1] => DK2 [2] => 639.869995 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T07:00:00 [1] => DK2 [2] => 482.970001 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T06:00:00 [1] => DK2 [2] => 456.929993 [3] => 1.2576 ) )
Array ( [0] => Array ( [0] => 2023-01-22T05:00:00 [1] => DK2 [2] => 465.630005 [3] => 1.2576 ) )
Array ( [0] => Array ( [0] => 2023-01-22T04:00:00 [1] => DK2 [2] => 520.840027 [3] => 1.2576 ) )
Array ( [0] => Array ( [0] => 2023-01-22T03:00:00 [1] => DK2 [2] => 531.549988 [3] => 1.2576 ) )
Array ( [0] => Array ( [0] => 2023-01-22T02:00:00 [1] => DK2 [2] => 543.530029 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T01:00:00 [1] => DK2 [2] => 588.97998 [3] => 0.4192 ) )
Array ( [0] => Array ( [0] => 2023-01-22T00:00:00 [1] => DK2 [2] => 590.690002 [3] => 0.4192 ) )

<?php
    // Brooker pricelist
    $url1 = 'https://api.energidataservice.dk/dataset/Elspotprices?start=2023-01-22T00:00&end=2023-01-23T00:00&columns=HourDK, PriceArea, SpotPriceDKK&filter={"PriceArea": "DK2"}';
    // Grid pricelist
    $url2 = 'https://api.energidataservice.dk/dataset/DatahubPricelist?start=2023-01-22T00:00&end=2023-01-23T00:00&filter={"ChargeOwner": "TREFOR El-net A/S", "Note": "Nettarif C time"}&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>";
    }
    ?>

CodePudding user response：

Use a prepared statement with bound parameters, then assign the variables that the parameters are bound to in a loop.

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

CodePudding user response：

As I can see and If I understand your answer correctly the query should be executed inside the $hour loop.

Also you missed Parentheses on your query.

if include "config.php"; include db configuration it better be outside the loop

...  
       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) . '")';
    
        }

...