php - 如何在for每个循环中使用多个索引并在mysql表中插入数据

Question

我正在尝试从 API 获取数据并将其插入 MySQL 表中。在此过程中，我使用 for-each 循环来获取数据数组。如何避免多次使用循环并仅在一个循环本身中使用索引？

<?php
$db = mysqli_connect('localhost', 'root', '', 'testing');
$url = 'MYAPI';
$curl = curl_init();
curl_setopt_array($curl, array(
    CURLOPT_URL => $url,
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_ENCODING => "",
    CURLOPT_MAXREDIRS => 10,
    CURLOPT_TIMEOUT => 30,
    CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
    CURLOPT_CUSTOMREQUEST => "POST",
    CURLOPT_POSTFIELDS => "",
    CURLOPT_HTTPHEADER => array(
        "accept: application/json",
        "cache-control: no-cache",
        "content-type: application/json"
    ),
));

$response = curl_exec($curl);
$err = curl_error($curl);
curl_close($curl);

if ($err)
{
    echo "cURL Error #:" . $err;
}
else
{
    $source = json_decode($response);
    if (is_array($source) || is_object($source))
    {
        foreach( $source->DATA as &$data )
        {
            $data = array_combine( $source->COLUMNS, $data );
        }

        $CLIENTCODE = ( array_column( $source->DATA, 'CLIENTCODE') );
        $BRANCHCODE = ( array_column( $source->DATA, 'BRANCHCODE') );
        $FAMILYCODE = ( array_column( $source->DATA, 'FAMILYCODE') );
        $CLIENTNAME = ( array_column( $source->DATA, 'CLIENTNAME') );
        $BRANCHNAME = ( array_column( $source->DATA, 'BRANCHNAME') );

        foreach ($CLIENTCODE as $cc)
        {
            $query = "INSERT INTO holding (CLIENTCODE) VALUES('$cc')";
            mysqli_query($db, $query);
        }

        foreach ($BRANCHCODE as $bc)
        {
            $query = "INSERT INTO holding (BRANCHCODE) VALUES('$bc')";
            mysqli_query($db, $query);
        }
        .
        .
        .
        .
        .
    }
}
?>

Answer 1

您可以在单个mysql查询中添加多个值，将所有数据放入如下所示的值，然后执行一次数据库插入查询

查询语法

INSERT INTO MyTable ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )

示例

<?php 

$vals = ""; // empty var for store values 
$array = ["John", "Ram", "Mohan"]; // sample array 

foreach($array as $val) {
    $vals .= "('".$val."'),"; 
}

$vals = rtrim($vals,',');
$query = "INSET INTO tbl(name) VALUES $vals";

// Now Execute query to insert 
mysqli_query($db, $query);