将多维数组的一部分插入mysql数据库

Question

下面是Magento api调用返回的数组的一部分。如何循环遍历所有记录并将parent_id、base_price、sku和name键的值插入MySQL数据库:

testarray美元

array(2) {
  [0]=>
  array(5) {
    ["store_id"]=>
    string(1) "1"
    ["base_grand_total"]=>
    string(3) "200"
    ["invoice_id"]=>
    string(1) "3"
    ["order_increment_id"]=>
    string(1) "2"
    ["items"]=>
    array(5) {
      ["parent_id"]=>
      string(1) "1"
      ["base_price"]=>
      string(8) "1400.000"
      ["tax_amount"]=>
      string(8) "120.2300"
      ["sku"]=>
      string(8) "testsku1"
      ["name"]=>
      string(9) "testprod1"
    }
  }
  [1]=>
  array(5) {
    ["store_id"]=>
    string(1) "1"
    ["base_grand_total"]=>
    string(3) "300"
    ["invoice_id"]=>
    string(1) "4"
    ["order_increment_id"]=>
    string(1) "3"
    ["items"]=>
    array(5) {
      ["parent_id"]=>
      string(1) "2"
      ["base_price"]=>
      string(8) "1000.000"
      ["tax_amount"]=>
      string(8) "100.5400"
      ["sku"]=>
      string(8) "testsku2"
      ["name"]=>
      string(9) "testprod2"
    }
  }
}

到目前为止，我的代码如下:

foreach ($testarray as $row)
    {
    mysqli_query($con, "INSERT INTO order_sku (parent_id, base_price, tax_amount, sku, name) VALUES ('$row[parent_id]', '$row[base_price]', '$row[tax_amount]', '$row[sku]', '$row[name]')");
    }

Answer 1

您已经在使用MYSQLI_函数，请充分利用使用预处理语句的优势。您需要在内部使用另一个foreach循环来访问items索引值。例子:

$testarray = array(
    array(
        'store_id' => '1', 
        'base_grand_total' => '200', 
        'invoice_id' => '3', 
        'order_increment_id' => '2',
        'items' => array('parent_id' => '1', 'base_price' => '1400.000', 'tax_amount' => '120.2300', 'sku' => 'testsku1', 'name' => 'testprod1')
    ),
    array(
        'store_id' => '1', 
        'base_grand_total' => '300', 
        'invoice_id' => '4', 
        'order_increment_id' => '3',
        'items' => array('parent_id' => '2', 'base_price' => '1000.000', 'tax_amount' => '100.5400', 'sku' => 'testsku2', 'name' => 'testprod2')
    ),
);
$con = new mysqli('localhost', 'username', 'password', 'database');
foreach($testarray as $values) {
    foreach($values['items'] as $item) {
        $stmt = $con->prepare('INSERT INTO order_sku (parent_id, base_price, tax_amount, sku, name) VALUES (?, ?, ?, ?, ?)');
        // provided the columns are all VARCHAR
        $stmt->bind_param('sssss', $item['parent_id'], $item['base_price'], $item['tax_amount'], $item['sku'], $item['name']);
        $stmt->execute();
    }
}