nested for loops to insert JSON into same row in SQL table in php

Question

I have a json file with 20 arrays(some with arrays inside arrays), I want to insert 3 different arrays into the same row on my table. Since doing each query separate would insert each array into a different row I tried to use nested for loops. The results for my code below as is were 1) only the second for loop values are inserted and the first for loop values are NULL 2) if i added a query at the end of the first for loop I get two rows,the first row with the values from the first for loop inserted and the second row contains the values from my second loop inserted. The values itself dont matter necessarily but my question is how can I insert different arrays of a JSON file into a single row on an sql table.

<?php
error_reporting(E_ALL);
$dir = "--------------------";
$connect = mysqli_connect("-----", "----", "","------");
if (is_dir($dir)) {
    if ($dh = opendir($dir)) {
        foreach(glob("*.json") as $filename) {
            $data = file_get_contents($filename);
            $testing = json_decode($data, true);
            foreach ($testing[0][0] as $Comments) {
                $sql = "INSERT INTO servers_updated (Model)  VALUES ('".$Comments["Model"]."')";

                foreach($testing[3][1] as $row2) {
                    $sql = "INSERT INTO servers_updated (TotalCores)
                              VALUES ('".$row2['/redfish/v1/Systems/1/Processors/1/']['TotalCores']."')";

                    if ($connect->query($sql) === TRUE) {
                        echo "Data entered";
                    } else {
                        echo "Error  entering data: " . $connect->error;
                    }
                }
            }
        }
        $connect->close();
    }
}
?>

It would be helpful for you to provide sample input, the current result based on that input, and the desired result based on that input. — Patrick Q
– Patrick Q, Commented Aug 16, 2018 at 16:13

RiggsFolly · Accepted Answer · 2018-08-16 16:48:05Z

1

You should have only one INSERT if you only want one row for both these pieces of data, and that can use the data from both the foreach loops like this.

You should also be using Prepared and Parameterised statements, that will improve performance in this code as you only need to prepare the query once, therefore is will only be compiled once by the database. You then amend the parameters each time round the loop before doing the insert.

<?php
error_reporting(E_ALL);
$dir = "--------------------";
$connect = mysqli_connect("-----", "----", "","------");
if (is_dir($dir)) {
    if ($dh = opendir($dir)) {

        // only need one query, 
        // and it can be prepared once if done here outside all the loops
        $sql = "INSERT INTO servers_updated (Model, TotalCores) VALUES (?,?)";
        $stmt = $connect->prepare($sql);

        foreach(glob("*.json") as $filename) {
            $data = file_get_contents($filename);
            $testing = json_decode($data, true);

            foreach ($testing[0][0] as $Comments) {

                foreach($testing[3][1] as $row2) {
                    $stmt->bind_param('ss', $Comments["Model"],
                                            $row2['/redfish/v1/Systems/1/Processors/1/']['TotalCores']);

                    if ( $stmt->execute() ){
                        echo "Data entered";
                    } else {
                        echo "Error  entering data: " . $connect->error;
                    }
                }
            }
        }
        $connect->close();
    }
}
?>

answered Aug 16, 2018 at 16:48

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Monte Over a year ago

so i ended up using your thinking, i used each forloop and assigned each value a variable and then inserted the variables as the values, made it much easier!

Collectives™ on Stack Overflow

nested for loops to insert JSON into same row in SQL table in php

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related