dongting3135
2011-08-14 05:42
浏览 112
已采纳

使用PDO准备的MySQL语句,从数据数组中将多个值插入多个列

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.

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.

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

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

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

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?

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dongqi1245 2011-08-14 05:52
    已采纳

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

    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);
    
    }
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • duanaixuan7385 2011-08-14 05:53

    bindParam does indeed take a reference to the variable -- note that the second parameter is mixed &$variable in the method prototype. Changes to the variable between binding and statement execution will affect the query.

    Also, note that this is what allows you to bind to "out" or "inout" SQL variables -- after the execute() call, those variables would change to whatever value the statement produced.

    评论
    解决 无用
    打赏 举报
  • dqz7636 2013-11-08 05:32

    You should escape and filter the params in the query (or use bindParam) because security reason inside the insert function!

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题