Summary: in this tutorial, you will learn how 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 the UPDATE 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 the stocks table that we created in the creating table tutorial for the demonstration.
The updateStock() method of the PostgreSQLPHPUpdate class updates the data in the stocks table based on a specified id.
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 | <?php namespace 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 the PostgreSQLPHPUpdate class in the index.php file as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | <?php require '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 call the updateStock method of the PostgreSQLPHPUpdate class to update company name of the stock id 2 from Google Inc. to Alphabet Inc.
Before running the script, we query data from the stocks table to see its current data.
1 2 3 4 | SELECT id, symbol, company FROM stocks; |
1 2 3 4 5 6 7 | id | symbol | company ----+--------+----------------------- 1 | MSFT | Microsoft Corporation 2 | GOOG | Google Inc. 3 | YHOO | Yahoo! Inc. 4 | FB | Facebook, Inc. (4 rows) |
Launch the index.php file in a web browser, we get the following output.
1 | Number of row affected 1 |
Let’s check the stocks table again.
1 2 3 4 5 6 7 | 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, we have shown you how to update data in a PostgreSQL table using prepared statement in PHP PDO.