PDO Inserting Data into Tables

Summary: in this tutorial, we will show you how to insert data into database table using PDO API.

The following are steps of how to insert data into a table using PDO:

  • Create a connection to the database by creating a new PDO object.
  • Use a SQL INSERT statement to insert the data into the table.
  • Call the exec() method of the PDO object to execute the INSERT statement. The exec() method returns the number of rows that were affected on success or returns¬†false on failure.

PDO Inserting data into a table example

We will insert a new department into the departments table of the empdb sample database.

First, we create a form for creating a new department. The form consists of one text field that accepts department name and a submit button.

insert department form

Second, when we enter a department name and click the Create Department button, we need to:

  • Validate the input by using the filter_var() function. Check it out the PHP HTML form tutorial to learn how to use the filter_var() function to validate form’s data.
  • Check the input department name to see if it already exists by querying data¬†from the departments table. If the department already exists, we display an error message. Otherwise we insert the new department and display a success message.

To insert a new department into the departments table, we construct an INSERT statement and call the exec() method of the PDO object.

PDO Inserting data into table script

We put the following code at the beginning of the script file:

If the form is not submitted, we display it. If it is submitted, we perform the logic as described above. The following are the functions for the each step:

Validate department function:

Check department exists function:

Insert a new department function:

Create new department function that includes the logic of validating, checking for existence and inserting:

Display error or success message function:

Display form function:

Testing the form:

PHP PDO Insert Sales Department

Insert Sales Department

Failed to insert Sales department because it already exists

Failed to insert Sales department because it already exists

PHP PDO insert Legal department

Insert Legal Department

PHP PDO insert Lega department success

Legal department created successfully

In this tutorial, we have shown you step by step how to insert data into a database table using PHP PDO.