Inserting multiple values into multiple columns from an array of data using a PDO prepared statement for MySQL

Question

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?

You wrote `[i]` a few times where you meant `[$i]`, along with some missing commas and extra quotes. Little mistakes like that'll kill ya. — Dan Grossman, Aug 14 '11 at 05:53
As an aside, I don't find classes like this at all useful. You don't really need an abstraction layer that's *between* the DBMS abstraction layer (PDO) and, if the application warrants it, the ORM for persisting objects. If you have to pass in a table name, column list and value list, have you really removed the SQL from your code? — Dan Grossman, Aug 14 '11 at 05:59
Dan, both good points. The 'i's' are just carelessness / my (limited) c++ background. About the abstraction layer - I hadn't considered this. I've tried to look at this application as a chance to play with OOP / PHP and I guess once I picked up that hammer, everything started looking like a nail. It would be a LOT easier to do away with this class altogether, although it was a fun exercise. Thanks! — sanukcm, Aug 14 '11 at 06:12

score 8 · Accepted Answer · answered Aug 14 '11 at 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);

}

Also, may want to change out hte for look with something like: `$placeholder = array_fill(0, count($values), '?');` — RobertPitt, Jul 14 '13 at 12:29

score 0 · Answer 2 · answered Nov 08 '13 at 05:32

0

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

answered Nov 08 '13 at 05:32

distvan

1

score 0 · Answer 3 · answered Aug 14 '11 at 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.

Inserting multiple values into multiple columns from an array of data using a PDO prepared statement for MySQL

3 Answers3

Linked