PostgreSQL PHP: Updating Data In a Table
Summary: in this tutorial, you will learn to update data in a PostgreSQL database table using PHP PDO.
Steps for updating data in a database table from a PHP application
To update data in a table, you use these steps:
- Connect to the PostgreSQL database server by creating an instance of the PDO class.
- Call the
prepare()method of the PDO object to prepare theUPDATE statement for execution. Theprepare()method returns aPDOStatementobject. - Pass the values to the
UPDATEstatement by calling thebindValue()method of thePDOStatementobject. - Execute the
UPDATEstatement by calling theexecute()method of thePDOStatementobject. - Get the number of rows updated using the
rowCount()method of thePDOStatementobject.
Updating data example
We will use thestocks table that we created in thecreating table tutorial for the demonstration.
TheupdateStock() method of thePostgreSQLPHPUpdate class updates the data in thestocks table based on a specified id.
<?phpnamespace PostgreSQLTutorial;/** * PostgreSQL PHP Update Demo */class PostgreSQLPHPUpdate { /** * PDO object * @var \PDO */ private $pdo; /** * Initialize the object with a specified PDO object * @param \PDO $pdo */ public function __construct($pdo){ $this->pdo = $pdo; } /** * Update stock based on the specified id * @param int $id * @param string $symbol * @param string $company * @return int */ public function updateStock($id, $symbol, $company){ // sql statement to update a row in the stock table $sql= 'UPDATE stocks ' . 'SET company = :company, ' . 'symbol = :symbol ' . 'WHERE id = :id'; $stmt= $this->pdo->prepare($sql); // bind values to the statement $stmt->bindValue(':symbol', $symbol); $stmt->bindValue(':company', $company); $stmt->bindValue(':id', $id); // update data in the database $stmt->execute(); // return the number of row affected return $stmt->rowCount(); }}We use thePostgreSQLPHPUpdate class in theindex.php file as follows:
<?phprequire 'vendor/autoload.php';use PostgreSQLTutorial\Connection as Connection;use PostgreSQLTutorial\PostgreSQLPHPUpdate as PostgreSQLPHPUpdate;try { // connect to the PostgreSQL database $pdo= Connection::get()->connect(); // $updateDemo= new PostgreSQLPHPUpdate($pdo); // insert a stock into the stocks table $affectedRows= $updateDemo->updateStock(2, 'GOOGL', 'Alphabet Inc.'); echo 'Number of row affected ' . $affectedRows;}catch (\PDOException $e) { echo $e->getMessage();}In the index.php script, we connected to the PostgreSQL database and called theupdateStock() method of thePostgreSQLPHPUpdate class to update the company name of the stock id 2 fromGoogle Inc. toAlphabet Inc.
Before running the script, we query data from the stocks table to see its current data.
SELECT id, symbol, companyFROM stocks;id | symbol | company----+--------+----------------------- 1 | MSFT | Microsoft Corporation 2 | GOOG | Google Inc. 3 | YHOO | Yahoo! Inc. 4 | FB | Facebook, Inc.(4 rows)Launch theindex.php file in a web browser; we get the following output.
Number of row affected 1Let’s recheck the stocks table.
id | symbol | company----+--------+----------------------- 1 | MSFT | Microsoft Corporation 2 | GOOGL | Alphabet Inc. 3 | YHOO | Yahoo! Inc. 4 | FB | Facebook, Inc.(4 rows)The company name of stock with id 2 has been updated to the new one.
In this tutorial, you have learned how to update data in a PostgreSQL table using the prepared statement in PHP PDO.
Last updated on