{"id":907,"date":"2016-05-15T09:03:51","date_gmt":"2016-05-15T02:03:51","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=907"},"modified":"2024-10-28T20:50:05","modified_gmt":"2024-10-28T13:50:05","slug":"create-tables","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-php\/create-tables\/","title":{"rendered":"SQLite PHP: Creating Tables"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create new tables in the SQLite database using PHP PDO.<\/p>\n\n\n\n

This tutorial begins where the “Connecting to SQLite Databases in PHP<\/a>” tutorial left off.<\/p>\n\n\n\n

To create tables in an SQLite database from PHP, you follow these steps:<\/p>\n\n\n\n

Step 1. Open a connection to the SQLite database:<\/p>\n\n\n

$pdo = new<\/span> \\PDO($dsn);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Execute a CREATE TABLE <\/code>statement by calling the exec()<\/code> method of the $pdo<\/code> instance to create the table:<\/p>\n\n\n
$pdo->exec($sql_create_table);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Creating tables in PHP<\/h2>\n\n\n\n
We’ll show you how to create two new tables: projects <\/code>and tasks<\/code> in the my.db<\/code> SQLite database. <\/p>\n\n\n\n
Here are SQL statements to create the projects<\/code> and tasks<\/code> tables:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> projects (\n    project_id   INTEGER<\/span> PRIMARY KEY<\/span>,\n    project_name TEXT<\/span>    NOT<\/span> NULL<\/span>\n);\n\nCREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> tasks (\n    task_id        INTEGER<\/span> PRIMARY KEY<\/span>,\n    task_name      TEXT<\/span>    NOT<\/span> NULL<\/span>,\n    completed      INTEGER<\/span> NOT<\/span> NULL<\/span>,\n    start_date     TEXT<\/span>,\n    completed_date TEXT<\/span>,\n    project_id     INTEGER<\/span> NOT<\/span> NULL<\/span>,\n    FOREIGN<\/span> KEY<\/span> (project_id)\n        REFERENCES<\/span> projects (project_id) \n           ON<\/span> UPDATE<\/span> CASCADE<\/span>\n           ON<\/span> DELETE<\/span> CASCADE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 1. Create a new file in the project directory namedcreate-tables.php<\/code>.<\/p>\n\n\n\n
Step 2. Add the following code to the create-tables.php<\/code> file:<\/p>\n\n\n
<?php<\/span>\n\nrequire_once<\/span> 'config.php'<\/span>;\n\n$statements = [\n    'CREATE TABLE IF NOT EXISTS projects (\n        project_id   INTEGER PRIMARY KEY,\n        project_name TEXT NOT NULL\n    )'<\/span>,\n    'CREATE TABLE IF NOT EXISTS tasks (\n        task_id INTEGER PRIMARY KEY,\n        task_name  VARCHAR (255) NOT NULL,\n        completed  INTEGER NOT NULL,\n        start_date TEXT,\n        completed_date TEXT,\n        project_id VARCHAR (255),\n        FOREIGN KEY (project_id)\n            REFERENCES projects(project_id) \n                ON UPDATE CASCADE\n                ON DELETE CASCADE)'<\/span>\n];\n      \n\n\/\/ connect to the SQLite databse<\/span>\n$dsn = \"sqlite:$db\"<\/span>;\n\n\/\/ create a PDO instance<\/span>\ntry<\/span> {\n    $pdo = new<\/span> \\PDO($dsn);\n\n    \/\/ create tables<\/span>\n    foreach<\/span>($statements as<\/span> $statement){\n        $pdo->exec($statement);\n    }\n} catch<\/span>(\\PDOException $e) {\n    echo<\/span> $e->getMessage();\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, load the config.php<\/code> file:<\/p>\n\n\n
require_once<\/span> 'config.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the config.php<\/code> file contains a variable that stores the path of the SQLite database file:<\/p>\n\n\n
<?php<\/span>\n\n$db = '.\/database\/my.db'<\/span>;<\/span><\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, define an array that holds the CREATE TABLE<\/code> statements for creating the projects<\/code> and tasks<\/code> tables:<\/p>\n\n\n
$statements = [\n    'CREATE TABLE IF NOT EXISTS projects (\n        project_id   INTEGER PRIMARY KEY,\n        project_name TEXT NOT NULL\n    )'<\/span>,\n    'CREATE TABLE IF NOT EXISTS tasks (\n        task_id INTEGER PRIMARY KEY,\n        task_name  VARCHAR (255) NOT NULL,\n        completed  INTEGER NOT NULL,\n        start_date TEXT,\n        completed_date TEXT,\n        project_id VARCHAR (255),\n        FOREIGN KEY (project_id)\n            REFERENCES projects(project_id) \n                ON UPDATE CASCADE\n                ON DELETE CASCADE)'<\/span>\n];<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, define a variable that holds the data source name (dsn<\/code>):<\/p>\n\n\n
$dsn = \"sqlite:$db\"<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, open a connection to the SQLite database file:<\/p>\n\n\n
$pdo = new<\/span> \\PDO($dsn);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, iterate over statements in the $statements<\/code> array, and execute each to create a new table:<\/p>\n\n\n
foreach<\/span>($statements as<\/span> $statement) {\n    $pdo->exec($statement);\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, display an error message if any exception occurred during the table creation process:<\/p>\n\n\n
echo<\/span> $e->getMessage();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Run the create-tables.php <\/code>script.<\/p>\n\n\n\n
Verifying tables<\/h2>\n\n\n\n
The following steps show you how to verify table creation using the sqlite3<\/code> shell:<\/p>\n\n\n\n
Step 1. Open your terminal and navigate to the database<\/code> directory.<\/p>\n\n\n\n
Step 2.  Connect to the my.db<\/code> database using the sqlite3<\/code> shell:<\/p>\n\n\n
sqlite3 my.db<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Show all tables in the my.db<\/code> database:<\/p>\n\n\n
.tables<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
projects  tasks<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the projects<\/code> and tasks<\/code> tables have been created successfully.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Run the CREATE TABLE<\/code> statement by calling the exec()<\/code> method of the PDO<\/code> object to create a new table in an SQLite database.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t