mabbs mabbs - 1 month ago 9
JSON Question

Convert JSON array into an insert query string in php

i have a JSON array and i need to convert it into insert query string,
does anyone have idea how to do that?

here's the JSON string

$array = '[
{
"table":"customer",
"data":
[
{
"customerid":1,
"address":"jl. abc no 5"
},
{
"customerid":2,
"address":"jl. kerinci 7"
}
]
},
{
"table":"supplier",
"data":
[
{
"id":123
},
{
"id":234
}
]
}
]
';


then i use json_decode to convert it into an array

$obj = json_decode($array,true);


here's the workaround i've done so far.

foreach($obj as $result){
$table = $result['table'] . "</br>";
$data = $result['data'];
$string = "INSERT INTO $table (";
foreach($data as $subdata ){
foreach($subdata as $key=>$val){
$string .= "$key,";
$string .= ")VALUES (";
$string .= "'$val'" . ",";
}
$string .= ");";
}
echo $string."</br>";
}


but it doesn't work like i want to,

what i need is to convert it into query like

INSERT INTO customer(customerid,address)values('1','jl.abc no 5');
INSERT INTO customer(customerid,address)values('2','jl. kerinci no 7');
INSERT INTO supplier(id)values('123');
INSERT INTO supplier(id)values('234');


thanks in advance.

PS: the array could be more than two arrays or even just one.

Answer

Instead of doing the foreach, I first use for statement to iterate first the array, because your json have a combination of index and key value pair. Here it is:

    $query = "";
    for($i = 0, $ctr = count($obj); $i < $ctr; $i++) {
        for($x = 0, $ctr_data = count($obj[$i]["data"]); $x < $ctr_data; $x++) {
            $query2 = array(); // After loop cleans the array
            $query .= "INSERT INTO "  . $obj[$i]["table"];
            $query .= "(";
                foreach($obj[$i]["data"][$x] as $key => $value) {
                    $query2[] = $key;
                }
                $query .= implode(",", $query2) . ") VALUES";  // glue the commas

                $query2 = array(); // After the first foreach cleans the array

                foreach($obj[$i]["data"][$x] as $key => $value) {
                    $query2[] = "'$value'";
                }
                $query .= "(";
                $query .= implode(",", $query2) . ") <br>"; // glue the commas
        }
    }

    echo $query;

EDIT:

  1. The first loop counts first the number of array, in your case its two.
  2. The second loop counts the array on the key "data".
  3. Prepare an array $query2 = array(); so that you will have a container of the values of the key/values on key "data".
  4. Get the index "table" name.
  5. The third loop is for the key "data" getting its keys.
  6. Store the keys on the $query2 for later use.
  7. Using implode function, all keys are glued with each other with commas.
  8. Prepare again the array $query2 to cleanse the array.
  9. The fourth loop is for the key "data" getting its values.
  10. Repeat number 6.
  11. Repeat number 7.