How to Delete Postgres in PHP

Written by Data Pilot

February 28, 2020

Postgres PHP

Introduction

The PHP PDO API is actually three separate acronyms that stand for hypertext preprocessor data objects application programming interface. While that certainly is a mouthful, the PHP PDO API function is easiest explained as an application that interacts with object relational database management systems. One of its functions is the updating of information on various SQL systems, such as Postgres, by using a simple PHP script to connection to Postgres. This tutorial will explain how to delete Postgres in PHP via the PHP PDO API and provide working examples.

Prerequisites

A text editor like Visual Studio Code is required to execute the examples used in this tutorial.
PostgreSQL must be correctly installed on the local machine.
Internet access is needed to download the requisite files.
XAMPP must be installed and working on the local machine.
Composer, shown in the below screenshot, is required for managing project dependencies and must also be properly installed on the local machine.

Postgres PDO_PGSQL driver

This section will explain how to enable the Postgres PDO_PGSQL driver in the current PHP distribution by editing just one line of code.

Locate the following text in the php.ini extension sections and remove the beginning character, the semi-colon (;), as shown here:

1	;extension=php_pdo_pgsql.dll

Project Structure

Now execute the following steps in Composer to create the project structure file:

1) Create a new folder named phpdeletepostgres in the htdocs directory. 2) Create a new JSON file named compser.json in the project directory phpdeletepostgres. 3) Create an index.php file in the project directory phpdeletepostgres. 4) Create a new folder named ‘app’. 5) Create three files inside of the ‘app’ folder and name each one Connection.php, dbParam.ini and deletepostgres.php respectively.

Now edit the composer.json file by appending the following script:

1
2
3
4
5
6
7

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

Note that the above script works to create a mapping for the ‘app’ directory for the phpdeletepostgres namespace.

Next, navigate to the project directory via the command line and execute the following command:

1	composer update

Composer will download the required libraries as declared inside the composer.json file and generate the autoload file and a new directory called “vendor“.

Note that since no libraries were defined in the composer.json file, only the auto-load files will be generated.

The output should resemble the following image:

The project structure should now resemble the following:

Create Sample dataset

The following commands will create the sample database and the corresponding table:

Execute the create database cars command to create the database.
Now create a ‘car’ table with the following command:

1
2
3
4
5

CREATE TABLE car(
id INT PRIMARY KEY NOT NULL,
car_brand VARCHAR NOT NULL,
car_model VARCHAR NOT NULL
);

Now insert the sample data as follows:

1
2
3
4
5
6
7

INSERT INTO car (id, car_brand, car_model)
VALUES
(1,'toyota','Montero'),
(2,'toyota','wigo'),
(3,'Honda','NSX'),
(4,'Ferarri','Enzo'),
(5,'Ferrari','F12 Berlinetta');

The ‘car’ table should resemble the following:

1
2
3
4
5
6
7
8

PHP Postgres Connection

This section will explain how to configure the PHP and Postgres database connection.

First, execute the following command to edit the dbParam.ini file to establish the connection parameters:

1
2
3
4
5

host=localhost
port=5432
database=inventory
user=postgres
password=1234

Now append the following PHP script in the 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

<?php

namespace phpdeletepostgres;

/**
* 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('dbParam.ini');
if ($paramDb === false) {
throw new \Exception("Error reading database config file, please check dbParam.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;
}

}

Next, configure the phpdeletepostgres.php file for deleting specific data in a Postgres table as shown here:

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 deletepostgres {

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

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

/**
* Deletes a row in the car table via defined id
* @param int $id
* After deleting we then @return the number row deleted
*/
public function delete($id) {
$sql = 'DELETE FROM car WHERE id = :id';

$stmt = $this->pdo->prepare($sql);
$stmt->bindValue(':id', $id);

$stmt->execute();

return $stmt->rowCount();
}

}

Now execute the following script to configure 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 phpdeletepostgres\Connection as Connection;
use phpdeletepostgres\deletepostgres as deletepostgres;

try {
// this code will connect us to the PostgreSQL database
$pdo = Connection::get()->connect();
//
$deletepostgres = new deletepostgres($pdo);
// we will delete a car with a specified id which is 1
$deletedRows = $deletepostgres->delete(1);
echo 'We delete : ' . $deletedRows . 'row(s) ' . '<br>';

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

Here the index.php file deleted a car from the table that had an ID equal to “1”.

Now execute the following command to update the auto-load files again:

1	composer dump-autoload -o

The output should resemble the following:

1	Generated optimized autoload files containing 2 classes

Now launch the index.php file from the web browser using the following URL: http://localhost/phpdeletepostgres/index.php

The output should now look like the following image:

Now query the car table again to verify the results via the Postgres shell:

1
2
3
4
5
6
7

Conclusion

This tutorial explained how to delete Postgres in PHP via the PHP PDO API and provided working examples. The tutorial covered how to enable the Postgres PDO_PGSQL driver and execute the required steps to create the project structure file in Composer. The article then explained how to create the sample dataset used in the tutorial and then how to configure the PHP and Postgres database connection. Finally, the tutorial covered how to verify the result via the Postgres shell. Remember that only the first character, the semicolon, in the php.ini extension sections must be removed to enable the Postgres PDO_PGSQL driver.

Pilot the ObjectRocket Platform Free!

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

Get Started

How to Delete Postgres in PHP

Introduction

Prerequisites

Postgres PDO_PGSQL driver

Project Structure

Create Sample dataset

PHP Postgres Connection

Conclusion

Pilot the ObjectRocket Platform Free!

Keep in the know!

Services

Platform

Company

Resources

Support