Scubadiver Scubadiver - 11 months ago 43
PHP Question

Dropping data in foreach loop

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 Source

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');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download