How to use the PostgreSQL PDO Driver with PHP Part 4

Introduction

This is part four of the tutorial series explaining how to connect PHP to PostgreSQL with the PDO driver. The last section of the tutorial covered how to update data in the PostgreSQL database in PHP with the PDO driver. This part four will explain how to query data. It is strongly suggested that parts one through three of this tutorial be completed before attempting the exercises in this part four.

Prerequisites to Connect PHP to PostgreSQL with the PDO Driver

Completing sections one through three in this series.

How to Query Data in PostgreSQL Database

The last section of this tutorial covered updating the first record in the PostgreSQL database. This part will explain how to query and retrieve data against that database.

How to retrieve all the data in the PostgreSQL database using PHP

This section will show how to retrieve all the data inside the previously-created tblemployee table and then present it in a PHP table in the browser. To accomplish this function, create another function inside the class pgSQLoperation.

Now open the pgSQLoperation.php file and insert the following PHP script:

Here is a breakdown of the above code:

1. The above script will create a function named findAllEmployee();
2. This command again grabbed the global $pdo for executing the connection.
3. The SQL query was created using the SELECT function.
4. Next, the fetch() method is used to enable control of the returned results. In this case, the PDO::FETCH_ASSOC command was used so that the results will be returned as an array indexed by the name of the column.

Now that thefindAllEmployee function for retrieving records has bee set up, next modify the index.php by executing the following code:

The above PHP script will carry out a connection to the server and then execute the findAllEmployee function from the class pgSQLoperation. It then displays the results using a HTML table. Now add an error handler, if one should error occur during the execution.

Now test the file using the same process in part one of this tutorial. Navigate to the project directory via the terminal and execute the following command:

Now type localhost:9090 in the browser.

Retrieving specific data in the PostgreSQL database using PHP

The previous section covered how to retrieve all the data, or all of the rows, in the testDatabase. This section will go into explaining how to retrieve a single row in the tblemployee via a user_id. This is accomplished by creating a function inside the class pgSQLoperation and naming it findByUserId($id). This new function will require a parameter that will be a reference point of the SQL SELECT function.

Execute the following command to retrieve a specific row from the tblemployee :

Executing the above script, using the same process as explained in the previous section, will return a result that resembles the following:

Conclusion

This was part four in the tutorial series “How to connect PHP to PostgreSQL with the PDO driver.” This part four discussed how to retrieve data in PostgreSQL using PHP and the PDO driver. The article specifically covered how to query data in the PostgreSQL database. The tutorial explained how to retrieve all the data in the PostgreSQL database using PHP, a breakdown of that code, and how to retrieve specific data in the PostgreSQL database. Remember to add an error handler, should an error occur, when using the PHP script to connect to the server and executing the findAllEmployee function from the class pgSQLoperation.