How to Create a Postgres Table in PHP

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’re a developer working with PHP, you’ll find that PostgreSQL gives you plenty of options for managing your database from your scripts. You can add, update and delete records with just a few lines of code. However, to perform any of these operations, you first need a table in place. In this article, we’ll show you how to create a Postgres table in PHP and provide some sample code to illustrate how it’s done.

Prerequisite

Before we dive into the code examples, let’s review the prerequisites that are necessary for this tutorial:

  • PostgreSQL must be installed and configured on your machine.
  • Some basic knowledge of PostgreSQL is also necessary.
  • PHP must be installed on your machine, whether it’s a standalone installation or installed with a package installer like XAMPP.
  • Composer also needs to be installed on your machine.

Creating New Postgres Table

In this section, we’ll show you how to create a Postgres table using PHP with the help of the PHP PDO API.

In order to better understand the process, let’s begin by looking at the command we would use to create a table if we were using the Postgres shell:

Shown below is the statement we would use:

1
2
3
4
5
CREATE TABLE IF NOT EXISTS item (
    id SERIAL PRIMARY KEY,
    item_name CHARACTER VARYING(100),
    item_description CHARACTER VARYING(255)
);

This statement will create a table named item as long as a table with the same name does not already exist in the database. If it does exist, a message will be returned.

NOTE: Although the process isn’t shown in this tutorial, we already created a database named hardware, which is where this table resides.

Project Structure

Next, we’ll show you the structure of the project directory we use in this tutorial:

alt text

PHP Scripts for Creating a Postgres Table

Now that we have our project structure and necessary files in place, we can jump into the code needed to create a Postgres table in PHP.

To do this, we’ll edit the PHP file named ‘postgrescreate.php’ and add the following code:

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
<?php
 
namespace phpcreatetable;

class postgrescreate {
 
    /**
     * PDO object
     * @var \PDO
     */

    private $pdo;
 
    /**
     * init the object with a \PDO object
     * @param type $pdo
     */

    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
 
    /**
     * PHP script for creating tables
     */

    public function createTables() {
        $sqlList = ['CREATE TABLE IF NOT EXISTS item (
            id SERIAL PRIMARY KEY,
            item_name CHARACTER VARYING(100),
            item_description CHARACTER VARYING(255)
        );'
];
 
        // execute each sql statement to create new tables
        foreach ($sqlList as $sql) {
            $this->pdo->exec($sql);
        }
        echo 'table successfully created!';
        return $this;
    }
}

Next, we’ll edit another PHP file called ‘index.php’. This script will invoke the script we just edited:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
 
require 'vendor/autoload.php';
 
use phpcreatetable\Connection as Connection;
use phpcreatetable\postgrescreate as postgrescreate;
 
try {
   
    // This will connect us to the database
    $pdo = Connection::get()->connect();
   
    //
    $tableCreator = new postgrescreate($pdo);
   
    // this creates the table in the target database

    $tables = $tableCreator->createTables();
                           
   
} catch (\PDOException $e) {
    echo $e->getMessage();
}

At this point, we can test our PHP application. Let’s navigate to the index file in a browser using the following URL: ‘http://localhost/phpcreatetable/index.php’

The output should look something like this:

alt text

We can verify that the table named item was created successfully in the target database in the Postgres shell. Just use the ‘dt’ command after connecting to the database ‘hardware’.

The output should look something like this:

1
2
3
4
5
6
hardware=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | item | table | postgres
(1 row)

Conclusion

If you’re a developer working with a PostgreSQL database, you may find yourself needing to create a Postgres table from one of your PHP scripts. In this article, we provided step-by-step instructions to create a Postgres table in PHP, and we examined sample code that shows exactly how to accomplish the task. With our code examples to guide you, you’ll have no problem creating a PostgreSQL table from your own PHP scripts.

The Code

Shown below is the complete PHP script that was used in this tutorial:

Connection.php

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
<?php
 
namespace phpcreatetable;
 
/**
 * Represent the Connection
 */

class Connection {
 
    /**
     * Connection
     * @var type
     */

    private static $conn;
 
    /**
     * Returns an instance of the PDO
     * @return \PDO
     * @throws \Exception
     */

    public function connect() {
 
        // This code will reads the parameters in the db.ini file using the parse_ini_file() function
       
        $paramDb = parse_ini_file('db.ini');
        if ($paramDb === false) {
            throw new \Exception("Error reading database config file, please check db.ini file");
        }
        // The following code will connect us to the database using the parsed parameters
        $connString = sprintf("pgsql:host=%s;port=%d;dbname=%s;user=%s;password=%s",
                $paramDb['host'],
                $paramDb['port'],
                $paramDb['database'],
                $paramDb['user'],
                $paramDb['password']);
 
        $pdo = new \PDO($connString);
        $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
 
        return $pdo;
    }
 
    /**
     * this code will return an instance of the Connection object
     * @return type
     */

    public static function get() {
        if (null === static::$conn) {
            static::$conn = new static();
        }
 
        return static::$conn;
    }
 
    protected function __construct() {
       
    }
 
}

db.ini

1
2
3
4
5
host=localhost
port=5432
database=hardware
user=postgres
password=1234

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.