Summary: in this tutorial, you will learn how to create new tables in the PostgreSQL database using PHP PDO API.
Creating new tables using PHP PDO steps
To create new tables in a PostgreSQL database using PHP PDO, you use the following steps:
- First, connect to the database by creating a new PDO object.
- Second, call the
exec()method of the PDO object to execute the CREATE TABLE statement.
Let’s look at an example of creating new tables.
Creating new table example
In the previous tutorial, we created the stocks database in the PostgreSQL database server. For the demonstration, we will create two new tables in the stocks database: stocks and stock_evaluations with the following structures:
1 2 3 4 5 | CREATE TABLE IF NOT EXISTS stocks ( id SERIAL PRIMARY KEY, symbol CHARACTER VARYING(10) NOT NULL UNIQUE, company CHARACTER VARYING(255) NOT NULL UNIQUE ); |
1 2 3 4 5 6 7 8 | CREATE TABLE IF NOT EXISTS stock_valuations ( stock_id INTEGER NOT NULL, value_on DATE NOT NULL, price NUMERIC(8 , 2 ) NOT NULL DEFAULT 0, PRIMARY KEY (stock_id , value_on), FOREIGN KEY (stock_id) REFERENCES stocks (id) ); |
We create a new class named PostgreSQLCreateTable in the app folder.
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 57 58 59 60 61 62 63 64 | <?php namespace PostgreSQLTutorial; /** * Create table in PostgreSQL from PHP demo */ class PostgreSQLCreateTable { /** * PDO object * @var \PDO */ private $pdo; /** * init the object with a \PDO object * @param type $pdo */ public function __construct($pdo) { $this->pdo = $pdo; } /** * create tables */ public function createTables() { $sqlList = ['CREATE TABLE IF NOT EXISTS stocks ( id serial PRIMARY KEY, symbol character varying(10) NOT NULL UNIQUE, company character varying(255) NOT NULL UNIQUE )', 'CREATE TABLE IF NOT EXISTS stock_valuations ( stock_id INTEGER NOT NULL, value_on date NOT NULL, price numeric(8,2) NOT NULL DEFAULT 0, PRIMARY KEY (stock_id, value_on), FOREIGN KEY (stock_id) REFERENCES stocks(id) );']; // execute each sql statement to create new tables foreach ($sqlList as $sql) { $this->pdo->exec($sql); } return $this; } /** * return tables in the database */ public function getTables() { $stmt = $this->pdo->query("SELECT table_name FROM information_schema.tables WHERE table_schema= 'public' AND table_type='BASE TABLE' ORDER BY table_name"); $tableList = []; while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { $tableList[] = $row['table_name']; } return $tableList; } } |
How it works.
- First, the constructor of the class accepts a PDO object as the argument.
- Second, the createTables() method is in charge of creating new tables in the database. The
$sqlListarray holds all the CREATE TABLE statements. To execute a statement, you call theexec()method of the PDO object. We iterate over the array of SQL statements and execute them one by one by calling theexec()method. - Third, the
getTables()method returns all tables in the connected database. We use it to query the tables in thestocksdatabase after calling thecreateTables()method.
In the index.php file, we connect to the PostgreSQL database, execute the statement to create tables, and query tables in the database.
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 | <?php require 'vendor/autoload.php'; use PostgreSQLTutorial\Connection as Connection; use PostgreSQLTutorial\PostgreSQLCreateTable as PostgreSQLCreateTable; try { // connect to the PostgreSQL database $pdo = Connection::get()->connect(); // $tableCreator = new PostgreSQLCreateTable($pdo); // create tables and query the table from the // database $tables = $tableCreator->createTables() ->getTables(); foreach ($tables as $table){ echo $table . '<br>'; } } catch (\PDOException $e) { echo $e->getMessage(); } |
Launch the index.php file in a web browser, we get the following output:
1 2 | stock_valuations stocks |
It means that we have created these two tables successfully from the PHP application. In the next tutorial, we will show you how to insert data into these tables using PHP PDO API.