Inserting Data into Tables from PDO

Summary: in this tutorial, you will learn how to insert one or more rows into a table using PHP PDO.

The steps for inserting a row into a table

To insert a row into a table, you follow these steps:

• First, connect to the database by creating a new PDO object.
• Second, construct the INSERT statement. If you need to pass a value to the INSERT statement, you can use the placeholders in the format :parameter. Later, you can substitute the parameter by its value.
• Third, create a prepared statement by calling the prepare() method of the PDO object. The prepare() method returns an instance of the PDOStatement class.
• Finally, call the execute() method of the prepared statement and pass the values.

Inserting a row into a table example

The following example shows how to insert a new row into the publishers table:

<?php

$pdo = require_once 'connect.php';

// insert a single publisher
$name = 'Macmillan';
$sql = 'INSERT INTO publishers(name) VALUES(:name)';

$statement = $pdo->prepare($sql);

$statement->execute([
	':name' => $name
]);

$publisher_id = $pdo->lastInsertId();

echo 'The publisher id ' . $publisher_id . ' was inserted';Code language: HTML, XML (xml)

How it works.

• First, use the connect.php to connect to the bookldb database.
• Next, construct an SQL INSERT statement. The :name is the placeholder that will be replaced by a publisher name.
• Then, prepare the INSERT statement for execution by calling the prepare() method of the $pdo instance. The prepare() method returns an instance of the PDOStatement class.
• After that, execute the prepared statement by passing the values to the execute() method.
• Finally, get the inserted id by calling the lastInsertId() method of the PDO object.

Note that this tutorial uses the connect.php script developed in the connecting to the database tutorial.

Inserting multiple rows into a table example

To insert multiple rows into a table, you need to call execute() the method multiple times. The method inserts a new row into the table in each call. For example:

<?php

$pdo = require_once 'connect.php';

$names = [
	'Penguin/Random House',
	'Hachette Book Group',
	'Harper Collins',
	'Simon and Schuster'
];

$sql = 'INSERT INTO publishers(name) VALUES(:name)';

$statement = $pdo->prepare($sql);

foreach ($names as $name) {
	$statement->execute([
		':name' => $name
	]);
}Code language: HTML, XML (xml)

In this example, we have a list of publishers stored in the $names array.

To insert these publishers into the publishers table, we iterate over the elements of the $names array using the foreach and insert each element into the table.

Summary

• Use a prepared statement to insert one or more rows into a table.