Creating New Tables

Summary: in this tutorial, you wil learn how to create new tables in the database using PDO.

SQL statements for creating new tables

To create a new table in a database, you use the CREATE TABLE statement. For example, the following CREATE TABLE statement creates the authors table:

CREATE TABLE authors( 
  author_id   INT AUTO_INCREMENT,
  first_name  VARCHAR(100) NOT NULL, 
  middle_name VARCHAR(50) NULL, 
  last_name   VARCHAR(100) NULL,
  PRIMARY KEY(author_id)
);Code language: SQL (Structured Query Language) (sql)

And the following CREATE TABLE statement creates the book_authors table:

CREATE TABLE book_authors (
  book_id   INT NOT NULL, 
  author_id INT NOT NULL, 
  PRIMARY KEY(book_id, author_id), 
  CONSTRAINT fk_book 
    FOREIGN KEY(book_id) 
    REFERENCES books(book_id) ON DELETE CASCADE, 
  CONSTRAINT fk_author 
    FOREIGN KEY(author_id) 
    REFERENCES authors(author_id) ON DELETE CASCADE
);Code language: SQL (Structured Query Language) (sql)

Note that the book_authors table links to the books created from the previous tutorial.

Typically, you execute these CREATE TABLE statements using a MySQL client tool to create the authors and book_authors tables. However, in this tutorial, you will learn how to create them from PHP using PDO.

Using PDO to create new tables

To execute an SQL statement using PDO, you use follow these steps:

• First, connect to the database (MySQL server in this case) by creating a new instance of the PDO class.
• Second, execute an SQL statement by calling the exec() method of the PDO instance.

The exec() method returns the number of affected rows on success or false on failure.

The following script illustrates how to create the authors and book_authors tables:

<?php

// SQL statement for creating new tables
$statements = [
	'CREATE TABLE authors( 
        author_id   INT AUTO_INCREMENT,
        first_name  VARCHAR(100) NOT NULL, 
        middle_name VARCHAR(50) NULL, 
        last_name   VARCHAR(100) NULL,
        PRIMARY KEY(author_id)
    );',
	'CREATE TABLE book_authors (
        book_id   INT NOT NULL, 
        author_id INT NOT NULL, 
        PRIMARY KEY(book_id, author_id), 
        CONSTRAINT fk_book 
            FOREIGN KEY(book_id) 
            REFERENCES books(book_id) 
            ON DELETE CASCADE, 
            CONSTRAINT fk_author 
                FOREIGN KEY(author_id) 
                REFERENCES authors(author_id) 
                ON DELETE CASCADE
    )'];

// connect to the database
$pdo = require 'connect.php';

// execute SQL statements
foreach ($statements as $statement) {
	$pdo->exec($statement);
}
Code language: PHP (php)

Note that this script reuses the connect.php script that creates a connection to the MySQL database server.

How it works.

• First, define an array that holds the SQL statements for creating the authors and book_authors tables.
• Second, connect to the MySQL database server using the connect.php script. The connect.php connects to the bookdb database on the local MySQL Server.
• Third, execute each statement in the $statements array by calling the exec() method of the PDO instance.

After executing the script, you can open the bookdb database in a MySQL client tool to check if the authors and book_authors tables are created successfully.

Summary

• Use the exec() method of the PDO instance to execute the CREATE TABLE statement to create a new table in the database from PHP.