Inserting a Record in a MySQL Database Table Using PDO in PHP

The following code example shows Inserting a Record in a MySQL Database Table Using PDO in PHP.

At first, we need to create a connection with the MySQL server database. Hence, here we use the require() method and pass the name of the PHP file connecting_database.php. When a user enters the values and clicks on the Insert button, the entered values are retrieved using the $_POST variables. Furthermore, here we are using the prepare() statement for preparing the SQL statement before executing it. After that, we call the execute() method that binds the parameter values and executes the query.

The Program for Inserting a Record in a MySQL Database Table Using PDO in PHP – Integrity Constraint Violation

The following code displays an HTML form to read the values from the user. Also, there is a button with the text Insert and the name ‘submit’. The isset() method verifies whether the user has pressed the Insert button. Otherwise, the script will run before the user could enter any value. So here you can insert records one by one. However, it is also possible to insert several rows at once.

<html>
  <head>
    <title>Inserting Records</title>
  </head>
  <body>
    <form name="Insert" method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
      <table border="0">
	<tr>
          <td>Customer ID</td><td><input type="text" name="t1"/></td>
        </tr>
	<tr>
          <td>Customer Name</td><td><input type="text" name="t2"/></td>
        </tr>
	<tr>
          <td>Customer City</td><td><input type="text" name="t3"/></td>
        </tr>
	<tr>
          <td><input type="submit" name="submit" value="Insert"/></td>
        </tr>
      </table>
    </form>
<?php
 require('connecting_database.php');
 if(isset($_POST['submit']))
 {
   $a=$_POST['t1'];
   $b=$_POST['t2'];
   $c=$_POST['t3'];
   try{
        $sql='insert into customer values(?, ?, ?);';
        $statement=$con->prepare($sql);
        $statement->execute(array($a, $b, $c)) or die(print_r($con->errorInfo(), true));
      }
      catch(PDOException $ex)
      {
              die('Error Inserting record: '.$ex->getMessage());
      }
 }
?>

Output

The above program is inserting a record in the customer table whose structure is shown below.

**The Customer Database Table Before Execution of Above PHP Script**

When you run the above script, it displays the following form. It enables you to provide the values to be inserted.

When you click on the Insert button, the Insert command of the SQL gets executed. So, you can view the newly inserted row in the customer table using phpMyAdmin.

**The Result of Inserting a Record in a MySQL Database Table Using PDO in PHP**

Moreover, when you try to insert the record with the same value for cust_id, it results in an Integrity Constraint Violation exception.

**Inserting Record with Same Customer ID**

So, the record will not be inserted!

Inserting a Record in a MySQL Database Table Using PDO in PHP - Integrity Constraint Violation — **Inserting a Record in a MySQL Database Table Using PDO in PHP – Integrity Constraint Violation**

The Program for Inserting a Record in a MySQL Database Table Using PDO in PHP – Integrity Constraint Violation

Further Reading