在php中循环多维数组并执行mysql插入（股票数据）

我有一个多维数组，我希望循环遍历它并为数组中的值执行 mysql 数据库插入。

我需要插入到 sql 查询中的数组值是：

candles[0][complete], candles[0][volume], candles[0][mid][h], candles[0][mid][l], candles[0][mid][c]  
candles[1][complete], candles[1][volume], candles[1][mid][h], candles[1][mid][l], candles[1][mid][c] 
candles[2][complete], candles[2][volume], candles[2][mid][h], candles[2][mid][l], candles[2][mid][c] 

数组：

$get_instruments_candles = Array
(
    [instrument] => EUR_USD
    [granularity] => H4
    [candles] => Array
        (
            [0] => Array
                (
                    [complete] => 1
                    [volume] => 4491
                    [time] => 2009-01-01T22:00:00.000000000Z
                    [bid] => Array
                        (
                            [o] => 1.40390
                            [h] => 1.40515
                            [l] => 1.39445
                            [c] => 1.39540  
                        )

                    [mid] => Array
                        (
                            [o] => 1.40440
                            [h] => 1.40565
                            [l] => 1.39470
                            [c] => 1.39565
                        )

                    [ask] => Array
                        (
                            [o] => 1.40490
                            [h] => 1.40615
                            [l] => 1.39495
                            [c] => 1.39590
                        )

                )

            [1] => Array
                (
                    [complete] => 1
                    [volume] => 6866
                    [time] => 2009-01-02T02:00:00.000000000Z
                    [bid] => Array
                        (
                            [o] => 1.39545
                            [h] => 1.39690
                            [l] => 1.38447
                            [c] => 1.38641
                        )

                    [mid] => Array
                        (
                            [o] => 1.39570
                            [h] => 1.39715
                            [l] => 1.38472
                            [c] => 1.38666
                        )

                    [ask] => Array
                        (
                            [o] => 1.39595
                            [h] => 1.39740
                            [l] => 1.38497
                            [c] => 1.38691
                        )

                )

            [2] => Array
                (
                    [complete] => 1
                    [volume] => 12529
                    [time] => 2009-01-02T06:00:00.000000000Z
                    [bid] => Array
                        (
                            [o] => 1.38635
                            [h] => 1.39679
                            [l] => 1.38391
                            [c] => 1.39471
                        )

                    [mid] => Array
                        (
                            [o] => 1.38660
                            [h] => 1.39688
                            [l] => 1.38400
                            [c] => 1.39480
                        )

                    [ask] => Array
                        (
                            [o] => 1.38685
                            [h] => 1.39697
                            [l] => 1.38409
                            [c] => 1.39489
                        )

                )

        )

 )

该数组最多可由 5000 个蜡烛[] 值组成，因此我需要找到在 php 中执行 foreach 或 while 循环的最佳方法，以运行抛出数组值并执行 MySQL 插入

// connect to mysql database

// Do a loop to create and insert the values into database    

// Create sql Query to insert. [0] would be changed to arrays value 0,1,2,3,etc

$sql = "INSERT INTO table (complete, volume, high, low, close) 
VALUES ('{candles[0][complete]}','{candles[0][volume]}','{candles[0][mid][h]}','{candles[0][mid][l]}','{candles[0][mid][c]}')";

您可以使用foreach循环，然后引用循环的当前元素。最好使用准备好的语句。这是 PDO 版本：

$stmt = $link->prepare("INSERT INTO table (complete, volume, high, low, close) 
                        VALUES (:complete, :volume, :high, :low, :close)";
$stmt->bindParam(':complete', $complete);
$stmt->bindParam(':volumn', $volume);
$stmt->bindParam(':high', $high);
$stmt->bindParam(':low', $low);
$stmt->bindParam(':close', $close);

foreach ($get_instrument_candles['candles'] as $candle) {
    $complete = $candle['complete'];
    $volume = $candle['volume'];
    $high = $candle['mid']['h'];
    $low = $candle['mid']['l'];
    $close = $candle['mid']['c'];
    $stmt->execute();
}

mysqli 版本如下所示：

$complete = $volume = $high = $low = $close = null;
$stmt = $link->prepare("INSERT INTO table (complete, volume, high, low, close) 
                        VALUES (?, ?, ?, ?, ?)";
$stmt->bind_param("iiiii", $complete, $volume, $high, $low, $close);

The foreach循环与 PDO 相同。