duanke0555 2016-10-07 21:51
浏览 101
已采纳

将PHP关联数组映射到PDO预处理语句中

I'm doing some cleanup and transformation on data (that part is done, whew), and need to insert it into a MySQL table. Having done this kind of thing in Perl previously, I assumed that, as part of processing, it would make sense for me to structure the data as an associative array with the keys being the same as the field names I need to load them into - that way, it would be easy to construct a prepared statement simply by looping over the keys and producing a list of both the named placeholders and the matching values.

However, I can't seem to make this work in PHP/PDO. Test code:

$x = <<<EOD
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
4 4 4 4 4
EOD;

$fields = array('name', 'job', 'wallet_size', 'inseam', 'pet_name');
foreach(explode("
", $x) as $line){
    $data = array_combine($fields, explode(' ', $line));

    # print_r($data);

    $stmt = $dbh->prepare('INSERT INTO foobar VALUES('.':'.implode(', :', $fields));

    foreach($fields as $field){
        $stmt->bindParam(':'.$field, $data[$field]);
    }
    $stmt->execute();
}

Frankly, it feels too... graceless and hacky to work - and it doesn't.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' in tst.php:24

There is a correct way to do this, right? I'd appreciate it if someone would introduce me to the appropriate PHP-flavored phrasing for it.

  • 写回答

1条回答 默认 最新

  • doutuan8887 2016-10-07 21:58
    关注

    It's hard to debug SQL when you're staring at the PHP code that formats SQL, instead of the final SQL string itself. I suggest you always create a string variable so you can output it during debugging.

    $sql = 'INSERT INTO foobar VALUES('.':'.implode(', :', $fields);
    echo "$sql
    ";
    $stmt = $dbh->prepare($sql);
    

    Outputs:

    INSERT INTO foobar VALUES(:name, :job, :wallet_size, :inseam, :pet_name
    

    Now it's very easy to see that you forgot the closing ) at the end of that INSERT statement!

    Also, your use of PDO is more difficult than it needs to be. You don't need to use named parameters. You don't need to use bindParam(). Here's how I would write this code:

    $fields = array('name', 'job', 'wallet_size', 'inseam', 'pet_name');
    
    $columns = implode(',', $fields);
    $placeholders = implode(',', array_fill(1, count($fields), '?'));
    $sql = "INSERT INTO foobar ($columns) VALUES ($placeholders)";
    echo "$sql
    "; // use this during debugging
    $stmt = $dbh->prepare($sql);
    
    foreach(explode("
    ", $x) as $line){
        $param_values = explode(' ', $line);
        $stmt->execute($param_values);
    }
    

    Tips:

    • Prepare the query once, and re-use the prepared statement for each row of data you want to insert.
    • Pass an array of data values as an argument to execute(). This is easier than using bindParam().
    • Use positional parameter placeholders (?) instead of named parameter placeholders when your data is in a simple array instead of an associative array.
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥50 易语言把MYSQL数据库中的数据添加至组合框
  • ¥20 求数据集和代码#有偿答复
  • ¥15 关于下拉菜单选项关联的问题
  • ¥20 java-OJ-健康体检
  • ¥15 rs485的上拉下拉,不会对a-b<-200mv有影响吗,就是接受时,对判断逻辑0有影响吗
  • ¥15 使用phpstudy在云服务器上搭建个人网站
  • ¥15 应该如何判断含间隙的曲柄摇杆机构,轴与轴承是否发生了碰撞?
  • ¥15 vue3+express部署到nginx
  • ¥20 搭建pt1000三线制高精度测温电路
  • ¥15 使用Jdk8自带的算法,和Jdk11自带的加密结果会一样吗,不一样的话有什么解决方案,Jdk不能升级的情况