How to Connect PHP with PostgreSQL using the PDO Driver - Part Three
Introduction
This tutorial is Part 3 of the series “How to Connect PHP with PostgreSQL using the PDO Driver.” Before we get started, let’s remind ourselves of four of PostgreSQL’s object-relational database management system (ORDBMS) notables. It’s open-source, reliably accurate, compatible with popular operating systems, and well respected in terms of adhering to database standards.
Also worth mentioning are two main benefits of the PHP Data Objects (PDO) driver. It allows you to access PHP databases with its steady, simple interface. The PHP PDO driver works with many database types and that simplicity translates into flexibility for you to retrieve data from the database of your choice. Combine the these two: PostgreSQL and the PDO driver and you have a super-easy way to bring to the surface the functions that are database-specific.
In Part 2 of the tutorial “How to use PostgreSQL PDO Driver with PHP,” you discovered how to add data to a database in PostgreSQL. Today, in Part 3, you’ll learn how to connect PHP PostgreSQL PDO and update data in a PostgreSQL database. Let’s proceed.
Prerequisites
Download and install the PostgresSQL ORDBMS for your OS.
Download and install the PDO_PGSQL Driver.
Complete tutorials in Parts 1 and 2 of the series “How to Use PostgreSQL PDO Driver with PHP.”
Database PostgreSQL data updating process
In Part 2 of the tutorial series, you added a record to the example
tblemployeetable.Part 3 begins now. You’re ready to update the record. Use the script of PHP below to make a function for pgSQLoperation.php.
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 | public function updateEmployee($id, $employeeNo, $firstname, $lastname, $email) { global $pdo; // sql statement to update a row in the tblemployee table $sql = 'UPDATE tblemployee ' . 'SET employeenumber = :employeenumber, ' . 'firstname = :firstname, ' . 'lastname = :lastname, ' . 'email = :email ' . 'WHERE user_id = :id'; $stmt = $pdo->prepare($sql); // bind values to the statement $stmt->bindValue(':employeenumber', $employeeNo); $stmt->bindValue(':firstname', $firstname); $stmt->bindValue(':lastname', $lastname); $stmt->bindValue(':email', $email); $stmt->bindValue(':id', $id); // update data in the database $stmt->execute(); // return the number of row affected return $stmt->rowCount(); } |
Here are the step-by-step details of the above script:
1) Function updateEmployee part of the script at the very top, is the function identifies the table that will be updated. The parameters within the function are the updates that are to be made.
2) Function global $pdo script segment mirrors the global $pdo variable setting.
3) The global object $pdo has a statement prepare(), and this is where the statement UPDATE is passed.
4) The parameter passes the values with the method bindValue().
5) Next, use the $stmt->execute() function to complete the statement UPDATE execution.
6) Lastly, verify the row that was updated.
Make an index.php modification
- Connect PHP PostgreSQL PDO and create the script to change the index.php.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | use PostgreSQLPHP\Connection as Connection; use PostgreSQLPHP\pgSQLoperation as pgSQLoperation; try { // connect to the PostgreSQL database $pdo = Connection::get()->connect(); $updateEmp = new pgSQLoperation($pdo); // update an employee data in the tblemployee table $affectedRows = $updateEmp->updateEmployee(1, 1, 'rommel','galisanao', 'rmgalisanao@yahoo.com'); echo 'Number of row affected ' . $affectedRows; } catch (\PDOException $e) { echo $e->getMessage(); } |
Here are the details of the above script:
1) A basic connect PHP PostgreSQL PDO to the server is made.
2) The class pgSQLoperation executes the function updateEmployee.
3) The updateEmployee completes the update for the identified record. Specifically, “rmgalisanao30@gmail.com” will modify or update to become “rmgalisanao@yahoo.com for the field value of email.
NOTE: Afterwards, you can print the results showing which rows in the table were updated.
Confirm the number of records updated
Back in Part 1 of series on “How to use PostgreSQL PDO Driver with PHP,” you learned how to check the file. Let’s do that again right here to check if the script to connect PHP PostgreSQL PDO and update worked properly.
Open a new terminal window, then go to the directory where the project is located. Use the command below:
1 | php -S localhost:9090 |
- Next, in the address bar of the browser, enter “localhost:9090”. The result should look similar to this:
1 | Number of row affected 1 |
Verify the details of the updated records
Login to the PostgreSQL server.
Complete a statement SQL
SELECTcommand like this:
1 | SELECT * FROM tblemployee; |
The result should look something like this.
1 2 3 4 5 6 7 | user_id | employeenumber | firstname | lastname | email ---------+----------------+-----------+-----------+----------------------- 1 | 1 | rommel | galisanao | rmgalisanao@yahoo.com (1 row) |
Conclusion
Great job! You’ve successfully completed Part 3 in the series on “How to use PostgreSQL PDO Driver with PHP.” You learned how to connect PHP PostgreSQL PDO to update records in a PostgreSQL database. You also practiced the steps on how to confirm the number of records and view the details of the updated fields. Streamlining your coding when updating your PostgreSQL databases is a realistic goal you achieved and can continue to attain on a regular basis. Now freely use what you know often and reap the benefits.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started