Insert Postgres Data using PHP on ObjectRocket

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

Introduction

When you interact with your PostgreSQL database from a PHP application, you’ll probably want to perform some common database operations in your code, such as inserting, updating, deleting and querying data. In this article, we’ll focus on the insert operation. We’ll show you how to insert Postgres data using PHP on ObjectRocket, providing detailed instructions and helpful code examples so that you can follow along on your own machine.

Prerequisites

A few prerequisites need to be in place before you proceed with this tutorial:

  • XAMPP also must be installed.

  • We will be using Composer to build our project structure and handle third-party library dependencies, so this must be installed as well.

You’ll need to create an instance of Postgres for your ObjectRocket account. You can do this using the ObjectRocket Mission Control panel.

Creating the PHP Project Structure

Let’s begin by creating the project structure needed for our application:

  • Inside the htdocs directory, we create a folder named phpPostgreSQLinsert.

  • We then navigate into the phpPostgreSQLinsert folder and create a new folder named app.

  • Next, we create a composer.json file in the phpPostgreSQLinsert folder. We’ll add the following script to this file:

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

With these steps, we’re able to complete the app folder’s mapping to the phpPostgreSQLinsert‘s namespace.

Now that we handled the mapping, we’ll need to update our project. We can do this in the terminal by navigating to our project directory and executing the following command:

1
composer update

This update command downloads all the required libraries that are defined in the composer.json file. When the code in that file is executed, the following changes take place within the project directory:

  1. The autoload files are automatically generated.
  2. Composer also automatically creates a folder named vendor, which will hold the necessary third-party libraries.

Within the app folder, we need to create three important files:

  • Connection.php
  • PostgreSQLinsert.php
  • dbinsert.ini

The final part of our project structure that still needs to be completed is an index.php file in our phpPostgreSQLinsert project directory.

Our structure should look something like this:

Connecting PHP to Postgres on ObjectRocket

If we want to connect PHP to Postgres, we’ll need to get the connection details for our Postgres server instance on ObjectRocket Mission Control. To access this information, just locate the “CONNECT” tab:

These details will be added to our dbinsert.ini configuration file:

1
2
3
4
5
host=ingress.w98sujpz.launchpad.objectrocket.cloud  
port=4149
database=person
user=pguser
password=1234

NOTE: The username and password used in this file is the one that was set up when you created a Postgres server instance.

We then parse the dbinsert.ini configuration file in our Connection.php file. This action takes place in 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
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 phpPostgreSQLupdate;
 
/**
 * Represent the Connection
 */

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

    private static $con;
 
    /**
     * 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('dbinsert.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
        $conString = 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($conString);
        $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::$con) {
            static::$con = new static();
        }
 
        return static::$con;
    }
 
    protected function __construct() {
       
    }
 
}

We add the PHP script shown below to our PostgreSQLupdate.php file. This is where the insert operation takes place:

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

class PostgreSQLinsert{

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

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

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

     /**
     * Insert a new row in person table
     * @return the id of the inserted row
     */

        public function insertPerson($id,$name,$age) {
            $sql =  'INSERT INTO person (id,name,age) VALUES (:id,:name,:age)';
        $stmt = $this->pdo->prepare($sql);

        // bind the values to be inserted in the table    
        $stmt->bindValue(':id', $id);
        $stmt->bindValue(':name', $name);
        $stmt->bindValue(':age', $age);

        $stmt->execute();

    }

}

The following script will be added to the index.php file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
require 'vendor/autoload.php';
 
use phpPostgreSQLinsert\Connection as Connection;
use phpPostgreSQLinsert\PostgreSQLinsert as PostgreSQLInsertDB;
 
try {
    // connect to the PostgreSQL database
    $pdo = Connection::get()->connect();
    $insertDb = new PostgreSQLInsertDB($pdo);
   
    $id = $insertDb->insertPerson(1,'jame',13);

    echo 'The person has been inserted  <br>';

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

This script will insert a new row to the person table.

Finally, we update our autoload files using the command shown below:

1
composer dump-autoload -o

Testing the Code

We’re ready to test our code and see if our application works. Let’s review the sample dataset we’ll be using in our test:

1
2
3
4
5
6
7
persondb=> select * from person;
 id |  name   | age
----+---------+-----
  3 | ruby    |  32
  4 | jovanny |  32
  2 | ann     |  16
(3 rows)

We can use the following URL to test the application: http://localhost/phpPostgreSQLinsert/index.php.

The result should look like this:

At this point, we can’t be certain that our insert operation was successful. Let’s verify our results using the psql utility:

1
2
3
4
5
6
7
8
persondb=> select * from person;
 id |  name   | age
----+---------+-----
  3 | ruby    |  32
  4 | jovanny |  32
  2 | ann     |  16
  1 | jame    |  13
(4 rows)

We can see that we successfully inserted the values we defined in the index.php file.

Conclusion

If you’re developing PHP applications that work with PostgreSQL, you’ll probably want the ability to insert data from those applications. In this article, we showed you how to accomplish that in just a few simple steps. We explained how to insert Postgres data using PHP on ObjectRocket, and we supplied all the sample code you’ll need to get started. With our instructions to guide you, you’ll have no trouble inserting Postgres data from your own PHP applications.

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.