dongyun3805 2013-05-02 21:47 采纳率: 0%
浏览 40
已采纳

使用mysqli将多维数组转换为db

I have a lengthy array $prices that is structured like this

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
)

I want to put it into a mysql table that has been created with the corresponding rows. fund_id,class,currency,nav,nav_change,price_date

here is my latest attempt to insert the rows into multiple db rows

    $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();

     }

Currently this is returning a Fatal error: Call to a member function execute() on a non-object. I'm not clear on a few things (obviously) with the mysqli approach, like where the loop goes and if this is the right kind of loop to access the data.

Any help would be great, thanks.

  • 写回答

1条回答 默认 最新

  • doumingo04696 2013-05-02 22:28
    关注

    There are a few things wrong with your code.

    1. You are not using the array syntax correctly
    2. In your query the non-number values need to be quoted
    3. And you are querying the database several times, which is not necessary

    I do not have your database at my disposal so the following code has not been tested in any way. Read the comments and try to understand the code:

    // 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!");
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题