在foreach循环中删除数据

问题描述：

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.

使用此数组： p>

  $ arr = array（\  n 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'  ），
）; 
  code>  pre> 
 
 所需的结果是： p> 
 
 
  INSERT INTO项目（ProjectId，ProjectName）  VALUES（62c1553d，'TEST JSON'）; 
INSERT INTO供应商（PrimeSpec，VendorId）价值（'Fabspec'，dd759c7f）; 
INSERT INTO供应商（PrimeSpec，VendorId）价值（'Vendor2'，Vendor2ID）; 
  代码>  pre> 
 
 但我在foreach循环中丢失 PrimeSpec  strong>， Fabspec  strong>  - 我得到的输出是：  p> 
 
 
  INSERT INTO项目（ProjectId，ProjectName）VALUES（62c1553d，TEST JSON）; 
INSERT INTO供应商（VendorId）VALUES（dd759c7f）; 
  code>  pre>  
 
 这是我的代码： p> 
 
 
  function arr  ay2sql（$ arr）{
 $ sql =''; 
 $ fields =''; 
 $ values =''; 
 $ extable = $ arr [0] [0]; 
 foreach（$ arr 如$ 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）; 
  code>  pre> 
 
 我不明白为什么它会删除第一组数据。 谢谢你看这个。 p> 
  div>

答

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

答

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;
}

答

Seems to be resolved by changing the else statement to

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

答

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

在foreach循环中删除数据

相关推荐