Inserting Data with PDO Prepared Statements

INSERT with named or positional bindings stores user data without concatenating SQL fragments.

The examples focus on the operation itself, including the important distinction: lastInsertId is meaningful only for inserts that generate an ID with the chosen driver.

What It Does

Use the examples below to observe the behavior on a small dataset before extending it to application code.

Before You Start

The examples use PHP 8.2+ features where shown. Database snippets require the corresponding PDO driver, demo tables, and credentials configured outside source code.

Example 1: Basic Use

Validate values first and rely on database constraints for duplicates; do not concatenate user input into SQL.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$email = '[email protected]';
$stmt = $pdo->prepare("INSERT INTO users(email, active) VALUES (:email, :active)");
$stmt->execute(["email" => $email, "active" => 1]);
echo $stmt->rowCount();

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 2: Checking Behavior

Validate values first and rely on database constraints for duplicates; do not concatenate user input into SQL.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$email = '[email protected]';
$stmt = $pdo->prepare("INSERT INTO users(email, active) VALUES (:email, :active)");
$stmt->execute(["email" => $email, "active" => 1]);
echo $stmt->rowCount();

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 3: Practical Example

Validate values first and rely on database constraints for duplicates; do not concatenate user input into SQL.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$email = '[email protected]';
$stmt = $pdo->prepare("INSERT INTO users(email, active) VALUES (:email, :active)");
$stmt->execute(["email" => $email, "active" => 1]);
echo $stmt->rowCount();

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Important Behavior and Edge Cases

Validate business rules before insertion and rely on database constraints for uniqueness.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• INSERT with named or positional bindings stores user data without concatenating SQL fragments.
• Validate business rules before insertion and rely on database constraints for uniqueness.
• lastInsertId is meaningful only for inserts that generate an ID with the chosen driver.

Use Inserting Data with PDO Prepared Statements when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.