SQLite PHP: Creating Tables

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

This tutorial begins where the “Connecting to SQLite Databases in PHP” tutorial left off.

To create tables in an SQLite database from PHP, you follow these steps:

Step 1. Open a connection to the SQLite database:

$pdo = new \PDO($dsn);Code language: PHP (php)

Step 2. Execute a CREATE TABLE statement by calling the exec() method of the $pdo instance to create the table:

$pdo->exec($sql_create_table);Code language: PHP (php)

Creating tables in PHP

We’ll show you how to create two new tables: projects and tasks in the my.db SQLite database.

Here are SQL statements to create the projects and tasks tables:

CREATE TABLE IF NOT EXISTS projects (
    project_id   INTEGER PRIMARY KEY,
    project_name TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS tasks (
    task_id        INTEGER PRIMARY KEY,
    task_name      TEXT    NOT NULL,
    completed      INTEGER NOT NULL,
    start_date     TEXT,
    completed_date TEXT,
    project_id     INTEGER NOT NULL,
    FOREIGN KEY (project_id)
        REFERENCES projects (project_id) 
           ON UPDATE CASCADE
           ON DELETE CASCADE
);Code language: SQL (Structured Query Language) (sql)

Step 1. Create a new file in the project directory namedcreate-tables.php.

Step 2. Add the following code to the create-tables.php file:

<?php

require_once 'config.php';

$statements = [
    'CREATE TABLE IF NOT EXISTS projects (
        project_id   INTEGER PRIMARY KEY,
        project_name TEXT NOT NULL
    )',
    'CREATE TABLE IF NOT EXISTS tasks (
        task_id INTEGER PRIMARY KEY,
        task_name  VARCHAR (255) NOT NULL,
        completed  INTEGER NOT NULL,
        start_date TEXT,
        completed_date TEXT,
        project_id VARCHAR (255),
        FOREIGN KEY (project_id)
            REFERENCES projects(project_id) 
                ON UPDATE CASCADE
                ON DELETE CASCADE)'
];
      

// connect to the SQLite databse
$dsn = "sqlite:$db";

// create a PDO instance
try {
    $pdo = new \PDO($dsn);

    // create tables
    foreach($statements as $statement){
        $pdo->exec($statement);
    }
} catch(\PDOException $e) {
    echo $e->getMessage();
}Code language: PHP (php)

How it works.

First, load the config.php file:

require_once 'config.php';Code language: PHP (php)

Note that the config.php file contains a variable that stores the path of the SQLite database file:

<?php

$db = './database/my.db';Code language: HTML, XML (xml)

Second, define an array that holds the CREATE TABLE statements for creating the projects and tasks tables:

$statements = [
    'CREATE TABLE IF NOT EXISTS projects (
        project_id   INTEGER PRIMARY KEY,
        project_name TEXT NOT NULL
    )',
    'CREATE TABLE IF NOT EXISTS tasks (
        task_id INTEGER PRIMARY KEY,
        task_name  VARCHAR (255) NOT NULL,
        completed  INTEGER NOT NULL,
        start_date TEXT,
        completed_date TEXT,
        project_id VARCHAR (255),
        FOREIGN KEY (project_id)
            REFERENCES projects(project_id) 
                ON UPDATE CASCADE
                ON DELETE CASCADE)'
];Code language: PHP (php)

Third, define a variable that holds the data source name (dsn):

$dsn = "sqlite:$db";Code language: PHP (php)

Fourth, open a connection to the SQLite database file:

$pdo = new \PDO($dsn);Code language: PHP (php)

Fifth, iterate over statements in the $statements array, and execute each to create a new table:

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

Sixth, display an error message if any exception occurred during the table creation process:

echo $e->getMessage();Code language: PHP (php)

Step 3. Run the create-tables.php script.

Verifying tables

The following steps show you how to verify table creation using the sqlite3 shell:

Step 1. Open your terminal and navigate to the database directory.

Step 2. Connect to the my.db database using the sqlite3 shell:

sqlite3 my.dbCode language: PHP (php)

Step 3. Show all tables in the my.db database:

.tablesCode language: PHP (php)

Output:

projects  tasksCode language: PHP (php)

The output indicates that the projects and tasks tables have been created successfully.

Summary

• Run the CREATE TABLE statement by calling the exec() method of the PDO object to create a new table in an SQLite database.