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):
1 2 3 4 5 6 7 8 9 10 11 | 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:
The above script created the function
deleteEmployeethat accepts anidas the reference for the record being deleted.The
global $pdowill obtain the setting of the global variable $pdo.The
DELETEstatement is now passed to theprepare()statement of the global $pdo object for execution.The values from the parameter are passed using the
bindValue()method.The
DELETEstatement is then executed using the$stmt→execute()code.Lastly, the number of rows deleted during the
DELETEoperation are counted using therowCount()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:
1 2 3 4 5 6 7 8 9 | 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:
The above script first created the function
deleteEmployee, but this time it does not require a parameter.The
global $pdowill grab the setting of the global variable $pdo.The
DELETEstatement is now passed into theprepare()statement of the global $pdo object for execution.The
DELETEstatement is then executed using the$stmt→execute()code.Lastly, the number of the successfully-deleted rows in the
tblemployeeare counted using therowCount()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:
1 | SELECT * FROM tblemployee ORDER BY user_id; |
The results should resemble the following:
1 2 3 4 5 6 7 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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:
1 | php -S localhost:9090 |
Now type localhost:9090 into the browser. The browser should display something that resembles the following:
1 | You have deleted 1 employee(s) |
Now edit the index.php and execute the deleteAllEmployee function with the following script”
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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:
1 | 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