Query Postgres data using PHP on ObjectRocket

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

Introduction

If your PHP applications interact with a PostgreSQL database, you may want the ability to execute some queries from these applications. Fortunately, this task is easy to accomplish. In this article, we’ll explain how to query Postgres data using PHP on ObjectRocket. Our step-by-step instructions are accompanied by code examples to get you started with your own application.

Prerequisites

Before moving forward with this tutorial, make sure that the following items are installed and configured:

You’ll also need some basic knowledge of PostgreSQL in order to follow along with this article.

Be sure to create an instance of Postgres for your ObjectRocket account– you can do this using the ObjectRocket Mission Control panel if you haven’t created one yet.

Creating the PHP Project Structure

Our next task is to create our PHP project structure, which should look something like this:

Here’s what we need to do:

  • Inside XAMPP’s htdocs directory, we’ll create a new folder and name it phpPostgreSQLquery.

  • In the phpPostgreSQLquery folder, we’ll create another folder called app that will hold three key files: 1. Connection.php 2. dbquery.ini 3. phpquery.php

  • In the phpPostgreSQLquery folder, we’ll also create a JSON file and name it composer.json. This file should contain the following script:

1
2
3
4
5
6
7
{
    "autoload": {
        "psr-4": {
            "phpPostgreSQLquery\": "app/"
        }
    }
}

The steps outlined above complete the mapping of the app folder to the project directory’s namespace; in this case, the namespace is phpPostgreSQLquery.

  • We then update our project in Composer using the following command in the terminal. Be sure that you are inside the project directory when you execute this command:
1
composer update
  • Finally, we create a file named index.php in phpPostgreSQLquery.

How to Connect PHP to Postgres on ObjectRocket

The connection details we need for the next task can found in the ObjectRocket Mission Control panel, under the CONNECT tab:

We will add these details to our dbiquery.ini configuration file as follows:

1
2
3
4
5
host=ingress.w98sujpz.launchpad.objectrocket.cloud  
port=4149
database=person
user=pguser
password=1234
  • We also need to edit the Connection.php file. We’ll add 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
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 phpPostgreSQLquery;
 
/**
 * 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('dbquery.ini');
        if ($paramDb === false) {
            throw new \Exception("Error reading database config file, please check db_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() {
       
    }
 
}

We then add the PHP script shown below to the phpquery.php file:

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 phpPostgreSQLquery;

class phpquery{

       /**
     * PDO object
     * @var \PDO
     */

    private $pdo;
 
    /**
     * Initialize the object with a specified PDO object
     * @param \PDO $pdo
     */

    public function __construct($pdo) {
        $this->pdo = $pdo;
    }

     /**
     * Return a single row in the person table
     * @return array
     */

        public function personby_id($id) {
        $stmt = $this->pdo->prepare('SELECT id, name, age FROM person WHERE id = :id');

        // binding the value to the parameter :id
        $stmt->bindValue(':id',$id);

        $stmt->execute();

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

    }

}

Last but not least, we’ll modify our project’s entry point: the index.php file. We will add the following PHP script:

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 phpPostgreSQLquery\Connection as Connection;
use phpPostgreSQLquery\phpquery as PersonDB;
 
try {
    // connect to the PostgreSQL database
    $pdo = Connection::get()->connect();
    //
    $personDB = new PersonDB($pdo);
    // get all stocks data
    $persons = $personDB->personby_id(1);

    var_dump($persons);

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

This script selects a row in the ‘person’ table, using the criteria we set in our WHERE clause.

Our final step is to update our autoload files using this command:

1
composer dump-autoload -o

Testing the Code

At this point, we can test out our code and see if this application works. We’ll be using this sample dataset for our test:

1
2
3
4
5
6
7
 id |  name   | age
----+---------+-----
  1 | jame    |  13
  2 | ann     |  16
  3 | ruby    |  32
  4 | jovanny |  32
(4 rows)

We’ll use the following URL to test the application: http://localhost/phpPostgreSQLquery/index.php.

We should see something like this:

Conclusion

If you’re developing a PHP application that works with PostgreSQL, there’s a good chance you’ll want to execute a Postgres query from your code. In this article, we showed you how to query Postgres data using PHP on ObjectRocket, and we supplied all the sample code you’ll need to start your project. With our examples and instructions, you’ll be prepared to query your Postgres instance from your own PHP application.

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.