How to Connect PHP with PostgreSQL using the PDO Driver - Part Two

Written by Data Pilot
August 06, 2019
  • PHP
  • PostgreSQL
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This is part two of the tutorial “How to Connect PHP with PostgreSQL using the PDO Driver.” Part one of the tutorial explained how to create a database and connect PostgreSQL in PHP using the PDO driver. This part will explain how to connect PHP PostgreSQL with the PDO driver and how to interact with the database with PHP. This part two will be building on the techniques learned in part one of this series.

Prerequisites for connecting PHP PostgreSQL with the PDO driver.

  • The completion and thorough understanding of part one in this tutorial series.

How to Create a Table

A database table must be created for the database that was created in part one of this series.

First, confirm that the PostgeSQL server is running in the background by executing the following command in the terminal:

1
sudo service postgresql start

Now log into the server and create the database table using the following SQL syntax:

1
2
3
4
5
6
7
CREATE TABLE tblEmployee(
user_id serial PRIMARY KEY,
employeenumber INTEGER NOT NULL,
firstname VARCHAR (50) NOT NULL,
lastname VARCHAR (50) NOT NULL,
email VARCHAR (355) UNIQUE NOT NULL
);

The above SQL statement will create an “employee” table. If the table was properly created, the columns can be verified by querying the table with the following command:

1
2
3
4
5
SELECT * FROM tblEmployee WHERE FALSE;

user_id | employeenumber | firstname | lastname | email |
---------+----------------+-----------+----------+--------
(0 ROWS)

The above image displays the columns within the tblEmployee.

How to insert data in the PostgreSQL table using PDO

With the table created in the previous section, CRUD operations can be performed against the tblEmployee in PHP.

Here, another PHP file will be created and be named pgSQLoperation.php. Then a class will be created within that file to execute the CRUD operation with the following PHP script:

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


class pgSQLoperation{

public function insertEmployee($employeenumber, $firstname, $lastname, $email) {
global $pdo;

// prepare statement for insert
$sql = 'INSERT INTO tblemployee (employeenumber,firstname,lastname,email) VALUES (:employeenumber,:firstname,:lastname,:email)';
$stmt = $pdo->prepare($sql);

// pass values to the statement
$stmt->bindValue(':employeenumber', $employeenumber);
$stmt->bindValue(':firstname', $firstname);
$stmt->bindValue(':lastname', $lastname);
$stmt->bindValue(':email', $email);

// execute the insert statement
$stmt->execute();

// return generated id
return $pdo->lastInsertId('user_id');

}

}


?>

A step-by-step explanation of the above script:

  1. The above script created the function insertEmployee that accepts the parameters that will eventually be inserted into the new table.

  2. The global $pdo will obtain the setting of the global variable $pdo.

  3. The INSERT statement is now passed to the prepare() statement of the global $pdo object for execution.

  4. The bindValue() method is used to pass the values from the parameter.

  5. The INSERT statement is then executed via the code $stmt->execute().

  6. Finally, the last of the inserted data in the table is passed using the lastInsertId() method of the PDO object.

With the class pgSQLoperation set up, modify the index.php by executing the following script:

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
<?php

require 'vendor/autoload.php';

use PostgreSQLPHPConnection as Connection;
use PostgreSQLPHPpgSQLoperation as pgSQLoperation;

try {
// Connect to the database
$pdo = Connection::get()->connect();

$insertEmp = new pgSQLoperation($pdo);

// insert an employee details into the tblemployee table
$id = $insertEmp->insertEmployee('001', 'rommel','galisanao','rmgalisanao30@gmail.com');
echo 'The employee has been inserted with an id of ' . $id . '
';



} catch (PDOException $e) {
echo $e->getMessage();
}


?>

The above PHP script connects to the server and then executes the insertEmployee function from the class pgSQLoperation while providing the needed parameters as shown above .

Should an error occur during the execution, an error handler can be performed.

Now test the file using the process explained in part one of this tutorial series. Navigate to the project directory, via the terminal, and execute the following command:

1
php -S localhost:9090

Then type localhost:9090 in the browser. The browser should display something that resembles the following:

1
The employee has been inserted with an id of 1

Conclusion

This was part two of the tutorial “How to Use PostgreSQL PDO Driver with PHP.” This part explained how to connect PHP PostgreSQL with the PDO driver. Building on part one in this series, part two specifically explained how to create a table, how to insert data in the PostgreSQL table using PDO and how to check for errors. Part two also provided a detailed breakdown and explanation of the script used to execute the CRUD operation. Remember that an error handler should be performed should an error occur during the execution of the insertEmployee function from the `class pgSQLoperation.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

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

Get Started
Related Topics:
Additional Information:

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.