使用MySQL的PDO准备语句将多个值从数据数组插入多个列中 [英] Inserting multiple values into multiple columns from an array of data using a PDO prepared statement for MySQL

问题描述

我是PHP和MySQL的新手(并且是一般编程人员)，我正在尝试创建一个名为Database_Handler的通用数据库处理程序类，该类将帮助我管理诸如插入，删除，选择，更新等基本操作

I'm new to PHP and MySQL (and programming in general) and I'm trying to create a generic database handler class called Database_Handler that will help me manage basic things like insert, delete, select, update, etc.

我目前正在处理一个成员函数来处理插入.在我的插入函数中，我要构建准备好的PDO插入语句并执行它.

I'm working on a member function to handle insert, currently. In my insert function, I would like to build a prepared PDO insert statement and execute it.

假设在我的应用程序中的某个地方，我已按以下方式调用了insert函数:

Assume that somewhere in my application, I have called the insert function as follows:

$table = "books";
$cols = array('author', 'title', 'pubdate');
$values = array('Bob Smith', 'Surviving the Zombie Apocalypse', '2010');

$db_handler->insert($table, $cols, $values);

如何使用$table，$cols和$values中的数据来构建准备好的PDO插入语句?这是基于.

How can I use the data from $table, $cols and $values to build a prepared PDO insert statement? Here's my first effort, based on an answer from "How to insert an array into a single MySQL Prepared statement w/ PHP and PDO".

public function insert($table, $cols, $values){

        $numvalues = count($values);

        $placeholder = array();
        for($i=0; $i<$numvalues; $i++)
        $placeholder[$i] = '?';

        $sql = 'INSERT INTO '. $table . '(' . implode(",", $cols) . ') ';
        $sql.= 'VALUES (' . implode("," $placeholder) . ')"';

        $stmt = $this->dbh->prepare($sql);
        $for($i=0; $i<$numvalues; $i++)
            $stmt->bindParam($i+1, $values[$i])
        $stmt->execute();
}

我认为这行不通，但是也许它将使您对我想做的事情有所了解.我有些困惑，因为php.net手册上给出了示例是:

I don't think this will work, but maybe it will give you an idea of what I want to do. I'm a little confused because the example given on the php.net manual is:

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

似乎他们正在将$name作为参数发送到bindParam()函数，然后将值分配给$name? $name发送到bindParam()时的值是什么?还是bindParam()只是将参数与变量相关联，而不获取该变量的数据-允许execute()处理该部分?

It seems like they are sending $name as a parameter to the bindParam() funciton, then assigning a value to $name afterwards? What is the value of $name when it's sent to bindParam()? Or does bindParam() just associate a parameter with a variable, without taking that variable's data - allowing execute() to handle that part?

推荐答案

是的，bindParam将参数绑定到变量名称(引用)，而不是值，例如

Yes, bindParam binds a parameter to a variable name (reference), not a value, as the manual says.

但是，针对您的情况，有一种更简单的语法. PDOStatement::execute可以采用值数组.

However, there's a simpler syntax for your situation. PDOStatement::execute can take an array of values.

public function insert($table, $cols, $values){

    $placeholder = array();
    for ($i = 0; i < count($values); $i++)
      $placeholder[] = '?';

    $sql = 'INSERT INTO '. $table . ' (`' . implode("`, `", $cols) . '`) ';
    $sql.= 'VALUES (' . implode(", ", $placeholder) . ')';

    $stmt = $this->dbh->prepare($sql);
    $stmt->execute($values);

}

这篇关于使用MySQL的PDO准备语句将多个值从数据数组插入多个列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！