Postgres query in PHP

Written by Data Pilot

March 24, 2020

PHP
Postgres

Introduction

The PostgreSQL SELECT statement can be used to obtain data from a single table, multiple tables or viewed as a virtual table. PDO, standing for PHP Data Objects, acts like a data-access layer that uses a unified application programming interface, commonly referred to as an API. The PDO extension is used to define a durable, but lightweight, interface that can be used to access databases in PHP. This tutorial will explain how to use the Postgres query in PHP via PHP PDO using the SELECT statement.

Prerequisites

PostgreSQL must be properly installed and configured on the local system.
A basic working knowledge in PostgreSQL is required to perform the Postgres query in PHP.
PHP, in either a standalone version or with a package installer such as XAMPP, must also be properly installed on the local system.
Composer must also be properly installed.

How to Query All Postgres Rows in PHP

Execute the following three steps, in order, to query the PostgreSQL database table:

1) Connect to a Postgres database using the PDO object.

2) As the methods required argument returns a PDOStatement object, execute the method query() of the PDO object by using the SELECT statement.

3) Execute the method fetch() of the PDOStatement object to fetch the succeeding rows from the result set. Note here that the argument fetch_style of the method fetch() shapes the results being returned.

Creating Sample Dataset

This section will detail how to create a sample dataset used for the demonstrations in this tutorial:

Begin by logging into the Postgres shell and then follow these steps:

Execute the following command to create database

1	create database product

Next, create tables for the database as follows:

1
2
3
4
5
6
7
8

CREATE TABLE IF NOT EXISTS stock (
id SERIAL PRIMARY KEY,
product_name CHARACTER VARYING(100),
sku CHARACTER VARYING(100),
description CHARACTER VARYING(100),
quantity INT

);

As demonstrated here, insert some sample records in the “stock” table:

1
2
3
4
5
6
7

INSERT INTO stock (id, product_name, sku, description, quantity)
VALUES
(1,'tumbler','MN0321', 'Hard plastic tumbler', 50),
(2,'thermostat','MN0325', 'light weight portable thermostat', 30),
(3,'camping bag','MN0341', 'Durable portable bag', 24),
(4,'latex gloves','MN0387', 'Gloves for surgery', 50),
(5,'Torch Flash Light','MN0311', 'Super bright flashlight', 90);

The table should look like the following:

1
2
3
4
5
6
7
8

id | product_name | sku | description | quantity
----+-------------------+--------+----------------------------------+----------
1 | tumbler | MN0321 | Hard plastic tumbler | 50
2 | thermostat | MN0325 | light weight portable thermostat | 30
3 | camping bag | MN0341 | Durable portable bag | 24
4 | latex gloves | MN0387 | Gloves for surgery | 50
5 | Torch Flash Light | MN0311 | Super bright flashlight | 90
(5 rows)

Project Structure

This section will demonstarted the overall project structure, displayed in the following image:

Query PostgreSQL in PHP Example

This section will cover executing the Postgres query in PHP.

Query all Postgres Rows

The following shows how to query all the Postgres rows via 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

<?php

namespace phpquery;

class phppostgresquery {

private $pdo;

/**
* Initialize the object with a specified PDO object
* @param \PDO $pdo
*/
public function __construct($pdo) {
$this->pdo = $pdo;
}

/**
* This function will return all rows in the stock table
* @return array
*/
public function all() {
$stmt = $this->pdo->query('SELECT id, product_name, sku, description, quantity '
. 'FROM stock '
. 'ORDER BY sku');
// creates an array place holder for the resultset
$stocks = [];
// iterate throught the $stocks array
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
$stocks[] = [
'id' => $row['id'],
'product_name' => $row['product_name'],
'sku' => $row['sku'],
'description' => $row['description'],
'quantity' => $row['quantity']
];
}
return $stocks;
}

}

Note that the properly commented above PHP scripts are much easier to understand.

Following is the ‘index.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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

<?php
require 'vendor/autoload.php';

use phpquery\Connection as Connection;
use phpquery\phppostgresquery as postgresquery;

try {
// connect to the PostgreSQL database
$pdo = Connection::get()->connect();
//
$stock = new postgresquery($pdo);
// get all stocks data
$stocks = $stock->all();
} catch (\PDOException $e) {
echo $e->getMessage();
}
?>
<!DOCTYPE html>
<html>
<head>
<title>PostgreSQL Query</title>
<link rel="stylesheet" href="<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
</head>
<body>
<div class="container">
<h1>Stock List</h1>
// creates a table that display all the records via table
<table class="table table-bordered">
<thead>
<tr>
<th>ID</th>
<th>Product Name</th>
<th>SKU</th>
<th>Description</th>
<th>Quantity</th>
</tr>
</thead>
<tbody>
// iterate through the $stocks and put them on their own rows
<?php foreach ($stocks as $stock) : ?>
<tr>
<td><?php echo htmlspecialchars($stock['id']) ?></td>
<td><?php echo htmlspecialchars($stock['product_name']); ?></td>
<td><?php echo htmlspecialchars($stock['sku']); ?></td>
<td><?php echo htmlspecialchars($stock['description']); ?></td>
<td><?php echo htmlspecialchars($stock['quantity']); ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
</body>
</html>

Next, use the following URL: ‘http://localhost/phpquery/index.php’ to test the PHP application.

The output should resemble the following:

-img src=”https://t.gyazo.com/teams/500rockets/bea2217c36f787d01378412f94ea1b4e.png” width=”632″ height=”456″-

Query Specific Postgres Rows

This section shows how to query specific Postgres rows in PHP using the following scripts:

phppostgresquery.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

<?php

namespace phpquery;

class phppostgresquery {

private $pdo;

/**
* Initialize the object with a specified PDO object
* @param \PDO $pdo
*/
public function __construct($pdo) {
$this->pdo = $pdo;
}

/**
* Find stock by id
* @param int $id
* @return a stock object
*/
public function findByID($id) {
// we prepare SELECT statement
$stmt = $this->pdo->prepare('SELECT id, product_name, sku, description, quantity
FROM stock
WHERE id = :id');
// we then bind value to the :id parameter
$stmt->bindValue(':id', $id);

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

// return the result set as an object
return $stmt->fetchObject();
}

}

index.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

<?php
require 'vendor/autoload.php';

use phpquery\Connection as Connection;
use phpquery\phppostgresquery as postgresquery;

try {
// connect to the PostgreSQL database
$pdo = Connection::get()->connect();
//
$stockDB = new postgresquery($pdo);
// This gets an specific data as per the argument's value
$stock = $stockDB->findByID(1);

var_dump($stock);

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

The result of the above scripts should look like the following:

Conclusion

This tutorial will explain the Postgres query in PHP via PHP PDO. The tutorial explained first explained how to query all Postgres rows in PHP and provided the steps required to query the PostgreSQL database table. The article then covered how to create a sample dataset and project structure, how to query all Postgres rows and then provided an example on how to query PostgreSQL in PHP. The tutorial then gave the URL used for testing the PHP application and provided a screenshot of what the successfully executed scripts should look like. Remember that the PHP scripts are much easier to understand when properly commented.

The Code

Following are the code for the files 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 phpquery;

/**
* Represent the Connection
*/
class Connection {

/**
* Connection
* @var type
*/
private static $conn;

/**
* Connect to the database and return an instance of \PDO object
* @return \PDO
* @throws \Exception
*/
public function connect() {

// This code will read the parameters on the Db.ini file using the parse_ini_file() function

$paramDb = parse_ini_file('param.ini');
if ($paramDb === false) {
throw new \Exception("Error reading database config file, please check param.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() {

}

}

param.ini

1
2
3
4
5

host=localhost
port=5432
database=product
user=postgres
password=1234

composer.json

1
2
3
4
5
6
7

{
"autoload": {
"psr-4": {
"phpquery\": "app/"
}
}
}

Pilot the ObjectRocket Platform Free!

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

Get Started