PostgreSQL PHP: Querying Data
Summary: in this tutorial, you will learn to query data from the PostgreSQL database in PHP using PDO.
Querying all rows in a table
To query all rows from a table in the PostgreSQL database, you use the following steps:
- First,connect to the PostgreSQL database by creating a new PDO object.
- Second, call the
query()method of the PDO object. The query() method accepts aSELECT statement as the argument. The query method returns aPDOStatementobject. - Third, fetch the next rows from the result by calling the fetch() method of the PDOstatement object. The fetch_style argument of the
fetch()method controls how the result returned. For example, thePDO::FETCH_ASSOCinstructs thefetch()method to return the result set as an array indexed by column name.
We will use thestocks table created in thecreating table tutorial for the demonstration. Let’s create a new classStockDB for storing all the methods that select data from thestocks table.
The followingall() method selects all rows in thestocks table.
/** * Return all rows in the stocks table * @return array */ public function all(){ $stmt= $this->pdo->query('SELECT id, symbol, company ' . 'FROM stocks ' . 'ORDER BY symbol'); $stocks= []; while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { $stocks[] = [ 'id' => $row['id'], 'symbol' => $row['symbol'], 'company' => $row['company'] ]; } return $stocks; }To test theall() method, we use the following code in theindex.php file.
<?phprequire 'vendor/autoload.php';use PostgreSQLTutorial\Connection as Connection;use PostgreSQLTutorial\StockDB as StockDB;try { // connect to the PostgreSQL database $pdo= Connection::get()->connect(); // $stockDB= new StockDB($pdo); // get all stocks data $stocks= $stockDB->all();}catch (\PDOException $e) { echo $e->getMessage();}?><!DOCTYPE html><html> <head> <title>PostgreSQL PHP Querying Data Demo</title> <link rel="stylesheet" href="https://cdn.rawgit.com/twbs/bootstrap/v4-dev/dist/css/bootstrap.css"> </head> <body> <divclass="container"> <h1>Stock List</h1> <tableclass="table table-bordered"> <thead> <tr> <th>ID</th> <th>Symbol</th> <th>Company</th> </tr> </thead> <tbody> <?phpforeach ($stocksas $stock): ?> <tr> <td><?phpecho htmlspecialchars($stock['id']) ?></td> <td><?phpecho htmlspecialchars($stock['symbol']);?></td> <td><?phpecho htmlspecialchars($stock['company']);?></td> </tr> <?phpendforeach;?> </tbody> </table> </div> </body></html>The first part of the index.php is to connect to the PostgreSQL database and get all rows in the stocks table. The second part shows the data in HTML format.
The following screenshot illustrates the output of the index.php file.
Querying a specific row in a table
To query a specific row in a table, you use the following steps:
- First, connect to the PostgreSQL database by creating a new PDO object.
- Next, prepare the SELECT statement for execution by calling the
prepare()method of the PDO object. Theprepare()method returns an instance of the PDOStatement class. - Then, bind the values to the statement by using the
bindValue()method. - After that, execute the SELECT statement by calling the
execute()method of the PDOStatement object. - Finally, fetch the next row in the result using the
fetch()method. If the SELECT statement returns 1 row, you can use the fetchObject() method to return an object.
The followingfindByPK() method selects a row in the stocks table based on a specified id and returns a Stock object.
/** * Find stock by id * @param int $id * @return a stock object */ public function findByPK($id){ // prepare SELECT statement $stmt= $this->pdo->prepare('SELECT id, symbol, company FROM stocks WHERE id = :id'); // bind value to the :id parameter $stmt->bindValue(':id', $id); // execute the statement $stmt->execute(); // return the result set as an object return $stmt->fetchObject(); }To test thefindByPK() method, we create a new PHP file namedstock.php.
<?phprequire 'vendor/autoload.php';use PostgreSQLTutorial\Connection as Connection;use PostgreSQLTutorial\StockDB as StockDB;try { // connect to the PostgreSQL database $pdo= Connection::get()->connect(); // $stockDB= new StockDB($pdo); // get all stocks data $stock= $stockDB->findByPK(1); var_dump($stock);}catch (\PDOException $e) { echo $e->getMessage();}Run the stock.php file, we get the following result.
object(stdClass)[6] public 'id' => int 1 public 'symbol' => string 'MSFT' (length=4) public 'company' => string 'Microsoft Corporation' (length=21)In this tutorial, you have learned various ways to query data from the tables in the PostgreSQL database using PHP PDO.
Last updated on