Update Postgres Data using PHP on ObjectRocket
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 | persondb=> select * from person; id | name | age ----+---------+----- 2 | risa | 29 3 | ruby | 32 4 | jovanny | 32 (3 rows) |
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