Dropping data in foreach loop

Question

Using this array:

$arr=array(
   array('project','ProjectId','62c1553d'),
   array('project','ProjectName','TEST JSON'),
   array('Vendors','PrimeSpec','Fabspec'),
   array('Vendors','VendorId','dd759c7f'),
   array('Vendors','PrimeSpec','Vendor2'),
   array('Vendors','VendorId','Vendor2ID'),
);

The desired result is:

INSERT INTO project (ProjectId,ProjectName) VALUES (62c1553d,'TEST JSON');
INSERT INTO Vendors (PrimeSpec,VendorId) VALUES ('Fabspec',dd759c7f);
INSERT INTO Vendors (PrimeSpec,VendorId) VALUES ('Vendor2',Vendor2ID);

But I'm losing PrimeSpec, Fabspec in the foreach loop - the output I'm getting is:

INSERT INTO project (ProjectId,ProjectName) VALUES (62c1553d,TEST JSON);
INSERT INTO Vendors (VendorId) VALUES (dd759c7f);

Here is my code:

function array2sql($arr){
    $sql = '';
    $fields = '';
    $values = '';
    $extable = $arr[0][0];
    foreach( $arr as $line ) {
        if ($extable == $line[0]) {
            $fields .= $line[1].',';
            $values .= $line[2].',';
        } else {
            $sql .= 'INSERT INTO ' . $extable . ' (' . rtrim($fields, ',') . ') VALUES (' . rtrim($values, ',') . ');';
            $fields = '';
            $values = '';
            $extable = $line[0];
        }
    }
    $sql .= 'INSERT INTO ' . $extable . ' (' . rtrim($fields, ',') . ') VALUES (' . rtrim($values, ',') . ');';
    echo $sql;
    return $arr;
}

array2sql($arr);

I don't understand why it's dropping the first set of data. Thanks for looking at this.

score 0 · Answer 1 · answered May 07 '16 at 13:03

0

Seems to be resolved by changing the else statement to

    $fields = $line[1].',';
    $values = $line[2].',';

answered May 07 '16 at 13:03

score 0 · Answer 2 · answered May 07 '16 at 13:11

0

Try This

function array2sql($arr){
    $sql = '';
    $newArr = array();
    foreach( $arr as $line ) {
        $newArr[$line[0]][$line[1]] = $line[2];
    }
    foreach($newArr as $tblNam=>$value) {
        $sql .= "INSERT INTO ".$tblNam." (`" . implode('`,`', array_keys($value)) . "`) VALUES ('" . implode("','", array_values($value)) . "') ";
    }
    echo $sql;
}

answered May 07 '16 at 13:11

Mukesh Ram

6,248
4
19
37

I added a clarification to better illustrate the nested arrays; there will be multiple vendors (and other elements) which my original code with the modification I posted seems to handle, albeit not efficiently. – May 07 '16 at 16:10

RomanPerekhrest · Accepted Answer · 2016-05-08T11:45:33.307

Consider the following simplified version of your array2sql function(using array_walk and array_column functions):

function array2sql($arr) {
    $query_data = [];
    $sql = "";

    array_walk($arr, function($v) use(&$query_data) {
        $query_data[$v[0]][$v[1]][] = $v[2];
    });
    foreach ($query_data as $table => $data) {
        $keys = array_keys($data);
        $key_string = implode(",", $keys);
        $count = count($data[$keys[0]]);  // number of values for a certain column
        while ($count--) {
            $value_string = "'". implode("','", array_column($data, $count)). "'";
            $sql .= "INSERT INTO $table($key_string) VALUES($value_string);". PHP_EOL;
        }
    }    
    return $sql;
}

print_r(array2sql($arr));

The output:

INSERT INTO project(ProjectId,ProjectName) VALUES('62c1553d','TEST JSON');
INSERT INTO Vendors(PrimeSpec,VendorId) VALUES('Vendor2','Vendor2ID');
INSERT INTO Vendors(PrimeSpec,VendorId) VALUES('Fabspec','dd759c7f');

score 0 · Answer 4 · answered Aug 17 '17 at 07:16

Because your tables are only receiving two columns of data each, array_chunk() can help to merge and prepare and compose the queries.

$arr=array(
   array('project','ProjectId','62c1553d'),
   array('project','ProjectName','TEST JSON'),
   array('Vendors','PrimeSpec','Fabspec'),
   array('Vendors','VendorId','dd759c7f'),
   array('Vendors','PrimeSpec','Vendor2'),
   array('Vendors','VendorId','Vendor2ID'),
);

// merge and prepare
foreach(array_chunk($arr,2) as $pair){
    if(!isset($queries[$pair[0][0]]['columns'])){
        $merge[$pair[0][0]]['columns']='`'.implode('`,`',array_column($pair,1)).'`';
    }
    $merge[$pair[0][0]]['values'][]="'".implode("','",array_column($pair,2))."'";
}

// compose queries
foreach($merge as $table=>$a){
    $queries[$table]="INSERT INTO $table ({$a['columns']}) VALUES (".implode('),(',$a['values']).")";
}
print_r($queries);
/*
Array(
    [project] => INSERT INTO project (`ProjectId`,`ProjectName`) VALUES ('62c1553d','TEST JSON')
    [Vendors] => INSERT INTO Vendors (`PrimeSpec`,`VendorId`) VALUES ('Fabspec','dd759c7f'),('Vendor2','Vendor2ID')
)
*/
$mysqli->multi_query(implode(';',$queries));
while ($mysqli->next_result()) {;} // flush multi_queries

This method performs no escaping or security measures. If you want to use prepared statements and placeholders, a few modifications will be necessary.

For more details on how to write a full mysqli_multi_query() INSERT block, see this link: Strict Standards: mysqli_next_result() error with mysqli_multi_query

Dropping data in foreach loop

4 Answers4