在foreach循环中删除数据

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.

Answer 1

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');