Create a PostgreSQL Database Table in PHP

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

Introduction

PostgreSQL gives developers many options for effectively managing database information. For example, with databases, you can add, retrieve, update, and delete data as needed. Without tables though, data is more difficult to decipher. Because tables help organize stored information in logical formations, creating tables using the PHP programming language comes in handy. In addition, when you use the PHP PDO driver to make a PostgreSQL connection, you can perform the create PostgreSQL database table PHP function in the simplest way.

Prerequisite

  • Download, and then install the object-relational database management system PostgreSQL recommended for your operating system.

  • Download, and then install XAMPP server on your operating system. When finished, properly configure it. Background-run the server.

Creating the PostgreSQL Database

  • In the PostgreSQL command shell, type this code below:
1
psql -U postgres

NOTE: The command shown above logs into the privileges-granted postgres user account. If you use a different account for your sample PostgreSQL database, give it the same type of elevated role privileges as a superuser.

  • Make your sample database with this command:
1
2
postgres=# create database sampledb;
CREATE DATABASE
  • Confirm the success of your database creation with the \l command. This generates an entire PostgreSQL database list.
1
2
3
4
5
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------------+----------+----------+--------------------------+--------------------------+-----------------------
carddb | postgres | UTF8 | English_Philippines.1252 | English_Philippines.1252 |
sampledb | postgres | UTF8 | English_Philippines.1252 | English_Philippines.1252 |

Enable PostgreSQL PDO_PGSQL Driver.

You won’t have to make a separate PostgreSQL PDOPGSQL driver extension installation because it comes with the distro of PHP. If you find it isn’t enabled, no worries. Follow these steps to activate the PDO driver to continue with the _create PostgreSQL database table PHP steps.

  • Open the php.ini configuration file.

  • Locate the ;extension=php_pdo_pgsql.dll section for extensions.

  • Take out the ; (semi-colon) at the start of it.

  • It will now show extension=php_pdo_pgsql.dll without the semi-colon.

  • Restart the server to save the changes so that you can proceed with the create PostgreSQL database table PHP lesson.

Connecting to the PostgreSQL Database

  • Connect to the PHP database like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$host="localhost";
$port="5432";
$dbname="sampledb";
$username="postgres";
$password="1234";
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;user=$username;password=$password";

try{
// creates the PostgreSQL database connection
$conn = new PDO($dsn);

// message if connected to the PostgreSQL successfully
if($conn){
echo "Connected to the $dbname database successfully!";
}
}catch (PDOException $e){
// should there be an error lets get that and show it to the user.
echo $e->getMessage();
}

Here’s an explanation of the variables you just inputted at the beginning of the code to complete the PostgreSQL connection with your data source name (DSN):

  • The hostname is $host=”localhost”;

  • The listening and receiving connection port is $port=”5432″;

  • The database name for your sample you just made for this tutorial is – $dbname=”sampledb”;

  • The username of the user is $username=”postgres”;

  • The password of the user $password=”1234″;

Now, put together the specifics based on your DSN in this order as shown here:

$dsn = “pgsql:host=$host;port=$port;dbname=$dbname;user=$username;password=$password”;.

  • Test the connection by going to a browser and typing “http://localhost/phppdo/index.php ” as the URL.

  • The result should look similar to this one:

Creating a PostgreSQL Database Table

  • _Create PostgreSQL database table PHP_by updating your file index.php. Modify the script to make the table called sampleTable.
1
2
3
4
5
6
$sqlList = "CREATE TABLE IF NOT EXISTS sampleTable (
id serial PRIMARY KEY,
sampleField character varying(20) NOT NULL UNIQUE
)"
;

$conn->exec($sqlList);

Here’s a more detailed explanation about the code you just entered:

  • The sampleTable will be created if none exists.

  • The id field and the sampleField are created.

You’re ready to test the app.

  • Do this by restarting it.

Verify that the sampleTable was constructed.

  • In the PostgreSQL shell, use the \c sample command to connect to the sampledb database.

  • Use the \dt command to determine if the table is in the “List of relations” for the database.

1
2
3
4
5
6
7
8
postgres-# \c sampledb
You are now connected to database "sampledb" as user "postgres".
sampledb-# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | sampletable | table | postgres
(1 row)

It’s a success! The database sampledb contains the sampleTable table.

Conclusion

PostgreSQL databases store large amounts of information. Developers, DBAs, and other technical administrators realize that they can best manage loads of data with tables. This tutorial explained how to best perform the create PostgreSQL database table PHP steps. It showed how the PDO driver enables fast connections for the PostgreSQL database. As a result, users can quickly access database table data to get the right answers to their questions. You practiced these steps by creating a sample database and table. After that, you made a successful database connection and located your table in the list of relations for your database. Work proficiency increases when your table data contains what you need; therefore, make tables often and put unlimited productivity on your side.

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.