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 follow these 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’ll
create two new tables in the stocks
database: stocks
and stock_evaluations
with the following structures:
CREATE TABLE IF NOT EXISTS stocks (
id SERIAL PRIMARY KEY,
symbol CHARACTER VARYING(10) NOT NULL UNIQUE,
company CHARACTER VARYING(255) NOT NULL UNIQUE
);
Code language: SQL (Structured Query Language) (sql)
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)
);
Code language: SQL (Structured Query Language) (sql)
We create a new class named PostgreSQLCreateTable
in the app
folder.
<?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;
}
}
Code language: PHP (php)
How it works.
- First, the constructor of the class accepts a PDO object as the argument.
- Second, the
createTables()
method creates new tables in the database. The $sqlList
array holds all the
CREATE TABLE statements. To execute a statement, you call the exec()
method of the PDO object. We iterate over the array of SQL statements and execute them one by one by calling the exec()
method. - Third, the
getTables()
method returns all tables in the connected database. We use it to query the tables in the stocks
database after calling the createTables()
method.
In the index.php
file, connect to the PostgreSQL database execute the statement to create tables and query tables.
<?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();
}
Code language: PHP (php)
Launch the index.php
file in a web browser. You’ll see the following output:
stock_valuations
stocks
Code language: SQL (Structured Query Language) (sql)
The
output shows that the script has created two tables successfully.
Summary
- Use the
CREATE TABLE
statement to create a new table. - Use the PDO
exec()
method to to execute a CREATE TABLE
statement to create a new table in the datatabase.
Was this tutorial helpful ?
PostgreSQL CREATE TABLE.
First, specify the name of the table after the CREATE TABLE keywords..
Second, creating a table that already exists will result in a error. ... .
Third, specify a comma-separated list of table columns. ... .
Finally, specify the table constraints including primary key, foreign key, and check constraints..
Login to phpPgAdmin and reach "Public" database. Now click on "Create table" in the right hand pane of the phpPgAdmin window. In the next window, supply name and number of columns you want to create and click "Next". In the next window, supply name of the column, data type and click "Create".
PostgreSQL PHP: Querying Data.
First, connect to the PostgreSQL database by creating a new PDO object..
Second, call the query() method of the PDO object. The query() method accepts a SELECT statement as the argument. ... .
Third, fetch the next rows from the result by calling the fetch() method of the PDOstatement object..