DELETE Postgres data using PHP on ObjectRocket

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

Introduction

If you’re writing PHP code that will interact with PostgreSQL, it’s important to know how to perform certain database operations. One key operation that you should be able to execute from an application is the deletion of records. In this article, we’ll show you how to delete Postgres data using PHP on ObjectRocket and provide code examples to get you started.

Prerequisites

Before diving into the code, let’s review the prerequisites that are necessary for this task. You’ll need to install and configure a few items on your machine:

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

Enabling PostgreSQL PDO_PGSQL Driver

If we want to be able to delete Postgres data using PHP, we’ll need to enable the driver that will bridge PostgreSQL to PHP. We can do this by modifying a PHP configuration file called php.ini. All we need to do is locate the following text inside the configuration file: ;extension=php_pdo_pgsql.dll. We’ll remove the semi-colon at the beginning of the line of text.

Connect PHP to Postgres Instance ObjectRocket

In this section, we’ll connect PHP to our instance of Postgres on ObjectRocket.

Create Project Structure

The first thing we’ll do is create our PHP project structure using Composer:

  • We’ll start by creating a folder named phppostgresdelete inside our htdocs directory.
  • Then, we’ll create another new folder and name it app.
  • Inside the phppostgresdelete folder, we’ll create a json file named composer.json and add the following code to it:
1
2
3
4
5
6
7
{
    "autoload": {
        "psr-4": {
            "phppostgresdelete\": "app/"
        }
    }
}

NOTE: The code shown above creates the namespace mapping of our basic PHP application.

  • Next, we’ll update our application using the command line and download any third-party libraries as described in the composer.json file. We can do this by navigating to our project directory using the terminal and executing the following command:
1
composer update

NOTE: After executing the above command, we should see something 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

Right after executing the command, an autoload file will be generated automatically. A directory named vendor will also be generated automatically. This directory stores all the necessary third-party library related files.

Our next task will be to create the following files in the app folder:

  • Connection.php
  • postgresdelete.php
  • db.ini

Finally, we’ll go to our project directory phppostgresdelete and create an index.php file that will serve as the entry point of our basic application.

At this point, our project structure should look like the following:

Connecting PHP to Postgres on ObjectRocket

Now that we have our project structure in place, let’s configure our created files. We’ll add the PHP script and the configuration details needed for our connection.

Before we can proceed, we need to gather the details about our Postgres server instance. These details are found inside ObjectRocket’s Mission Control:

NOTE: We’ll use the above details for our database connection, In this case, we’ll be using the details in the db.ini file, as shown below:

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

The PHP script shown below should be added to our Connection.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
48
49
50
51
52
53
54
55
56
57
58
59
<?php
 
namespace phppostgresdelete;
 
/**
 * 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 inside the db.ini via 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 below script distributes the needed details for our connection string
        $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($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::$con) {
            static::$con = new static();
        }
 
        return static::$con;
    }
 
    protected function __construct() {
       
    }
 
}

Then, we ad the following PHP script to the postgresdelete.php file. This is the script that performs the actual delete operation:

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
<?php

namespace phpdeletepostgres;


class postgresdelete {


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

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

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

    /**
     * Delete a row in the stocks table specified by id
     * @param int $id
     * @return the number row deleted
     */

    public function phpdelete($id) {
        $sql = 'DELETE FROM person WHERE id = :id';
 
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':id', $id);
 
        $stmt->execute();
 
        return $stmt->rowCount();
    }

}

We then use the following script in our 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 phppostgresdelete\Connection as Connection;
use phppostgresdelete\postgresdelete as postgresdelete;
 
try {
    // this code will connect us to the PostgreSQL database
    $pdo = Connection::get()->connect();
   
    $delete = new postgresdelete($pdo);
    // we will delete a car with a specified id which is 1
    $deletedRows = $delete->phpdelete(1);
    echo 'We delete : ' . $deletedRows .'row(s)' . '<br>';
   
} catch (\PDOException $e) {
    echo $e->getMessage();
}

Finally, we update the autoload files using the following command:

1
composer dump-autoload -o

We’ll receive the following in response:

1
Generated optimized autoload files containing 1 classes

Testing the Code

Before we proceed with the testing, let’s check out the following table:

1
2
3
4
5
6
7
 id |  name   | age
----+---------+-----
  1 | rommel  |  29
  2 | risa    |  29
  3 | ruby    |  32
  4 | jovanny |  32
(4 rows)

We’ll use the URL shown below to test the application: http://localhost/phppostgresdelete/index.php.

We should see something that looks like the following.

We can verify that our delete operation was successful using the psql utility. If we issue a SELECT statement, we should see something like the following:

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)

Conclusion

Many PHP applications are required to interact with a PostgreSQL database, so it’s important to know how to connect to a Postgres instance and perform various database operations. In this article, we focused on the delete operation, and we showed you how to delete Postgres data using PHP on ObjectRocket. With our detailed instructions and code examples, you’ll be ready to create your own PHP applications that work with PostgreSQL.

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.