0
Follow
2
View

PDO bindParam() Multiple insert foreach [duplicate]

luyeku55 注册会员
2023-01-25 01:57

You can also go this way:

rows)-1);

$sql = "INSERT INTO `table`(col1,col2,col3) VALUES $qmarks";
$vals = array();
foreach($rows as $row)
    $vals = array_merge($vals, $row);
$db->prepare($sql)->execute($vals);

To be honest, I don't know which one will be faster, all depends on the delay between mysql and the php server.

dicongyan 注册会员
2023-01-25 01:57
/* test.php */

rows = array(
    array(
    'name' => 'balasubramani',
    'status' => 1
    ),
    array(
    'name' => 'balakumar',
    'status' => 1
    ),
    array(
    'name' => 'mani',
    'status' => 1
    )
);

var_dump($obj->insertMultiple($table,$rows));
?>

/* Database.php */
dbh = new PDO('sql:host='.$this->host.';dbname='.$this->database.'', $this->user, $this->pass);
            //print "Connected Successfully";
        } 
        catch (PDOException $e) {
            print "Error!: " . $e->getMessage() . "
"; die(); } } /* Insert Multiple Rows in a table */ public function insertMultiple($table,$rows){ $this->dbh->beginTransaction(); // also helps speed up your inserts. $insert_values = array(); foreach($rows as $d){ $question_marks[] = '(' . $this->placeholders('?', sizeof($d)) . ')'; $insert_values = array_merge($insert_values, array_values($d)); $datafields = array_keys($d); } $sql = "INSERT INTO $table (" . implode(",", $datafields ) . ") VALUES " . implode(',', $question_marks); $stmt = $this->dbh->prepare ($sql); try { $stmt->execute($insert_values); } catch (PDOException $e){ echo $e->getMessage(); } return $this->dbh->commit(); } /* placeholders for prepared statements like (?,?,?) */ function placeholders($text, $count=0, $separator=","){ $result = array(); if($count > 0){ for($x=0; $x<$count; $x++){ $result[] = $text; } } return implode($separator, $result); } } ?>

The above code should be good solution for Inserting Multiple Records using PDO.

dududu55 注册会员
2023-01-25 01:57

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.

wangjz38257372 注册会员
2023-01-25 01:57

I would do it the first way, prepare the statement with one row of parameter placeholders, and insert one row at a time with execute.

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

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

It's not quite as fast as doing multiple rows in a single insert, but it's close enough that you will probably never notice the difference.

And this has the advantage that it's very easy to work with the code. That's why you're using PHP anyway, for the developer efficiency, not the runtime efficiency.

If you have many rows (hundreds or thousands), and performance is a priority, you should consider using LOAD DATA INFILE.

About the Author

Question Info

Publish Time
2023-01-25 01:57
Update Time
2023-01-25 01:57