Inserting multiple rows at once with prepared statements

Question

I would like to know how can I insert multiple values in an array via prepared statements. I've looked at these two (this question and this other one ) questions but they don't seem to do what I'm trying. This is what I have:

$stmt = $this->dbh->prepare("INSERT INTO
t_virtuemart_categories_en_gb 
(category_name, virtuemart_category_id)
VALUES
(:categoryName, :categoryId)
;");

foreach($this->values as $insertData){
    $categoryName = $insertData['categoryName'];
    $categoryId = $insertData['categoryId'];
    $stmt->bindParam(':categoryName', $categoryName);
    $stmt->bindParam(':categoryId', $categoryId);
    $stmt->execute();
}

I tried placing the prepare line inside the foreach loop and outside, but it only adds the first key in the array, and I don't understand why.

This is my Connection.php file:

<?php
$hostname = 'localhost';
$username = 'root';
$password = '';

function connectDB ($hostname, $username, $password){
$dbh = new PDO("mysql:host=$hostname;dbname=test", $username, $password);
return $dbh;
}

try {
$dbh = connectDB ($hostname, $username, $password);
} catch(PDOException $e) {
echo $e->getMessage();
}

And my Import.php file:

<?php
class Import{
public function __construct($dbh, $values) {
    $this->dbh = $dbh;
    $this->values = $values;
}

public function importData() {
    $stmt = $this->dbh->prepare("INSERT INTO
    t_virtuemart_categories_en_gb 
    (category_name, virtuemart_category_id)
    VALUES
    (:categoryName, :categoryId)
    ;");

    foreach($this->values as $insertData){
        $categoryName = $insertData['categoryName'];
        $categoryId = $insertData['categoryId'];
        $stmt->bindParam(':categoryName', $categoryName);
        $stmt->bindParam(':categoryId', $categoryId);
        $stmt->execute();
    }
}

}

Answer 1

Working principle:

Use only one INSERT sql statement to add multiple records, defined by your values pairs. In order to achieve this you have to build the corresponding sql statement in the form

INSERT INTO [table-name] ([col1],[col2],[col3],...) VALUES (:[col1],:[col2],:[col3],...), (:[col1],:[col2],:[col3],...), ...

by iterating through your values array.

Notes:

• I hope you'll understand all. I commented as much as I could. I didn't test it, but it should work. Maybe an answer I wrote a short time ago will give you further ideas regarding structuring of data access classes/functions as well.
• Never use ";" at the end of the sql statements when you define them in PHP.
• Never use one input marker to bind multiple values. For each value to bind use a unique named input marker.

Good luck.

Connection.php

<?php

$hostname = 'localhost';
$username = 'root';
$password = '';
$port = 3306;

try {
    // Create a PDO instance as db connection to a MySQL db.
    $connection = new PDO(
            'mysql:host='. $hostname .';port='.$port.';dbname=test'
            , $username
            , $password
    );

    // Assign the driver options to the db connection.
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
    $connection->setAttribute(PDO::ATTR_PERSISTENT, TRUE);
} catch (PDOException $exc) {
    echo $exc->getMessage();
    exit();
} catch (Exception $exc) {
    echo $exc->getMessage();
    exit();
}

Import.php:

<?php

class Import {

    /**
     * PDO instance as db connection.
     * 
     * @var PDO
     */
    private $connection;

    /**
     * 
     * @param PDO $connection PDO instance as db connection.
     * @param array $values [optional] Values list.
     */
    public function __construct(PDO $connection, array $values = array()) {
        $this->connection = $connection;
        $this->values = $values;
    }

    /**
     * Import data.
     * 
     * @return int Last insert id.
     * @throws PDOException
     * @throws UnexpectedValueException
     * @throws Exception
     */
    public function importData() {
        /*
         * Values clauses list. Each item will be
         * later added to the sql statement.
         * 
         *  array(
         *      0 => '(:categoryName0, :categoryId0)',
         *      1 => '(:categoryName1, :categoryId1)',
         *      2 => '(:categoryName2, :categoryId2)',
         *  )
         */
        $valuesClauses = array();

        /*
         * The list of the input parameters to be
         * bound to the prepared statement.
         * 
         *  array(
         *      :categoryName0 => value-of-it,
         *      :categoryId0 => value-of-it,
         *      :categoryName1 => value-of-it,
         *      :categoryId1 => value-of-it,
         *      :categoryName2 => value-of-it,
         *      :categoryId2 => value-of-it,
         *  )
         */
        $bindings = array();

        /*
         * 1) Build a values clause part for each array item,
         *    like '(:categoryName0, :categoryId0)', and 
         *    append it to the values clauses list.
         * 
         * 2) Append each value of each item to the input
         *    parameter list.
         */
        foreach ($this->values as $key => $item) {
            $categoryName = $item['categoryName'];
            $categoryId = $item['categoryId'];

            // Append to values clauses list.
            $valuesClauses[] = sprintf(
                    '(:categoryName%s, :categoryId%s)'
                    , $key
                    , $key
            );

            // Append to input parameters list.
            $bindings[':categoryName' . $key] = $categoryName;
            $bindings[':categoryId' . $key] = $categoryId;
        }

        /*
         * Build the sql statement in the form:
         *  INSERT INTO [table-name] ([col1],[col2],[col3]) VALUES 
         *  (:[col1],:[col2],:[col3]), (:[col1],:[col2],:[col3]), ...
         */
        $sql = sprintf('INSERT INTO t_virtuemart_categories_en_gb (
                    category_name,
                    virtuemart_category_id
                ) VALUES %s'
                , implode(',', $valuesClauses)
        );

        try {
            // Prepare the sql statement.
            $statement = $this->connection->prepare($sql);

            // Validate the preparing of the sql statement.
            if (!$statement) {
                throw new UnexpectedValueException('The sql statement could not be prepared!');
            }

            /*
             * Bind the input parameters to the prepared statement 
             * and validate the binding of the input parameters.
             * 
             * -----------------------------------------------------------------------------------
             * Unlike PDOStatement::bindValue(), when using PDOStatement::bindParam() the variable 
             * is bound as a reference and will only be evaluated at the time that 
             * PDOStatement::execute() is called.
             * -----------------------------------------------------------------------------------
             */
            foreach ($bindings as $key => $value) {
                // Read the name of the input parameter.
                $inputParameterName = is_int($key) ? ($key + 1) : (':' . ltrim($key, ':'));

                // Read the data type of the input parameter.
                if (is_int($value)) {
                    $inputParameterDataType = PDO::PARAM_INT;
                } elseif (is_bool($value)) {
                    $inputParameterDataType = PDO::PARAM_BOOL;
                } else {
                    $inputParameterDataType = PDO::PARAM_STR;
                }

                // Bind the input parameter to the prepared statement.
                $bound = $statement->bindValue($inputParameterName, $value, $inputParameterDataType);

                // Validate the binding.
                if (!$bound) {
                    throw new UnexpectedValueException('An input parameter could not be bound!');
                }
            }

            // Execute the prepared statement.
            $executed = $statement->execute();

            // Validate the prepared statement execution.
            if (!$executed) {
                throw new UnexpectedValueException('The prepared statement could not be executed!');
            }

            /*
             * Get the id of the last inserted row.
             */
            $lastInsertId = $this->connection->lastInsertId();
        } catch (PDOException $exc) {
            echo $exc->getMessage();
            // Only in development phase !!!
            // echo '<pre>' . print_r($exc, TRUE) . '</pre>';
            exit();
        } catch (Exception $exc) {
            echo $exc->getMessage();
            // Only in development phase !!!
            // echo '<pre>' . print_r($exc, TRUE) . '</pre>';
            exit();
        }

        return $lastInsertId;
    }

}

Answer 2

I think the statements have to be prepared and bound separately for each iteration:

if($stmt = $this->dbh->prepare("INSERT INTO t_virtuemart_categories_en_gb (category_name, virtuemart_category_id) VALUES (:categoryName, :categoryId);")){
    foreach($this->values as &$insertData){
        $stmt->bindParam(':categoryName', $insertData['categoryName']);
        $stmt->bindParam(':categoryId', $insertData['categoryId']);
        $stmt->execute();
        $stmt->close();
    }
}

Answer 3

I would suggest this, using a $dbh = mysqli_connect():

<?php
class Import{
public function __construct($dbh, $values) {
    $this->dbh = $dbh;
    $this->values = $values;
}

public function importData() {
    $stmt = $this->dbh->prepare("INSERT INTO t_virtuemart_categories_en_gb 
    (category_name, virtuemart_category_id)
    VALUES
    (?, ?)");

    $catetoryName = ''; $categoryId = '';
    $stmt->bind_param('ss', $categoryName, $categoryId);
    foreach($this->values as $insertData){
        $categoryName = $insertData['categoryName'];
        $categoryId = $insertData['categoryId'];
        $stmt->execute();
    }
}

}

In this way you create a reference and bind that variable to the execution of the prepared statement.

You should be careful about it when passing an array, the trick is commented in the php.net page ( http://php.net/manual/it/mysqli.prepare.php )

A code that work is:

$typestring = 'sss'; //as many as required: calc those
$stmt = $dbconni->prepare($ansqlstring);
$refs = [$typestring];
// if $source is an array of array  [ [f1,f2,...], [f1,f2,...], ...]
foreach($source as $data) {
  if (count($refs)==1) {
    foreach ($data as $k => $v) {
      $refs[] = &$data[$k];
    }
    $ref = new \ReflectionClass('mysqli_stmt');
    $method = $ref->getMethod("bind_param");
    $method->invokeArgs($stmt, $refs);
    $r = $stmt->execute();
  } else {
    // references are maintained: no needs to bind_param again
    foreach ($data as $k => $v) {
      $refs[$k+1] = $v;
    }
    $r = $stmt->execute();
  }
}

this spare resources and is more performant, but you have to make benchmark to be sure about my words.

this is one of the case where prepared statement make sense, see

https://joshduff.com/2011-05-10-why-you-should-not-be-using-mysqli-prepare.md

Normally PDO emulate prepared statement, see PDO::ATTR_EMULATE_PREPARES

EDIT: specify it is using mysqli, and correct the code.