Inserting records with INSERT
Learn to insert a new record with PDO and a prepared statement.
Inserting a new record with INSERT
To add a new record you run an SQL INSERT with a prepared statement. First prepare() builds the query template; the ? marks are parameter slots.
Then execute() passes the parameters as an array and runs the query. This approach is safe against SQL injection.
To get the new record's ID, call lastInsertId(); it returns the latest value of the AUTOINCREMENT column.
execute() parameters
In a prepared statement the ? placeholders take parameters in order. execute() accepts an array; the values must match the slot order.
For example, in INSERT INTO users (name, email) VALUES (?, ?) the first ? is name and the second is email.
PDO binds the values safely; you do not deal with quotes or escaping.
Exercise Insert a new user and print the ID
In the skeleton below, use prepare, execute and lastInsertId to insert a new record with 'Alex', 'alex@example.com' and print the new ID.
Show example solution
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT)");
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(['Alex', 'alex@example.com']);
echo $pdo->lastInsertId() . "\n";Test yourself
-
Which method returns the ID of the newly inserted record?
- prepare()
- lastInsertId()
- fetchColumn()
- rowCount()
Show answer
lastInsertId() — lastInsertId() returns the new AUTOINCREMENT ID.
-
Run the prepared statement
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)"); $stmt->____(['Alex', 'alex@example.com']);Show answer
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)"); $stmt->execute(['Alex', 'alex@example.com']); -
Match PDO methods to their roles
- prepare()
- Prepares the statement
- execute()
- Runs the statement
- lastInsertId()
- Returns the new ID
- query()
- Runs a query directly