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

    报告相同问题?

    悬赏问题

    • ¥100 webapi的部署(标签-服务器)
    • ¥20 怎么加快手机软件内部计时的时间(关键词-日期时间)
    • ¥15 C语言除0问题的检测方法
    • ¥15 为什么四分管的内径有的是16mm有的15mm,四分不应该是12.7mm吗
    • ¥15 macos13下 ios交叉编译的问题
    • ¥15 bgz压缩文件怎么打开
    • ¥15 封装dll(引入了pcl的点云设计库)
    • ¥30 关于#开发语言#的问题:我需要在抄板的基础上再抄板抄程序,根据RDA8851CM基础上开发
    • ¥15 oracle 多个括号,怎么删除指定的括号及里面的内容?
    • ¥15 小新14API2019想用bios调风扇