Update Postgres Data using PHP on ObjectRocket

Written by Data Pilot

May 04, 2020

ObjectRocket
PHP
Postgres

Introduction

If you’re creating a PHP application that interacts with a Postgres instance on ObjectRocket, you might need your application to update Postgres data. Fortunately, this task couldn’t be easier. In this article, we’ll show you how to update Postgres data using PHP on ObjectRocket, and we’ll provide sample code to get you started.

Prerequisites

A few items need to be installed and set up before you proceed with this tutorial:

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

You’ll also need some knowledge of PostgreSQL to get the most out of this tutorial.

How to Enable PostgreSQL PDO_PGSQL Driver

To enable the PDO_PGSQL driver, we need to modify the PHP file named php.ini. All we need to do is remove the semi-colon at the beginning of the following text: ;extension=php_pdo_pgsql.dll. Once you make this edit, restart your Apache server to update the changes made to PHP configuration file.

Creating the PHP Project Structure

We will use Composer to build the required parts of our PHP project. We’ll perform the following steps in sequence:

First, we’ll create a folder inside the htdocs directory and name it phpPostgreSQLupdate.
Inside the phpPostgreSQLupdate folder, we’ll create another new folder and name it app.
We’ll also create a composer.json in the phpPostgreSQLupdate folder. We will add the code shown below to this file:

1
2
3
4
5
6
7

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

This completes the app folder’s mapping to the phpPostgreSQLupdate‘s namespace.

Next, we’ll navigate to the project directory on the command line and execute the following command:

1	composer update

The Composer update command will gather and download the necessary libraries specified in the composer.json file we created in the previous section. Since we did not specify any library files in that file, it will just perform the following.

Automatically create autoload files.
Automatically create a vendor folder that will contain the necessary third-party libraries.

NOTE: Right after executing the update command, we should see a notification that looks like this in the terminal:

1
2
3
4

Loading composer repositories with package information
Updating dependencies (including require-dev)
Nothing to install or update
Generating autoload files

Once the update is complete, we’ll enter the app folder and create the following files:

Connection.php
PostgreSQLupdate.php
dbupdate.ini

Last but not least, we’ll create an index.php file in our phpPostgreSQLupdate project directory.

Our project structure should look something like this.

Connecting PHP to Postgres on ObjectRocket

To connect our PHP application to our Postgres instance on ObjectRocket, we’ll need to supply the details that can be found in the “CONNECT” tab of the ObjectRocket Mission Control panel.

These details will be added to our dbupdate.ini file:

1
2
3
4
5

host=ingress.w98sujpz.launchpad.objectrocket.cloud
port=4149
database=person
user=pguser
password=1234

Next, we’ll set up the configuration file in the Connection.php file, adding the following code to it:

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('dbupdate.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 then add the PHP script shown below to handle the update operation in the PostgreSQLupdate.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
39
40
41
42
43
44
45
46
47

<?php

namespace phpPostgreSQLupdate;

class PostgreSQLupdate {

/**
* PDO object
* @var \PDO
*/
private $pdo;

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

/**
* update a row in the person table via id
* @param int $id
* @return the number of updated row
*/
public function phpupdate($id, $name, $age) {
$sql = 'UPDATE person '
. 'SET name =:name, '
. 'age =:age '
. 'WHERE id =:id ' ;

$stmt = $this->pdo->prepare($sql);

// this code bind the values to the statement
$stmt->bindValue(':id', $id);
$stmt->bindValue(':name', $name);
$stmt->bindValue(':age', $age);

$stmt->execute();

// this return the number of affected rows.
return $stmt->rowCount();
}

}

After that, we add the following script to the index.php file:

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 phpPostgreSQLupdate\Connection as Connection;
use phpPostgreSQLupdate\PostgreSQLupdate as PostgreSQLupdateDb;

try {
// this code will connect us to the PostgreSQL database
$pdo = Connection::get()->connect();

$updatePostgreSQL = new PostgreSQLupdateDb($pdo);
// we will delete a car with a specified id which is 1
$updatedRows = $updatePostgreSQL->phpupdate(2, 'ann', 16);
echo 'We updated : ' . $updatedRows .' row(s)' . '<br>';

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

Finally, we revise our autoload files using this command:

1	composer dump-autoload -o

Testing the Code

It’s time to test out our application and verify that the update operation is successful. Let’s take a look at the following table before we test our application:

1
2
3
4
5
6
7

Now, let’s use the following URL to test the application: http://localhost/phpPostgreSQLupdate/index.php.

We should see something like this:

We can use psql to execute a simple SELECT statement to verify that our application is working properly:

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 see that the name of the person with an id of 2 was updated from ruby to ann.

Conclusion

If you’re a PHP developer working with a Postgres instance on ObjectRocket, it’s important to know how to update data from your applications. In this article, we explained how to update Postgres data using PHP on ObjectRocket, and we provided all the code you’ll need to follow along on your machine. With our instructions and examples, you’ll be able to perform update operations from your own PHP applications.

Pilot the ObjectRocket Platform Free!

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

Get Started

Update Postgres Data using PHP on ObjectRocket

Introduction

Prerequisites

How to Enable PostgreSQL PDO_PGSQL Driver

Creating the PHP Project Structure

Connecting PHP to Postgres on ObjectRocket

Testing the Code

Conclusion

Pilot the ObjectRocket Platform Free!

Keep in the know!

Services

Platform

Company

Resources

Support