Inserting Data into Tables from PDO

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

The steps for inserting a row into a table

To insert a row into a table of the database, 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 create a new connection to the booklib database.
  • Next, prepare an insert statement. The :name is the placeholder that will be replaced by the real publisher name.
  • Then, prepare the INSERT statement for execution by calling the prepare() method of the $pdo instance.
  • 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.

If you did not follow the tutorial that explains how to connect to the database, you can find the connect.php script below:

<?php require_once 'config.php'; function connect($host, $db, $user, $password) { $dsn = "mysql:host=$host;dbname=$db;charset=UTF8"; try { $options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]; return new PDO($dsn, $user, $password, $options); } catch (PDOException $e) { die($e->getMessage()); } } return connect($host, $db, $user, $password);
Code language: HTML, XML (xml)

Inserting multiple rows into a table example

To insert multiple rows into a table, you just need to call execute() method multiple times, each time the method will insert a new row into the table. 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 and insert each row at a time.

Summary

  • Use a prepared statement to insert one or more rows into a table.
Did you find this tutorial useful?