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

Question

I am trying to use staticsan´s answer in this question for prepared statements. Lets take this example:

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

In staticsan´s answer imploding the array is adding all the values into the mysql statement so that in the end we can insert multiple data into the database with just one statement. How would this be done in my example?

Your example has a fixed number of parameters, so it's already solved. Can you elaborate your problem a bit more? What have you tried so far? It should basically work the same but adding more parameters, e.g. in a foreach loop. — hakre
– hakre, Commented Dec 2, 2011 at 20:14
You can form prepared statement using code as mentioned here, stackoverflow.com/questions/1176352/… — Hemant Thorat
– Hemant Thorat, Commented Aug 30, 2016 at 11:53

Jonathan Rich · Accepted Answer · 2011-12-02 20:18:26Z

9

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.

answered Dec 2, 2011 at 20:18

Jonathan Rich

1,73810 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Over a year ago

Thanks, that works. But the execute() will be executed for each array element, so each row will be inserted into the database separately and not all at the same time. Am I right? I was hoping to see a solution where you bind_param first all the values and at the very end execute it once.

Jonathan Rich Over a year ago

Yes, that's correct, but the main advantage is the speed increase provided by the prepared statement - preparing the statement once and running in multiple times is much faster than preparing it each time.

NoobishPro Over a year ago

This is still tons slower than a good old static INSERT query though, isn't it? I mean, executing queries in a loop creates a ~2ms delay between every query. With big arrays this would take forever, no?

jkmartindale · Accepted Answer · 2019-07-09 16:59:29Z

You can build prepared statement using code as mentioned here,

PDO Prepared Inserts multiple rows in single query

PHP logic will be sort of like,

/**
 * Insert With Ignore duplicates in Mysql DB.
 */
public static function insertWithIgnore($em, $container, $tableName, $fields, $rows)
{
    $query = "INSERT IGNORE INTO $tableName (`" . implode('`,`', $fields) . "`) VALUES ";
    $placeHolr = array_fill(0, count($fields), "?");
    $qPart = array_fill(0, count($rows), "(" . implode(',', $placeHolr) . ")");
    $query .= implode(",", $qPart);

    $pdo = self::getPDOFromEm($em, $container);
    $stmt = $pdo->prepare($query);
    $i = 1;
    foreach ($rows as $row) {
        $row['created_at'] = date("Y-m-d H:i:s");
        foreach ($fields as $f) {
            if (!isset($row[$f])) {
                $row[$f] = null;
            }
            $stmt->bindValue($i++, $row[$f]);
        }
    }

    $result = $stmt->execute();

    if ($result == false) {
        $str = print_r($stmt->errorInfo(), true);
        throw new \Exception($str);
    }

    $stmt->closeCursor();
    $pdo = null;
}

/**
 * Replace old rows in Mysql DB.
 */
public static function replace($em, $container, $tableName, $fields, $rows, $extraFieldValues = null)
{
    if ($extraFieldValues != null) {
        $fields = array_unique(array_merge($fields, array_keys($extraFieldValues)));
    }

    $query = "REPLACE INTO $tableName (`" . implode('`,`', $fields) . "`) VALUES ";
    $placeHolr = array_fill(0, count($fields), "?");
    $qPart = array_fill(0, count($rows), "(" . implode(',', $placeHolr) . ")");
    $query .= implode(",", $qPart);

    $pdo = self::getPDOFromEm($em, $container);
    $stmt = $pdo->prepare($query);
    $i = 1;
    foreach ($rows as $row) {            
        if ($extraFieldValues != null) {
            $row = array_merge($row, $extraFieldValues);
        }
        foreach ($fields as $f) {
            $stmt->bindValue($i++, $row[$f]);
        }                        
    }
    $stmt->execute();
    if (!$stmt) {
        throw new \Exception("PDO::errorInfo():" . print_r($stmt->errorInfo(), true));
    }
    $stmt->closeCursor();
    $pdo = null;
}

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related