PostgreSQL PHP: Delete Data From a Table
Summary: This tutorial shows you how to delete data from a PostgreSQL table using the PHP PDO.
Steps for deleting data in the PostgreSQL using PHP PDO
To delete data from a PostgreSQL table in PHP, you use the following steps:
- Connect to the PostgreSQL database server by creating an instance of the PDO class.
- Prepare theDELETE statement for execution by calling the
prepare()method of the PDO object. Theprepare()method returns aPDOStatementobject. - Bind values to the DELETE statement by calling the
bindValue()method of thePDOStatementobject. - Execute the
DELETEstatement by calling theexecute()method. - Get the number of rows deleted using the
rowCount()method.
Deleting data examples
We will use thestocks table for the demonstration. If you have not created thestocks table yet, you can follow thecreating table tutorial.
Let’s create a new class named StockDB that contains all the methods for deleting data in a table.
<?phpnamespace PostgreSQLTutorial;/** * PostgreSQL PHP delete data demo */class StockDB { /** * PDO object * @var \PDO */ private $pdo; /** * Initialize the object with a specified PDO object * @param \PDO $pdo */ public function __construct($pdo){ $this->pdo = $pdo; } // other methods // ...}The followingdelete() method deletes a row specified by id from thestocks table
/** * Delete a row in the stocks table specified by id *@param int $id *@return the number row deleted */ public function delete($id) { $sql= 'DELETE FROM stocksWHERE id= :id'; $stmt= $this->pdo->prepare($sql); $stmt->bindValue(':id', $id); $stmt->execute(); return $stmt->rowCount(); }The followingdeleteAll() method deletes all rows from thestocks table.
/** * Delete all rows in the stocks table *@return int the number of rows deleted */ public function deleteAll() { $stmt= $this->pdo->prepare('DELETE FROM stocks'); $stmt->execute(); return $stmt->rowCount(); }Before running the methods, we query the data from thestocks table.
stocks=# SELECT * FROM stocksstocks-# ORDER BY id; id| symbol| company----+--------+----------------------- 1 | MSFT| Microsoft Corporation 2 | GOOGL| Alphabet Inc. 3 | YHOO| Yahoo! Inc. 4 | FB| Facebook, Inc.(4 rows)Use the following code in the index.php file to delete the row with id 1.
<?phprequire'vendor/autoload.php';use PostgreSQLTutorial\Connection as Connection;use PostgreSQLTutorial\StockDBas StockDB;try { // connect to the PostgreSQLdatabase $pdo= Connection::get()->connect(); // $stockDB= new StockDB($pdo); // delete a stockwith a specified id $deletedRows= $stockDB->delete(1); echo'The number of row(s) deleted: ' . $deletedRows .'<br>';}catch (\PDOException $e) { echo $e->getMessage();}The following is the output:
The number of row(s)deleted: 1We query data from the stocks table again to verify.
stocks=#SELECT * FROM stocksstocks-#ORDER BY id; id | symbol | company----+--------+---------------- 2 | GOOGL | Alphabet Inc. 3 | YHOO | Yahoo! Inc. 4 | FB | Facebook, Inc.(3 rows)The row with id 1 was deleted as expected.
In theindex.php file, modify the code to call thedeleteAll() method instead of thedelete() method and execute it. The following is the output of the script:
Thenumber ofrow(s) deleted:3The following shows the output when we query data from thestocks table.
stocks=# SELECT* FROM stocksstocks-# ORDER BY id; id | symbol | company----+--------+---------(0 rows)All rows in the stocks table have been deleted as expected.
In this tutorial, we have shown you how to delete data from a PostgreSQL table in the PHP application using PDO API.
Last updated on