CODEWITHSUNDEEP
phpsqljson
John Monte
John Monte

Reputation: 27

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

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

Upvotes: 0

Views: 200

Answers (1)

RiggsFolly
RiggsFolly

Reputation: 94662

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

Upvotes: 1

Related Questions