遍历多维数组，获取要插入到MySQL中的值

Question

我有这个多维数组:

array(88) {
  [0]=>
  array(9) {
    ["currency_pair"]=>
    string(6) "BTCCAD"
    ["type"]=>
    string(3) "Buy"
    ["trade_id"]=>
    string(5) "34637"
    ["order_id"]=>
    string(8) "51214063"
    ["quantity"]=>
    string(10) "0.03296178"
    ["price"]=>
    string(6) "271.83"
    ["total"]=>
    string(4) "8.96"
    ["fee"]=>
    string(4) "0.04"
    ["executed"]=>
    string(19) "2015-06-05 03:04:21"
  }
  [1]=>
  array(9) {
    ["currency_pair"]=>
    string(6) "BTCCAD"
    ["type"]=>
    string(3) "Buy"
    ["trade_id"]=>
    string(5) "34631"
    ["order_id"]=>
    string(8) "51183954"
    ["quantity"]=>
    string(10) "0.47688340"
    ["price"]=>
    string(6) "271.45"
    ["total"]=>
    string(6) "129.45"
    ["fee"]=>
    string(4) "0.54"
    ["executed"]=>
    string(19) "2015-06-04 23:19:09"
  }
  [2]=>
  array(9) {
    ["currency_pair"]=>
    string(6) "BTCCAD"
    ["type"]=>
    string(3) "Buy"
    ["trade_id"]=>
    string(5) "34630"
    ["order_id"]=>
    string(8) "51169904"
    ["quantity"]=>
    string(10) "0.25000000"
    ["price"]=>
    string(6) "272.63"
    ["total"]=>
    string(5) "68.16"
    ["fee"]=>
    string(4) "0.29"
    ["executed"]=>
    string(19) "2015-06-04 21:45:01"
  }
}

我想通过数组进行迭代，获取每个键的值(例如，"currency_pair"，"type"，"trade_id"等)并将它们插入mysql表。

foreach ($datas as $data) {
    
    $datetime 		= $data['executed'];
    $currency_pair 	= $data['currency_pair'];
    $type 			= $data['type'];
    $quantity 		= $data['quantity'];
    $rate 			= $data['price'];
    $total 			= $data['total'];
    $fee 			= $data['fee'];
	$servername = "localhost";
	$username = "root";
	$password = "password";
	$dbname = "myDB";
	
	$connection = mysqli_connect($servername, $username, $password, $dbname);
	
	
	// create connection
	if (!$connection) {
		die("connection failed: " . mysqli_connect_error());
	} else {
		echo "connected! ";
	}
	
	// sql to create table
	
	$sql = "INSERT INTO Transactions (type)
	VALUES ('$type')";
	
	if (mysqli_query($connnection, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($connnection);
}
	
} 
mysqli_close($connection);

在上面的例子中，我只是试图将'types'插入数据库。我得到两个错误:

注意:未定义变量:connection in

Answer 1

首先你的JSON是无效的，上面的大括号应该是方括号。

应该像:

[ 
    {
        "date" : "May 12",
        "id" : "20",
        "type" : "tall"
    }, 
    {
        "date" : "May 14",
        "id" : "30",
        "type" : "short"
    }, 
    {
        "date" : "May 17",
        "id" : "70",
        "type" : "giant"
    }
]

你可以使你的JSON源正确，或者去掉你的响应的第一个和最后一个字符，并在方括号中括起来，如:

$json_response = '['.substr(trim($response), 1, -1).']';

在do json_decode之后，loop through

$json_array = json_decode($json_response);
if(!empty(json_response)){
    foreach ($json_response as $key => $value) {
        // Do Something!
    }
}