Question 20942 in Mysql Question posted in
The official MySQL documentation can be found here.

How do you insert an array loop imploded to MySql database in php

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%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>";
    }
    ?>
Tags:

2

Answers


  1. 0

    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();
}
    Login or Signup to reply.
  2. 0

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

    …

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search