How to update Postgres in PHP

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

Introduction

Updating data in Postgres using PHP is an easy task when you use the Postgres PDO_PGSQL driver. Find out more with this tutorial that illustrates how to successfully update Postgres PHP. You can connect to your Postgres database and update a table in just a few steps.

Prerequisite

Install and configure the following for your OS:

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

Enabling Postgres PDO_PGSQL driver

It’s likely that you won’t have to configure the Postgres PDO_PGSQL separately because it is packed within the Postgres PHP distribution. If that’s not the case in your situation, you can open the file php.ini and delete the semi-colon (;) at the start of the bash command like this:

1
;extension=php_pdo_pgsql.dll

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

PHP project structure

Use Composer to create the structure of your PHP project.

  • Go to the htdocs directory and create a folder there.

  • Name the folder phppostgres.

  • Make another folder.

  • Name that folder app.

  • Next, in phppostgre directory, make a compser.json file.

  • Append the compser.json file in the directory with this script here:

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

The app folder’s mapping to the namespace for phppostgres is now complete.

  • At the command line, go to the project directory and use this update command:
1
composer update

The update command in Composer will download the required libraries specified in the composer.json file you created earlier. Because specific libraries weren’t defined in the compser.json file to complete the update Postgres PHP steps, the following will occur:

  • Composer makes an autoload file automatically.

  • A folder vendor is created automatically and it holds the necessary third-party library.

The result should resemble this:

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

Make two files with these names in the app folder in this order:

  • Connection.php
  • phpupdate.php

  • Next, go to the project directory and make the index.php file. Name it phppostgres.

  • It should look similar to this:

Connect PHP to PostgreSQL database

Make a PHP app to PostgreSQL connection to update Postgres PHP. The example below is based on a database named inventory. The sample database includes product for the name of the table.

1
2
3
4
5
6
7
8
 id | product_name | product_price
----+--------------+---------------
  1 | hotdog       |           250
  2 | sausage      |           150
  3 | smoked fish  |           300
  4 | bacon        |           250
  5 | burger       |           100
(5 rows)
  • Use the parameters that are contained in the DB.ini file.
1
2
3
4
5
host=localhost  
port=5432
database=inventory
user=postgres
password=1234
  • Next, append the PHP Connection.php file 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
<?php
 
namespace phppostgres;
 
/**
 * 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('Db.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
        $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;
    }
 

 
}
  • After that, to update Postgres PHP, configure the file phpupdate.php. You’ll use it to revise the table in your Postgres database.
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
<?php
namespace phppostgres;

class phpupdate {

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

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


public function updateProduct($id, $product_name, $product_price) {
 
        // sql statement to update a row in the stock table
        $sql = 'UPDATE product '
                . 'SET product_name = :product_name, '
                . 'product_price = :product_price '
                . 'WHERE id = :id';
 
        $stmt = $this->pdo->prepare($sql);
 
        // bind values to the statement
        $stmt->bindValue(':product_name', $product_name);
        $stmt->bindValue(':product_price', $product_price);
        $stmt->bindValue(':id', $id);
        // update data in the database
        $stmt->execute();
 
        // return the number of row affected
        return $stmt->rowCount();
    }
}
  • Use this script to complete an index.php file configuration.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
 
require 'vendor/autoload.php';
 
use phppostgres\Connection as Connection;
 
use phppostgres\phpupdate as phpupdate;

try {
    $pdo = Connection::get()->connect();
    echo 'PHP application successfully connected to PostgreSQL database.';
   
    $updatePostgresDemo = new phpupdate($pdo);

    $affectedRows = $updatePostgresDemo->updateProduct(2, 'corned beef', '450');

    echo 'Number of row affected ' . $affectedRows;
   
} catch (\PDOException $e) {
    echo $e->getMessage();
}

The above script shows “2” as the updateProduct id; therefore, the row with “2” as its id will be updated.

  • Revise the autoload files with this command:
1
composer dump-autoload -o
  • Your response should resemble this:
1
Generated optimized autoload files containing 2 classes
  • Input this URL: http://localhost/phppostgres/index.php from your web browser to execute the index.php file.

  • You should see a result like this one:

  • In the Postgres shell, confirm the successful response.
1
2
3
4
5
6
7
8
id | product_name | product_price
----+--------------+---------------
1 | hotdog | 250
3 | smoked fish | 300
4 | bacon | 250
5 | burger | 100
2 | corned beef | 450
(5 rows)

Conclusion

The PDO_PGSQL is the best way to connect to a database server and in fact, the driver is what makes the update Postgres PHP database connection happen. It propels the PDO (Data Objects) interface. Since it already comes with PHP distribution, you’re pretty much set. Use it now and streamline your record updating processes from here on.

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.