PHP Pdo adding record to MySQL database table with lastInsertId

We will learn how to insert or add record to a MySQL table by using PDO in PHP. Here we will only work on the database query and record insert part.

PHP PDO Parameterized query to insert data to MySQL table and getting lastInsertId() of the record

To keep the script simple and easy to understand we have omitted the data validation part. The table structure has unique constraints of userid so duplicate userid is not accepted. If you try to add same userid ( already exist) then you will get error message.

In your downloaded script there is a file pdo-insert.php . This file displays the form and after submission of the form data is posted to pdo-insert2.php file for storing in MySQL table.

Before using this page you must create the tables by using sql_dump file.

The detail of signup page with data validation is shown here. php_signup.php.

collecting form data

Here we are setting value for our variables, the same we can get from Form submission.

$name=$_POST['name'];
$class=$_POST['class'];
$mark=$_POST['mark'];
$gender=$_POST['gender'];

We can set the variables with value for our example.

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

LastInsertID

This gives you the value of the auto increment Id field. The value is created once a new record is added to the table. You can read more on this at mysql_insert_id() . Here is our datbase connection object declared inside config.php file. We will directly go to query part.

require "config.php"; // Database Connection, $dbo is available here

$query="INSERT INTO student SET name=:name,
	class=:class, mark=:mark, gender=:gender";
$step=$dbo->prepare();
$step->bindParam(':name',$name,PDO::PARAM_STR, 15);
$step->bindParam(':class',$class,PDO::PARAM_STR, 15);
$step->bindParam(':mark',$mark,PDO::PARAM_INT,5);
$step->bindParam(':gender',$gender,PDO::PARAM_STR,10);

if($step->execute()){
$mem_id=$dbo->lastInsertId(); 
echo " Thanks .. Your Membership id = $mem_id ";
}
else{
echo " Not able to add data please contact Admin ";
}

This code connects to a database using config.php and inserts a record into the student table. It uses prepared statements to securely bind user-provided values (name, class, mark, and gender) to the query. After executing the statement, it retrieves the lastInsertId() to get the ID of the newly inserted record. If the query executes successfully, it displays the new membership ID. If it fails, an error message is shown.

Example 1: Using Named Parameters in PDO Insert

This example demonstrates how to use named parameters for inserting user data securely into a database.

try {
 $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']);
 $mem_id=$dbo->lastInsertId(); 
 echo " Thanks .. Your Membership id = $mem_id ";
}
 catch (PDOException $e) {
   echo "Error: " . $e->getMessage();
}

Output:

Thanks .. Your Membership id = 37

Example 2: Inserting Multiple Rows Using Loop

try{
$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->execute($student);
	echo "<br> Student ID : ". $dbo->lastInsertId();
}
}
 catch (PDOException $e) {
   echo "Error: " . $e->getMessage();
}

Output

Student ID : 40
Student ID : 41

Transaction and Rollback Example Using All Columns in PDO

Transaction: Inserts two records into the student table.
Rollback: Reverts changes if an error occurs, ensuring data integrity.
More on PDO Transactions

try {
    $dbo->beginTransaction(); // Start the transaction

    // First Insert
    $query1 = "INSERT INTO student (name, class, mark, gender) 
		VALUES ('John Doe', 'Six', 85, 'Male')";
    $dbo->exec($query1);

    // Second Insert
    $query2 = "INSERT INTO student (name, class, mark, gender) 
		VALUES ('Jane Doe', 'Seven', 90, 'Female')";
    $dbo->exec($query2);

    $dbo->commit(); // Commit the transaction if both queries succeed
    echo "Data inserted successfully!";
} catch (Exception $e) {
    $dbo->rollBack(); // Roll back the transaction on error
    echo "Transaction failed: " . $e->getMessage();
}

Using PHP to Insert Data into SQLite and MySQL Databases via an HTML Form

Learn how to create an HTML form to insert data into both SQLite and MySQL databases using PHP. The same code works for both databases; only the connection object differs. For SQLite, the object is $my_conn, while for MySQL, it's $dbo. This guide demonstrates the process of submitting and storing data securely through prepared statements.

Full article to store user input in database

Printing the error message.

This example shows how to handle errors using exceptions in PDO during the insert process.

try {
    $stmt->execute($data);
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

Output:

Error: SQLSTATE[23000]: Integrity constraint violation

To get more meaning full information from the database we have to use errorinfo() function. We will modify the part of the above code like this.

if($step->execute()){
$mem_id=$dbo->lastInsertId(); 
echo " Thanks .. Your Membership id = $mem_id ";
}
else{
echo " Not able to add data please contact Admin ";
print_r($step->errorInfo()); 

}

Error Handling for Mark Validation in PDO Insert Query

Mark Check: Before executing the query, we check if the mark is greater than 100. If it is, an exception is thrown with a custom error message.

require "config.php"; // Database Connection,  is available here

try {
    if ($mark > 100) {
        throw new Exception("Mark cannot be greater than 100. Please check the input.");
    }

    $query = "INSERT INTO student SET name=:name, 
		class=:class, mark=:mark, gender=:gender";
    $step = $dbo->prepare($query);
    $step->bindParam(':name', $name, PDO::PARAM_STR, 15);
    $step->bindParam(':class', $class, PDO::PARAM_STR, 15);
    $step->bindParam(':mark', $mark, PDO::PARAM_INT, 5);
    $step->bindParam(':gender', $gender, PDO::PARAM_STR, 10);

    if ($step->execute()) {
        $mem_id = $dbo->lastInsertId();
        echo "Thanks .. Your Membership id = $mem_id";
    } else {
        echo "Not able to add data, please contact Admin";
    }
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}

Storing error message in a string and passing to front end

$str='';
$dbo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); 
$a=$step->errorInfo();
while (list ($key, $val) = each ($a)  ) {
$str .= "<br>$key -> $val <br>";
}
$msg .= " Not able to add data please contact Admin $str";

Adding current Date

In the above code you can add current date like this ( only changes )

$dt=date("Y-m-d");// Today's date 
$step->bindParam(':dt',$dt,PDO::PARAM_STR,10);

Changing date format to store in MySQL table

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

Here $dt is the variable storing date field value.

To add null to a date field

If the date field is set to accept null data then here is the query to add

$dt=null; // make it null 
$step->bindParam(':dt',$dt,PDO::PARAM_STR,10);

If you are receiving date field as blank from a form then use this PHP script.

if(strlen($dt) >2){
$date = new DateTime($dt_approved);
$dt=$date->format('Y-m-d');
}else{
$dt=null;
}
$step=$dbo->prepare("insert into table_name(dt) values(:dt)");
$step->execute();

Download Zip file to test your PHP PDO script

Podcast on MySQL database management using PHP PDO

Sample Student table SQL dump
PDO References rowcount() Update record

Subscribe to our YouTube Channel here

PDO inserting record to MySQL table