使用mysql将多维数组转换成数据库

Question

我有一个很长的数组$prices，它的结构是这样的

Array
(
    [Fund] => BGEF
    [Class] => G
    [Currency] => CAD
    [NAV] => 8.6442
    [NavChange] => 0.0431
    [PriceDate] => 2013-05-01
)
Array
(
    [Fund] => BGOF
    [Class] => G
    [Currency] => EUR
    [NAV] => 12.1503
    [NavChange] => 0.0226
    [PriceDate] => 2013-05-01
)
Array
(
    [Fund] => BIEF
    [Class] => G
    [Currency] => USD
    [NAV] => 9.6914
    [NavChange] => 0.0635
    [PriceDate] => 2013-05-01
)

我想把它放入一个mysql表，已经创建了相应的行。fund_id、类货币,导航,nav_change price_date

这是我最近一次尝试插入行到多个db行

    $mysqli = new mysqli( "localhost", "user", "pw","db" );
    if( $mysqli->connect_errno ){
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    $mysqli->select_db("db");
                foreach($prices as $rows){                      
                        $sql = "  INSERT INTO price_data(  price_date,fund_id,currency_id,class_id,nav,nav_change  ) 
                            VALUES (  $rows[PriceDate] , $rows[Fund] , $rows[currency] , $rows[Class] , $rows[NAV] , $rows[NavChange]  )   " ;
    $stmt = $mysqli->prepare($sql);
    $stmt->execute();
     }

当前返回致命错误:在非对象上调用成员函数execute()。我对mysqli方法的一些事情(显然)不清楚，比如循环去哪里，如果这是正确的循环访问数据。

任何帮助将是伟大的，谢谢。

Answer 1

你的代码有一些问题。

1. 你没有正确使用数组语法
2. 在您的查询中，需要引用非数字值
3. 并且您正在多次查询数据库，这是不必要的

我没有你的数据库在我的处置，所以下面的代码没有以任何方式进行测试。阅读注释并尝试理解代码:

// Connect to the database as usual
$mysqli = new MySQLi("localhost", "user", "pw", "db");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: ({$mysqli->connect_errno}) {$mysqli->connect_error}";
}
// The first part of the SQL query
$query = "INSERT INTO `price_data` (`price_date`, `fund_id`, `currency_id`, `class_id`, `nav`, `nav_change`) VALUES";
// This is the format of a VALUES tuple; it is used
// below in sprintf()
$format = " ('%s', '%s', '%s', '%s', %f, %f),";
// Go over each array item and append it to the SQL query
foreach($prices as $price) {
    $query .= sprintf(
        $format,
        $mysqli->escape_string($price['PriceDate']),
        $mysqli->escape_string($price['Fund']),
        $mysqli->escape_string($price['Currency']),
        $mysqli->escape_string($price['Class']),
        $mysqli->escape_string($price['NAV']),
        $mysqli->escape_string($price['NavChange'])
    );
}
// The last VALUES tuple has a trailing comma which will cause
// problems, so let us remove it
$query = rtrim($query, ',');
// MySQLi::query returns boolean for INSERT
$result = $mysqli->query($query);
// Find out what happened
if ($result == false) {
    die("The query did not work: {$mysqli->error}");
} else {
    die("The query was a success!");
}