MySQL Insert Data
Insert rows safely using prepared statements. Never concatenate user input into SQL — even for "internal" tools.
Single row
PHP
$stmt = $pdo->prepare(
'INSERT INTO customers (name, email) VALUES (:name, :email)'
);
$stmt->execute([
':name' => 'Ada',
':email' => 'ada@example.com',
]);
echo $pdo->lastInsertId(); // newly assigned id
Positional placeholders
Same thing, less verbose:
PHP
$stmt = $pdo->prepare('INSERT INTO customers (name, email) VALUES (?, ?)');
$stmt->execute(['Ada', 'ada@example.com']);
Bulk insert — reuse one prepared statement
PHP
$stmt = $pdo->prepare('INSERT INTO customers (name, email) VALUES (?, ?)');
foreach ($newUsers as $u) {
$stmt->execute([$u->name, $u->email]);
}
Multi-row VALUES — fastest path
When you need maximum throughput:
PHP
$rows = [['Ada', 'ada@…'], ['Linus', 'linus@…'], ['Grace', 'grace@…']];
$marks = implode(',', array_fill(0, count($rows), '(?, ?)'));
$flat = array_merge(...$rows);
$pdo->prepare("INSERT INTO customers (name, email) VALUES $marks")->execute($flat);
Transactions
Wrap multi-statement work so it's all-or-nothing:
PHP
$pdo->beginTransaction();
try {
$pdo->prepare('INSERT INTO orders (customer_id, total) VALUES (?, ?)')
->execute([$customerId, $total]);
$orderId = $pdo->lastInsertId();
foreach ($items as $i) {
$pdo->prepare('INSERT INTO order_items (order_id, product_id, qty) VALUES (?, ?, ?)')
->execute([$orderId, $i->productId, $i->qty]);
}
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}
Tip: The driver caches prepared statements — calling
prepare() inside a loop is fine as long as the SQL is identical. PDO recognises the cache key.Example
Example
<?php
$stmt = $pdo->prepare('INSERT INTO customers (name, email) VALUES (?, ?)');
$stmt->execute(['Ada', 'ada@example.com']);
echo $pdo->lastInsertId();
Try it Yourself »
Exercise
Method to retrieve last auto-id.
$pdo->
()
camelCase; 12 chars.
Discussion
Loading…