Palma posted

PDO Prepared Inserts multiple rows in single query

steave
answered Apr 28 '23 00:00

Sure, here's a rewrite:

Using PDO prepared statements, it's possible to perform a bulk insert operation to insert multiple rows in a single query. This is also known as a batch insert operation. Here's an example of how you can do it:



// Define the data to be inserted
$data = array(
    array('John', 'Doe', 25),
    array('Jane', 'Smith', 30),
    array('Bob', 'Johnson', 40)
);

// Set up a connection to the database using PDO
$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'myusername';
$password = 'mypassword';
$options = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
$dbh = new PDO($dsn, $username, $password, $options);

// Prepare an insert statement with placeholders
$stmt = $dbh->prepare('INSERT INTO mytable (first_name, last_name, age) VALUES (?, ?, ?)');

// Execute the insert statement for each row of data
foreach ($data as $row) {
    $stmt->execute($row);
}

In this example, the $data array contains the data to be inserted, with each row represented as an array of values. The prepare () method is used to create a prepared statement with placeholders for the values to be inserted. The execute() method is then called for each row of data, with the array of values passed as a parameter.

It's important to note that using a bulk insert operation can be more efficient than executing individual insert statements for each row, as it reduces the overhead of multiple round trips to the database. However, it's important to consider the size of the data being inserted and the limitations of your database server, as a large bulk insert operation can still cause performance issues if not properly optimized.

Edit Answer

Palma posted

PDO Prepared Inserts multiple rows in single query

steave answered Apr 28 '23 00:00

Latest Questions

Insert link

Insert image

steave
answered Apr 28 '23 00:00