PHP PDO INSERT: Add MySQL Record & lastInsertId()

To add a MySQL record with PDO, prepare an INSERT query, bind the values and execute the statement. If the table uses an auto-increment ID, lastInsertId() can return the ID generated for the new record.

<?php
require 'config.php';

$name='Alex R';
$class='Five';
$mark=70;
$gender='Female';

$sql="INSERT INTO student
      (name,class,mark,gender)
      VALUES
      (:name,:class,:mark,:gender)";

try{
    $stmt=$dbo->prepare($sql);

    $stmt->bindValue(
        ':name',
        $name,
        PDO::PARAM_STR
    );

    $stmt->bindValue(
        ':class',
        $class,
        PDO::PARAM_STR
    );

    $stmt->bindValue(
        ':mark',
        $mark,
        PDO::PARAM_INT
    );

    $stmt->bindValue(
        ':gender',
        $gender,
        PDO::PARAM_STR
    );

    $stmt->execute();

    $new_id=$dbo->lastInsertId();

    echo 'Record added. Student ID: '.$new_id;
}catch(PDOException $e){
    error_log($e->getMessage());
    echo 'Unable to add the record.';
}

The PDO connection object $dbo is created in the config.php connection file.

Show Table of Contents

PDO INSERT Query
Collect Form Data
Validate Input before INSERT
PDO lastInsertId()
Pass Values with execute()
Insert Multiple Rows
Insert Multiple Rows in a Transaction
Handle INSERT Errors
Insert a Date
Insert NULL into a Date Column
Insert Form Data into MySQL or SQLite
Video Tutorial
Common Problems
Related PDO Tutorials
FAQs

PDO INSERT Query with Named Parameters Top ↑

The SQL INSERT statement adds a new row to a table. In PDO, placeholders can be used instead of placing PHP variable values directly inside the SQL.

$sql="INSERT INTO student
      (name,class,mark,gender)
      VALUES
      (:name,:class,:mark,:gender)";

$stmt=$dbo->prepare($sql);

The values are supplied separately:

$stmt->bindValue(
    ':name',
    $name,
    PDO::PARAM_STR
);

$stmt->bindValue(
    ':mark',
    $mark,
    PDO::PARAM_INT
);

$stmt->execute();

This keeps supplied data separate from the SQL statement.

Collect Form Data before the PDO INSERT Top ↑

Values submitted through an HTML form can be collected with $_POST and validated before the INSERT is executed.

$name=trim($_POST['name'] ?? '');
$class=trim($_POST['class'] ?? '');
$gender=trim($_POST['gender'] ?? '');

$mark=filter_input(
    INPUT_POST,
    'mark',
    FILTER_VALIDATE_INT
);

See the PHP form tutorials for collecting and processing form values.

Validate Data before Inserting It Top ↑

Prepared statements keep supplied values separate from SQL syntax, but application data should still be validated before it is stored.

if(
    $name==='' ||
    $class==='' ||
    $gender==='' ||
    $mark===false ||
    $mark<0 ||
    $mark>100
){
    exit('Please enter valid student data.');
}

Validation checks whether values are acceptable for the application. PDO parameter binding protects the structure of the SQL query. These are separate tasks.

A more complete form with validation is available in the PHP signup tutorial.

Get the New Record ID with lastInsertId() Top ↑

When the table uses an auto-increment field, MySQL generates a new ID while inserting the record.

After the INSERT succeeds, call lastInsertId() on the PDO connection:

$stmt->execute();

$new_id=$dbo->lastInsertId();

echo 'New student ID: '.$new_id;

Example:

New student ID: 37

lastInsertId() belongs to the PDO connection object $dbo. Methods such as execute() and rowCount() belong to the PDO statement.

See the MySQL insert ID tutorial for more about retrieving the generated ID.

Pass INSERT Values Directly to execute() Top ↑

Parameters can also be supplied as an array when the prepared statement is executed.

$sql="INSERT INTO student
      (name,class,mark,gender)
      VALUES
      (:name,:class,:mark,:gender)";

$stmt=$dbo->prepare($sql);

$stmt->execute([
    ':name' => 'Alice',
    ':class' => 'Five',
    ':mark' => 95,
    ':gender' => 'Female'
]);

$new_id=$dbo->lastInsertId();

echo 'Student ID: '.$new_id;

When the parameter type should be specified explicitly, such as for an integer, use bindValue() with the corresponding PDO parameter type.

Insert Multiple Rows using One Prepared Statement Top ↑

When several rows use the same INSERT structure, prepare the statement once and execute it repeatedly with different values.

$students=[
    [
        'name' => 'Bob',
        'class' => 'Six',
        'mark' => 89,
        'gender' => 'Male'
    ],
    [
        'name' => 'Charlie',
        'class' => 'Seven',
        'mark' => 76,
        'gender' => 'Female'
    ]
];

$sql="INSERT INTO student
      (name,class,mark,gender)
      VALUES
      (:name,:class,:mark,:gender)";

$stmt=$dbo->prepare($sql);

foreach($students as $student){
    $stmt->bindValue(
        ':name',
        $student['name'],
        PDO::PARAM_STR
    );

    $stmt->bindValue(
        ':class',
        $student['class'],
        PDO::PARAM_STR
    );

    $stmt->bindValue(
        ':mark',
        $student['mark'],
        PDO::PARAM_INT
    );

    $stmt->bindValue(
        ':gender',
        $student['gender'],
        PDO::PARAM_STR
    );

    $stmt->execute();

    echo 'Student ID: '.$dbo->lastInsertId().'<br>';
}

Example output:

Student ID: 40
Student ID: 41

Insert Multiple Records inside a PDO Transaction Top ↑

If several related records must all be inserted successfully, use a PDO transaction. If one operation fails, the completed inserts can be rolled back.

$students=[
    [
        'name' => 'John Doe',
        'class' => 'Six',
        'mark' => 85,
        'gender' => 'Male'
    ],
    [
        'name' => 'Jane Doe',
        'class' => 'Seven',
        'mark' => 90,
        'gender' => 'Female'
    ]
];

$sql="INSERT INTO student
      (name,class,mark,gender)
      VALUES
      (:name,:class,:mark,:gender)";

try{
    $dbo->beginTransaction();

    $stmt=$dbo->prepare($sql);

    foreach($students as $student){
        $stmt->bindValue(
            ':name',
            $student['name'],
            PDO::PARAM_STR
        );

        $stmt->bindValue(
            ':class',
            $student['class'],
            PDO::PARAM_STR
        );

        $stmt->bindValue(
            ':mark',
            $student['mark'],
            PDO::PARAM_INT
        );

        $stmt->bindValue(
            ':gender',
            $student['gender'],
            PDO::PARAM_STR
        );

        $stmt->execute();
    }

    $dbo->commit();

    echo 'Records inserted successfully.';
}catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());

    echo 'Unable to insert the records.';
}

PDO Transactions and Rollback

Handle PDO INSERT Errors Top ↑

A database constraint, duplicate unique value or invalid database operation can cause an INSERT to fail.

try{
    $stmt=$dbo->prepare($sql);
    $stmt->execute();

    $new_id=$dbo->lastInsertId();
}catch(PDOException $e){
    error_log($e->getMessage());

    echo 'Unable to add the record.';
}

Do not display raw PDO exception messages or errorInfo() output to visitors on a production site. Log the detailed error on the server instead.

PDO Error Handling

Validate the Student Mark Top ↑

if($mark<0 || $mark>100){
    exit('Mark must be between 0 and 100.');
}

Application validation should normally happen before sending the INSERT statement to MySQL.

Insert a Date into MySQL using PDO Top ↑

Dates stored in MySQL should use a format supported by the target date column. The SQL section has more examples on inserting date values into MySQL.

For a MySQL DATE column, PHP can create a value in Y-m-d format:

$dt=date('Y-m-d');

$sql="INSERT INTO table_name
      (dt)
      VALUES
      (:dt)";

$stmt=$dbo->prepare($sql);

$stmt->bindValue(
    ':dt',
    $dt,
    PDO::PARAM_STR
);

$stmt->execute();

Convert a Date before INSERT Top ↑

$date=new DateTime($dt);
$dt=$date->format('Y-m-d');

Validate user-entered dates before storing them in the database.

Insert NULL into a MySQL Column Top ↑

If the database column permits SQL NULL values and no date is available, bind a PHP null using PDO::PARAM_NULL.

$dt=null;

$stmt=$dbo->prepare(
    "INSERT INTO table_name
     (dt)
     VALUES
     (:dt)"
);

$stmt->bindValue(
    ':dt',
    $dt,
    PDO::PARAM_NULL
);

$stmt->execute();

Blank Form Date: Store NULL Top ↑

$dt=trim($_POST['dt'] ?? '');

if($dt===''){
    $dt=null;
}else{
    $date=DateTimeImmutable::createFromFormat(
        '!Y-m-d',
        $dt
    );

    $errors=DateTimeImmutable::getLastErrors();

    if(
        $date===false ||
        (
            $errors!==false &&
            (
                $errors['warning_count']>0 ||
                $errors['error_count']>0
            )
        )
    ){
        exit('Invalid date.');
    }

    $dt=$date->format('Y-m-d');
}

$stmt=$dbo->prepare(
    "INSERT INTO table_name
     (dt)
     VALUES
     (:dt)"
);

$stmt->bindValue(
    ':dt',
    $dt,
    $dt===null ? PDO::PARAM_NULL : PDO::PARAM_STR
);

$stmt->execute();

Insert Form Data into MySQL or SQLite using PDO Top ↑

The prepared-statement approach can also be used with PDO SQLite. The PDO interface is similar, although connection details and database-specific SQL can differ.

Insert HTML Form Data using PDO SQLite and MySQL

PDO INSERT and lastInsertId() Video Top ↑

PHP PDO parameterized INSERT query and lastInsertId()

Podcast on MySQL database management using PHP PDO

Common PDO INSERT Problems Top ↑

Putting Form Values Directly into SQL Top ↑

Avoid constructing the INSERT query by joining external values into the SQL. Use placeholders and prepared statements.

Calling lastInsertId() on the Statement Top ↑

lastInsertId() belongs to the PDO connection:

$new_id=$dbo->lastInsertId();

Printing Raw Database Errors Top ↑

Log technical database exceptions and show a short message instead of exposing SQL or database details.

Using Prepared Statements without Validation Top ↑

Prepared statements protect the SQL structure, but they do not decide whether a name, mark, date or other value is valid for the application.

Inserting Several Dependent Records without a Transaction Top ↑

If all inserts must succeed together, use a transaction so completed inserts can be rolled back if a later operation fails.

Binding NULL as a String Top ↑

Use a PHP null value together with PDO::PARAM_NULL when the database column should receive SQL NULL.

PDO rowCount() PDO Update PDO Delete

PDO Transactions PDO Errors PDO and SQL Injection

Download the complete sample scripts from the main PDO tutorial.

Sample Student Table SQL Dump

Frequently Asked Questions Top ↑

Q1: How do I insert a MySQL record using PHP PDO?

Prepare an INSERT statement containing placeholders, bind or supply the values separately and call execute() on the PDOStatement.

Q2: Why should I use a prepared statement for PDO INSERT queries?

Prepared statements keep supplied values separate from the SQL statement and allow values to be bound using the expected parameter type.

Q3: How do I get the auto-increment ID after a PDO INSERT?

After the INSERT succeeds, call lastInsertId() on the PDO connection object.

Q4: Can I insert multiple records using the same prepared statement?

Yes. Prepare the INSERT once and execute it repeatedly with a new set of values for each record.

Q5: When should I use a transaction for PDO INSERT queries?

Use a transaction when several related inserts must either all succeed or all be rolled back together if an error occurs.

Q6: How do I insert NULL into a MySQL column using PDO?

Use a PHP null value and bind it with PDO::PARAM_NULL when the database column permits NULL.

Q7: Do prepared statements replace input validation?

No. Prepared statements separate values from SQL syntax, while validation checks whether those values satisfy the application's rules.

PDO rowCount() PDO Update

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: