How to Insert Multiple Rows Using Prepared Statements

Java: Insert multiple rows into MySQL with PreparedStatement

You can create a batch by PreparedStatement#addBatch() and execute it by PreparedStatement#executeBatch().

Here's a kickoff example:

public void save(List<Entity> entities) throws SQLException {
    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT);
    ) {
        int i = 0;

        for (Entity entity : entities) {
            statement.setString(1, entity.getSomeProperty());
            // ...

            statement.addBatch();
            i++;

            if (i % 1000 == 0 || i == entities.size()) {
                statement.executeBatch(); // Execute every 1000 items.
            }
        }
    }
}

It's executed every 1000 items because some JDBC drivers and/or DBs may have a limitation on batch length.

See also:

• JDBC tutorial - Using PreparedStatement
• JDBC tutorial - Using Statement Objects for Batch Updates

How to insert multiple rows in a mysql database at once with prepared statements?

This is completely valid:

$stmt = $mysqli->prepare("INSERT INTO something (userid, time, title) VALUES (?, ?, ?)");

$stmt->bind_param('iis', $userid, time(), $title);
$stmt->execute();

$stmt->bind_param('iis', $userid, time(), $title);
$stmt->execute();

$stmt->bind_param('iis', $userid, time(), $title);
$stmt->execute();

$stmt->bind_param('iis', $userid, time(), $title);
$stmt->execute();

You can foreach over your array of values to insert and bind and execute each time. It wont be quite as fast as the bulk insert in the example you linked, but it will be more secure.

Inserting multiple rows at once with prepared statements

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

}

insert multiple values into multiple rows with prepared statement PHP, MYSQL

Rather than implode the contents of $_POST['multiselectdata'] you can iterate over it as an array and execute your prepared statement for each value in the array:

$stmt = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt, $sql)){
    // ....
    exit();
}
else {
    mysqli_stmt_bind_param($stmt, "ii", $value, $id);
    foreach (explode(',', $_POST['multiselectdata']) as $value) {
        mysqli_stmt_execute($stmt);
    }
}

Note that calling mysqli_stmt_store_result on an INSERT query makes no sense as there is no result set. That line and the foreach loop following should be removed from your code.

PDO Prepared Inserts multiple rows in single query

Multiple Values Insert with PDO Prepared Statements

Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts.

$datafields = array('fielda', 'fieldb', ... );

$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);

more data values or you probably have a loop that populates data.

With prepared inserts you need to know the fields you're inserting to, and the number of fields to create the ? placeholders to bind your parameters.

insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....

That is basically how we want the insert statement to look like.

Now, the code:

function placeholders($text, $count=0, $separator=","){
    $result = array();
    if($count > 0){
        for($x=0; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}

$pdo->beginTransaction(); // also helps speed up your inserts.
$insert_values = array();
foreach($data as $d){
    $question_marks[] = '('  . placeholders('?', sizeof($d)) . ')';
    $insert_values = array_merge($insert_values, array_values($d));
}

$sql = "INSERT INTO table (" . implode(",", $datafields ) . ") VALUES " .
       implode(',', $question_marks);

$stmt = $pdo->prepare ($sql);
$stmt->execute($insert_values);
$pdo->commit();

Although in my test, there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value.

---

Related Topics