Insert Data

Inserting Data into MySQL with PHP PDO Prepared Statements

To add new records into a MySQL table from PHP, prepare an INSERT INTO SQL statement with placeholders and bind your values using PDOStatement::execute(). Prepared statements separate SQL syntax from user data completely, blocking SQL injection attacks even when users type quotes or SQL keywords.

Inserting a Single Row with Named Placeholders

Use named placeholders like :full_name and :email inside $pdo->prepare(), then pass an associative array of values to $stmt->execute().

flowchart LR
    A["$pdo->prepare(INSERT SQL)"] --> B["Send Parameterized Template to MySQL"]
    B --> C["$stmt->execute(boundValues)"]
    C --> D["Row Inserted Safely"]

<?php
declare(strict_types=1);

function createCustomer(PDO $pdo, string $fullName, string $email): int
{
    $sql = 'INSERT INTO users (full_name, email, status) VALUES (:full_name, :email, :status)';
    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        ':full_name' => trim($fullName),
        ':email' => strtolower(trim($email)),
        ':status' => 1,
    ]);

    return (int) $pdo->lastInsertId();
}

Handling Duplicate Key Violations Gracefully

Because users.email has a UNIQUE index, inserting an existing email throws a PDOException with SQLSTATE code '23000'. Catch PDOException, inspect $e->getCode() === '23000', and return a helpful validation message ("That email is already registered") instead of crashing with a 500 error.

Common Mistakes to Avoid

• Concatenating PHP variables into SQL strings: Writing "INSERT INTO users (email) VALUES ('$email')" opens your database to catastrophic SQL injection. Always use prepared placeholders.
• Putting quotes around placeholders: Write VALUES (:email)—never VALUES (':email'). Quotes turn the placeholder into a literal text string.
• Calling $pdo->exec() for user-supplied inserts: $pdo->exec() runs raw unparameterized SQL and should never receive untrusted variables.

Frequently Asked Questions

Can I reuse the same prepared $stmt for multiple inserts?

Yes. Calling $stmt = $pdo->prepare(...) once and calling $stmt->execute($row) inside a loop lets MySQL reuse the compiled query plan.

What does $stmt->rowCount() return after an INSERT?

It returns the number of rows inserted—typically 1 for a single-row insert.

Are positional ? placeholders safer or worse than :named placeholders?

Both provide identical SQL injection protection. Named :placeholders are easier to read and refactor in queries with four or more columns.