PDO Creating New Tables

Summary: in this tutorial, we will introduce you to employees data model that we will use through our PDO tutorial series and show you how to create new tables using PDO API.

Introducing the Employees data model

We are going to use the employees database through our PDO tutorial series. The following database diagram illustrates the employees data model:

PDO Employees Data Model

There are two tables in the Employees database:

  • departments: stores the department data including department number and department name. Each department has one or more employees.
  • employees: stores employees data including employee number, first name, last name, birth date, gender, hire date and department which the employee belongs to.

Notice that we make the employees data model as simple as possible for the demonstration purpose only. We will use MySQL as the database management system for the PDO tutorial series.

If you don’t know anything about SQL or you want to refresh you SQL knowledge, you can check it out the SQL tutorial section in our website.

To create a new table in a database you use the CREATE TABLE statement. The following SQL statement creates the departments table:

And to create the employees table, you use the following SQL statement:

We execute these SQL statements to create departments and employees tables via MySQL command line tool or GUI tool like MySQL Workbench. However, we will use PDO  API to create tables from the PHP script.

Using PDO to create new tables

First, we need to create a new database in MySQL named empdb to store the employee data using the CREATE DATABASE  statement as follows:

Second, we need to create a new database configuration file dbconfig.php to store the database parameters including hosts, database name, username and password as follows:

Third, we develop a script file that creates new tables in the   empdb database. We will include the dbconfig.php in this script file to access the database configurations.

How it works.

  • First, we created a connection to the MySQL database by instantiating an instance of the PDO class and passing the $dsn argument to its constructor.
  • Second, we specify the SQL statements that creates the departments and employees tables and put them into string variables:   $sql_create_dept_tbl and  $sql_create_emp_tbl
  • Third, we call the exec() method of the PDO class to execute the SQL statements in sequence. The exec() method returns the number of affected rows, including 0, on success and false on failure, therefore we had to use the !== operator to compare its returned value with false.

After executing the script file, you can check the database to see if the departments and employees tables were created.

In this tutorial, you have learned about the employees data model and how to create new tables in the employees database using PDO API.