使用 PDO 将大量变量插入表中

2021-12-26 00:00:00 php mysql pdo

我有一个包含大约 25 个输入字段的大表单.

I have a large form with about 25 input fields.

我正在尝试将它们插入我的表格中，而我知道如何使用以下内容的唯一方法...

Im trying to insert them into my table and the only way i know how is using the following...

$count = $dbh->exec("INSERT INTO directory(field1, field2) VALUES (':value1', ':value2')");

由于我有这么多帖子变量，有没有比在我的查询中输入每个人更好的方法呢?

As I have so many post variables, is there a better way to do this than type each and everyone into my query?

推荐答案

动态准备查询

您可以从 $_POST 数组动态构建查询:

Dynamic prepared queries

You can build your query dynamically from $_POST array:

但是，永远不要相信用户输入，这意味着您不能相信 $_POST 中的数据将包含有效的列名.

But, NEVER trust user input, which means you cannot trust that data in $_POST will contain valid column names.

1.清理帖子数据

可以定义一个白名单列名数组$whitelist = array('field1', 'field2', ...)，然后使用:

You can define an array of whitelisted column names $whitelist = array('field1', 'field2', ...), and then use:

$data = array_intersect_key($_POST, array_flip($whitelist));

找到列入白名单的列和您的 $_POST 数组之间的交集.(感谢@BillKarwin)

to find the intersection between the whitelisted columns and your $_POST array. (Thanks @BillKarwin)

2.构建查询

private function buildInsertSql($data, $table) {
    $columns = "";  
    $holders = "";  
    foreach ($data as $column => $value) {  
       $columns .= ($columns == "") ? "" : ", ";  
       $columns .= $column;  
       $holders .= ($holders == "") ? "" : ", ";  
       $holders .= ":$column";  
    }  
    $sql = "INSERT INTO $table ($columns) VALUES ($holders)";  
    return $sql; 
}

这将为您提供以下形式的 SQL 语句:

This will give you a SQL statement of the form: 

$sql = INSERT INTO directory (field1, field2) VALUES (:field1, :field2)

并准备声明:

$stmt = $dbh->prepare($sql);

3.绑定参数

然后您可以将参数动态绑定到占位符:

You can then dynamically bind parameters to the placeholders:

foreach ($data as $placeholder => $value) {
    $stmt->bindValue(":$placeholder", $value);
 }

并执行它:

$stmt->execute();

<小时>

更高级一点...

  • 看看这个链接 绑定到相同的占位符有关如何使您的动态准备好的语句更加健壮的信息.
  • 看看这个链接:绑定参数内部循环 有关在循环中绑定参数与值的警告.

    • A little more advanced...

    • Take a look at this link Binding to the same placeholder For information about how to make your dynamic prepared statement more robust.
    • Take a look at this link: Bind Params Inside Loop For a caveat regarding binding paramaters vs values in a loop.

相关文章