How to use the PostgreSQL PDO Driver with PHP - Part 5

Introduction

This is part five in the tutorial series explaining how to use PHP with the PostgreSQL PDO driver. Part four of this article covered how to retrieve rows from the tblemployee in the PostgreSQL testDatabase. This final part in the series will cover how to delete both a specific data row and all the data rows within the tblemployee.

Prerequisites to use PHP with the PostgreSQL PDO Driver for Deleting Specific Records and All Data

  • The completion and thorough understanding of the previous four parts of this tutorial series.

How to Delete a Specific Record from the ‘tblemployee’

This section will explain how to delete a specific data row within the tblemployee.

If the tblemployee table has not already been created, refer to this creating table tutorial.

Execute the following script to create a function inside the class pgSQLoperation and name it deleteEmployee($id):

public function deleteEmployee($id){
global $pdo;

$tmt = $pdo->prepare('DELETE FROM tblemployee WHERE user_id = :id');

$stmt->bindValue(':id', $id);

$stmt->execute();

return $stmt->rowCount();
}

The following is a step-by-step explanation of the above script:

  1. The above script created the function deleteEmployee that accepts an id as the reference for the record being deleted.

  2. The global $pdo will obtain the setting of the global variable $pdo.

  3. The DELETE statement is now passed to the prepare() statement of the global $pdo object for execution.

  4. The values from the parameter are passed using the bindValue() method.

  5. The DELETE statement is then executed using the $stmt→execute() code.

  6. Lastly, the number of rows deleted during the DELETE operation are counted using the rowCount() method.

How to Delete All Records from the ‘tblemployee’

The previous section covered how to delete a specific data row. This section will explain how to delete all the data within the tblemployee using the following script:

public function deleteAllEmployee(){
global $pdo;

$tmt = $pdo->prepare('DELETE FROM tblemployee');

$stmt->execute();

return $stmt->rowCount();
}

The following is a step-by-step explanation of the above script:

  1. The above script first created the function deleteEmployee, but this time it does not require a parameter.

  2. The global $pdo will grab the setting of the global variable $pdo.

  3. The DELETE statement is now passed into the prepare() statement of the global $pdo object for execution.

  4. The DELETE statement is then executed using the $stmt→execute() code.

  5. Lastly, the number of the successfully-deleted rows in the tblemployee are counted using the rowCount() method.

Now check the dataset, before running the PHP files, by logging to the PostgreSQL deployment and accessing testDatabse via the command line with the following SQL command:

SELECT * FROM tblemployee ORDER BY user_id;

The results should resemble the following:

user_id | employeenumber | firstname | lastname | email
---------+----------------+-----------+-----------+--------------------
1 | 2 | risa | galisanao | example@gmail.com
2 | 3 | raizel | galisanao | example2@gmail.com
3 | 4 | yeshua | galisanao | example3@gmail.com
4 | 5 | abishai | galisanao | exampl4e@gmail.com
(4 rows)

Next, modify the index.php file and execute the first function that was deleteEmployee with the following command:

require 'vendor/autoload.php';

use PostgreSQLPHP\Connection as Connection;
use PostgreSQLPHP\pgSQLoperation as pgSQLoperation;

try {

// connect to the PostgreSQL database
$pdo = Connection::get()->connect();
$employeeDB = new pgSQLoperation($pdo);

// delete employee details in the tblemployee with id of 1
$deleteEmployee = $employeeDB->deleteEmployee(1);
echo 'You have deleted: ' . $deleteEmployee . ' employee(s)' . '
'
;
} catch (\PDOException $e) {
echo $e->getMessage();
}

The above PHP script connects to the server and then executes the deleteEmployee function from the class pgSQLoperation while providing the needed parameters as shown above.

NOTE: An error handler can be performed if an error should occur during execution.

Next, test the file using the process explained in previous section of this tutorial series. Navigate to the project directory, via the terminal, and execute the following command:

php -S localhost:9090

Now type localhost:9090 into the browser. The browser should display something that resembles the following:

You have deleted 1 employee(s)

Now edit the index.php and execute the deleteAllEmployee function with the following script”

require 'vendor/autoload.php';

use PostgreSQLPHP\Connection as Connection;
use PostgreSQLPHP\pgSQLoperation as pgSQLoperation;

try {

// connect to the PostgreSQL database
$pdo = Connection::get()->connect();
$employeeDB = new pgSQLoperation($pdo);

// delete all employee details in tblemployee table
$deleteEmployee = $employeeDB->deleteAllEmployee;
echo 'You have deleted: ' . $deleteEmployee . ' employee(s)' . '
'
;
} catch (\PDOException $e) {
echo $e->getMessage();
}

The above PHP script connects to the server and then executes the deleteAllEmployee function from the class pgSQLoperation and will handle an error accordingly, should one occur.

Now follow the same procedure executed in the previous step to test the file.

The browser should display something that resembles the following:

You have deleted 3 employee(s)

Conclusion

This is the last section in the five-part tutorial series explaining how to use PHP with the PostgreSQL PDO driver. This section covered how to perform the delete operation for both a specific data row and multiple data rows from the tblemployee table using PHP. This last section of the series also provided a step-by-step breakdown of the scripts used for both functions. Remember to visually confirm the successful execution of the deleted data rows after completing either function.

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.