How to Insert Multiple Rows in PHP Pdo MySQL

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.

What is the best way to insert multiple rows in PHP PDO MYSQL?

You have at least these two options:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values (?,?,?)";

$stmt = $db->prepare($sql);

foreach($rows as $row)
{
    $stmt->execute($row);
}

OR:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values ";

$paramArray = array();

$sqlArray = array();

foreach($rows as $row)
{
    $sqlArray[] = '(' . implode(',', array_fill(0, count($row), '?')) . ')';

    foreach($row as $element)
    {
        $paramArray[] = $element;
    }
}

// $sqlArray will look like: ["(?,?,?)", "(?,?,?)", ... ]

// Your $paramArray will basically be a flattened version of $rows.

$sql .= implode(',', $sqlArray);

$stmt = $db->prepare($sql);

$stmt->execute($paramArray);

As you can see the first version features a lot simpler code; however the second version does execute a batch insert. The batch insert should be faster, but I agree with @BillKarwin that the performance difference will not be noticed in the vast majority of implementations.

PDO MySQL: Insert multiple rows in one query

An easy way for this avoiding the complications would be something like this

$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item)
{
    $stmt->bindValue(':a', $item[0]);
    $stmt->bindValue(':b', $item[1]);
    $stmt->bindValue(':c', $item[2]);
    $stmt->execute();
}

However, this executes the statement multiple times. So, it is better if we create a long single query in order to do this.

Here is an example of how we can do this.

$query = "INSERT INTO foo (key1, key2) VALUES "; //Prequery
$qPart = array_fill(0, count($data), "(?, ?)");
$query .=  implode(",",$qPart);
$stmt = $dbh -> prepare($query); 
$i = 1;
foreach($data as $item) { //bind the values one by one
   $stmt->bindValue($i++, $item['key1']);
   $stmt->bindValue($i++, $item['key2']);
}
$stmt -> execute(); //execute

how to Insert multiple arrays with multiple rows into MySQL using PHP PDO

As you have a nested for loop it will definitely lead for duplicate records. Let me make a quick correction.

$totalamount=0;
for ($i = 0; $i < count($_POST['pname']); $i++){
    $proname = $_POST['pname'][$i];
    $quantity = $_POST['pcount'][$i];
    $estamount = $_POST['estamount'][$i];
    $totalamount = $quantity*$estamount;

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO `order`(custname, contact, product_name, quantity, est_amount, advance_paid, delivery_period, date, status, total_amount, orderid) VALUES(:custname, :contact, :product_name, :quantity, :est_amount, :advance_paid, :delivery_period, :date, :status, :total_amount, :orderid)";
    $query=$dbh->prepare($sql);
    $query->bindParam(':custname',$custmorname,PDO::PARAM_STR);
    $query->bindParam(':contact',$contact,PDO::PARAM_STR);
    $query->bindParam(':product_name',$proname,PDO::PARAM_STR);
    $query->bindParam(':quantity',$quantity,PDO::PARAM_STR);
    $query->bindParam(':est_amount',$estamount,PDO::PARAM_STR);
    $query->bindParam(':advance_paid',$advancepaid,PDO::PARAM_STR);
    $query->bindParam(':delivery_period',$delevery,PDO::PARAM_STR);
    $query->bindParam(':date',$date,PDO::PARAM_STR);
    $query->bindParam(':status',$status,PDO::PARAM_STR);
    $query->bindParam(':orderid',$orderid,PDO::PARAM_STR);
    $query->bindParam(':total_amount',$totalamount,PDO::PARAM_STR);
    $query->execute();

}

PDO and binding multiple value sets during insert - recently

Just create your query text wtih ? placeholders as:

INSERT INTO table (firstName, lastName) VALUES (?, ?),(?, ?),(?, ?)

And execute it. Sample code can be:

$data = ['Joe', 'Smith','Fred','Sampson','Lisa','Pearce'];
$placeholders = ['(?, ?)', '(?, ?)', '(?, ?)'];  // but you should define this data according to your data
$query = 'INSERT INTO table (firstName, lastName) VALUES ' . implode(',', $placeholders);
$stmt = $dbh->prepare($query);
$stmt->execute($data);

php PDO insert batch multiple rows with placeholders

First of all, ? symbols are real place-holders (most drivers allow to use both syntaxes, positional and named place-holders). Secondly, prepared statements are nothing but a tool to inject raw input into SQL statements—the syntax of the SQL statement itself is unaffected. You already have all the elements you need:

How to insert multiple rows with a single query
How to generate SQL dynamically
How to use prepared statements with named place-holders.

It's fairly trivial to combine them all:

$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = [];
$insertData = [];
$n = 0;
foreach ($data as $row) {
    $insertQuery[] = '(:memberID' . $n . ', :programID' . $n . ')';
    $insertData['memberID' . $n] = $memberid;
    $insertData['programID' . $n] = $row;
    $n++;
}

if (!empty($insertQuery)) {
    $sql .= implode(', ', $insertQuery);
    $stmt = $db->prepare($sql);
    $stmt->execute($insertData);
}