How do I insert an array into mysql

[nhtj]

Hello everyone,

 

I need some help here, I got stuck. Im beginner in programming so if someone can explain to me what I am doing wrong since i cant insert my imploded array to the MySQL database. As i understand,  to insert an array of data to MySQL I have to convert the datatype for the array so MySQL can understand the dataformat. There are more ways of doing that. I chose implode and my datatype output looks comma seperated. I then query to the database, but nothing is inserted. Below is my code. Thanks :-)

 

    <?php
    // DB connection
    include "config.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
        );
        
        // Insert, does not work
        $array_data = implode(", ",  $hour_prices);
        $sql = "INSERT INTO elpriser (HourDK, PriceArea, SpotPriceDKK, GridPrice) VALUES('" . $array_data . "');";
        mysqli_query($con, $sql);
        
        //echo "</br>";
        //print_r (array($array_data));
        //echo var_dump($array_data);
    }   
        // Select, works
        $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>";
    }
    ?>

 

[Barand]

How do you inset an array? You don't.

Store each array element in a single record in another table with the id of its parent record.

Example:

We have 3 parents with children

• Laura has children Peter, Paul, Mary
• Scott              Curly, Larry, Mo
• Tom                Jane, John

They would be stored as follows...

TABLE: parent                             TABLE: child
+-------+--------------------+            +-------+------------+---------+
| ID    | Name               |            | ID    | ParentID   | Name    |
+-------+--------------------+            +-------+------------+---------+
|  1    | Laura Norder       |            |  1    |     1      | Peter   |
|  2    | Scott Chegg        |            |  2    |     1      | Paul    |
|  3    | Tom DiCanari       |            |  3    |     1      | Mary    |
+-------+--------------------+            |  4    |     2      | Curly   |
                                          |  5    |     2      | Larry   |
                                          |  6    |     2      | Mo      |
                                          |  7    |     3      | Jane    |
                                          |  8    |     3      | John    |
                                          +-------+------------+---------+

 

[nhtj]

8 hours ago, Barand said:

How do you inset an array? You don't.

Store each array element in a single record in another table with the id of its parent record.

Example:

We have 3 parents with children

• Laura has children Peter, Paul, Mary
• Scott              Curly, Larry, Mo
• Tom                Jane, John

They would be stored as follows...

TABLE: parent                             TABLE: child
+-------+--------------------+            +-------+------------+---------+
| ID    | Name               |            | ID    | ParentID   | Name    |
+-------+--------------------+            +-------+------------+---------+
|  1    | Laura Norder       |            |  1    |     1      | Peter   |
|  2    | Scott Chegg        |            |  2    |     1      | Paul    |
|  3    | Tom DiCanari       |            |  3    |     1      | Mary    |
+-------+--------------------+            |  4    |     2      | Curly   |
                                          |  5    |     2      | Larry   |
                                          |  6    |     2      | Mo      |
                                          |  7    |     3      | Jane    |
                                          |  8    |     3      | John    |
                                          +-------+------------+---------+

Thank you. But i dont understand why i need two tables to insert some comma seperated text  like (boys, girls, man, women).

 

[Barand]

11 hours ago, nhtj said:

But i dont understand why i need two tables to insert some comma seperated text 

You don't. It's all about efficiently accessing that data after you have stored it. With a separate table the data can be indexed. When searching the query uses the indexes.

For instance

Who has a child called "John"?

• With a comma-separated list you have to read every parent record and then search their lists for 'John'.
• With an indexed table you can can go directly to "John" and see who the parent is.

Relational database tables are not like spreadsheet tables. Instead, via a process of normalization (Google it) the data is split into related tables linked via indexes for rapid retrieval.

[Strider64]

Like already stated you don't store an array in a database table, but you can store the individual values as long as the correct key names (index names) in an array matching the tables columns.

Here's an example:

 

function insertData(array $data, $pdo, $table) {
    try {
        /* Initialize an array */
        $attribute_pairs = [];

        /*
         * Set up the query using prepared states with the values of the array matching
         * the corresponding keys in the array
         * and the array keys being the prepared named placeholders.
         */
        $sql = 'INSERT INTO ' . $table . ' (' . implode(", ", array_keys($data)) . ')';
        $sql .= ' VALUES ( :' . implode(', :', array_keys($data)) . ')';

        /*
         * Prepare the Database Table:
         */
        $stmt = $pdo->prepare($sql);

        /*
         * Grab the corresponding values in order to
         * insert them into the table when the script
         * is executed.
         */
        foreach ($data as $key => $value)
        {
            if($key === 'id') { continue; } // Don't include the id: (Normally not needed if it is NEW data)
            $attribute_pairs[] = $value; // Assign it to an array:
        }

        return $stmt->execute($attribute_pairs); // Execute and send boolean true:

    } catch (PDOException $e) {

        if ($e->errorInfo[1] === 1062) {
            return false;
        }

        throw $e;
    } catch (Exception $e) {
        echo 'Caught exception: ', $e->getMessage(), "\n"; // Not for a production server:
    }

    return true;
}

 

Edited February 6, 2023 by Strider64

[Phi11W]

14 hours ago, nhtj said:

But i dont understand why i need two tables to insert some comma seperated text  like (boys, girls, man, women).

If you always, always, always retrieve and use that comma-separated text as a single unit and never, ever, ever query individual elements of it then (and only then) you can store it in a single field.  Treat it like a BLOB and you can store it like a BLOB. 

As soon as you suspect that you might, one day, possibly, want to find those records that have one (or more) of these elements in them, then you absolutely must store the data "properly" and split it out into separate tables, as others have described.  Failing to do so will give you major problems in finding the data, most of them relating to performance as you Table-Scan through millions of records, substring-ing into this field for each and every one of them. 

Remember, relational databases are really, really good at finding little bits of stuff and bolting them together. 
They are generally pretty rubbish at finding big chunks of stuff and pulling them apart. 

Regards, 
   Phill  W.

 

[ginerjm]

Quote

But i dont understand why i need two tables to insert some comma seperated text  like (boys, girls, man, women).

So those values in the string - how do you plan on using them?  Are they just a reference that you will only use to provide some info about the record key that you will display just to help highlight that record?  You cannot use the field when you want to find all of the "women" in your table, nor for any of the other parts of the string.

Modern RDBMS work really fast and make finding the data that is stored very easy to find.  IF IT IS STORED CORRECTLY.  Using a string of various elements that would have to be searched is not the correct way.  You have a link that is tied to that string such as the record key.  You create the 2nd table as a lookup table of all the values that can be related to each record key in the 1st table.  Then you an write a query that pulls in the desired record keys and joins those records to the records in the table 2.  This is the way it is done.  Period.   Between all of the responders to this topic it should be clear to you now that  this is how it is done.

[Barand]

Your SQL query is incorrect. Your current code...

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

... builds a query like this, with all the values in single comma-delimited string ...

INSERT INTO elpriser (HourDK, PriceArea, SpotPriceDKK, GridPrice) VALUES('2023-01-22T23:00:00, DK2, 1103.27002, 0.1397');
                                                                         ^                                            ^ 

This means you have 4 columns into which you are trying to write 1 value. You need to insert 4 values - one for each column. These value should be 2 string values and 2 numeric values all separated by commas. It should look like this ...

INSERT INTO elpriser (HourDK, PriceArea, SpotPriceDKK, GridPrice) VALUES( '2023-01-22T23:00:00', 'DK2', 1103.27, 0.14 )
                                                                          ^                   ^  ^   ^

Change those 2 lines to

        $array_data = vsprintf(" '%s', '%s', %0.2f, %0.2f ", $hour_prices);
        $sql = "INSERT INTO elpriser (HourDK, PriceArea, SpotPriceDKK, GridPrice) VALUES($array_data)";

 

You should, though, be using a prepared statement instead of putting values into the query. If you use PDO (instead of mysqli) it is particularly easy

    $stmt = $pdo->prepare("INSERT INTO elpriser (HourDK, PriceArea, SpotPriceDKK, GridPrice) VALUES(?, ?, ?, ?)");    // prepare the query statement
    
    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
        );

        $stmt->execute($hour_prices);                                                                                 // execute it with the new values
    }