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:
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:
1 2 3 4 5 | CREATE TABLE departments ( department_no int(11) NOT NULL AUTO_INCREMENT, name varchar(255) DEFAULT NULL, PRIMARY KEY (department_no) ) ENGINE=InnoDB; |
And to create the employees table, you use the following SQL statement:
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE employees ( employee_no int(11) NOT NULL AUTO_INCREMENT, first_name varchar(40) NOT NULL, last_name varchar(40) NOT NULL, birth_date date NOT NULL, gender varchar(1) NOT NULL, hire_date date DEFAULT NULL, department_no int(11) DEFAULT NULL, PRIMARY KEY (employee_no), KEY emp_dept (department_no), CONSTRAINT emp_dept FOREIGN KEY (department_no) REFERENCES departments (department_no) ) ENGINE=InnoDB; |
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:
1 | CREATE DATABASE empdb; |
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:
1 2 3 4 5 | <?php $host='localhost'; $db = 'empdb'; $username = 'root'; $password = ''; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | <?php require_once 'dbconfig.php'; try{ $dsn = "mysql:host=$host;dbname=$db"; $dbh = new PDO($dsn, $username, $password); $sql_create_dept_tbl = <<<EOSQL CREATE TABLE departments( department_no int(11) NOT NULL AUTO_INCREMENT, name varchar(255) DEFAULT NULL, PRIMARY KEY (department_no) ) ENGINE=InnoDB EOSQL; $sql_create_emp_tbl = <<<EOSQL CREATE TABLE employees ( employee_no int(11) NOT NULL AUTO_INCREMENT, first_name varchar(40) NOT NULL, last_name varchar(40) NOT NULL, birth_date date NOT NULL, gender varchar(1) NOT NULL, hire_date date DEFAULT NULL, department_no int(11) DEFAULT NULL, PRIMARY KEY (employee_no), KEY emp_dept (department_no), CONSTRAINT emp_dept FOREIGN KEY (department_no) REFERENCES departments (department_no) ) ENGINE=InnoDB EOSQL; $msg = ''; $r = $dbh->exec($sql_create_dept_tbl); if($r !== false){ $r = $dbh->exec($sql_create_emp_tbl); if($r !== false){ $msg = "Tables are created successfully!<br/>"; }else{ $msg = "Error creating the employees table.<br/>"; } }else{ $msg = "Error creating the departments table.<br/>"; } // display the message if($msg != '') echo $msg; }catch (PDOException $e){ echo $e->getMessage(); } |
How it works.
- First, we created a connection to the MySQL database by instantiating an instance of the
PDOclass and passing the$dsnargument to its constructor. - Second, we specify the SQL statements that creates the
departmentsandemployeestables and put them into string variables:$sql_create_dept_tbland$sql_create_emp_tbl
- Third, we call the
exec()method of the PDO class to execute the SQL statements in sequence. Theexec()method returns the number of affected rows, including0, on success andfalseon failure, therefore we had to use the!==operator to compare its returned value withfalse.
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.