w3resource

PostgreSQL with php - a thorough introduction

Requirements for development and installation

To install PostgreSQL on Linux and Windows we have discussed here, detail installation process.

You can use Apache Web server, PHP, PostgreSQL and phpPgAdmin (which is an excellent tool to manage PHP-PostgreSQL projects) to build a basic web application.

Connect to PostgreSQL using PHP native functions

Assume we have the following table (book) under "postgres" database. Here is the structure of the table :

     column_name     | is_nullable |     data_type     | character_maximum_length | numeric_precision | numeric_scale
---------------------+-------------+-------------------+--------------------------+-------------------+---------------
 book_id             | NO          | character varying |                       10 |                   |
 author              | YES         | character varying |                       25 |                   |
 publisher           | YES         | character varying |                       25 |                   |
 date_of_publication | YES         | date              |                          |                   |
 price               | YES         | numeric           |                          |                 8 |             2
 

PHP provides many functions for working directly with PostgreSQL databases. Here are some functions :

pg_connect : The function is used to open a PostgreSQL connection.

Version: (PHP 4, PHP 5)

Syntax :

resource pg_connect ( string $connection_string [, int $connect_type ] )

Parameters:

  • connection_string: The connection_string can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace. The currently recognized parameter keywords are: host, hostaddr, port, dbname (defaults to value of user), user, password, connect_timeout, options, tty (ignored), sslmode, requiressl (deprecated in favor of sslmode), and service. Which of these arguments exist depends on your PostgreSQL version.
  • connect_type : If PGSQL_CONNECT_FORCE_NEW is passed, then a new connection is created, even if the connection_string is identical to an existing connection. If PGSQL_CONNECT_ASYNC is given, then the connection is established asynchronously. The state of the connection can then be checked via pg_connect_poll() or pg_connection_status().

Return Value: PostgreSQL connection resource on success, FALSE on failure.

pg_query: Execute a query

Version: (PHP 4 >= 4.2.0, PHP 5)

Syntax :

resource pg_query ([ resource $connection ], string $query )

Parameters :

  • connection : PostgreSQL database connection resource. When connection is not present, the default connection is used. The default connection is the last connection made by pg_connect() or pg_pconnect().
  • query : The SQL statement or statements to be executed.

Return Value: A query result resource on success or FALSE on failure.

Insert data into table with PHP

We will create a an HTML form and a PHP script to insert data into the "book" table. Here is the code (file name insert.php) :

<!DOCTYPE html>
<head>
<title>Insert data to PostgreSQL with php - creating a simple web application</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style>
li {listt-style: none;}
</style>
</head>
<body>
<h2>Enter information regarding book</h2>
<ul>
<form name="insert" action="insert.php" method="POST" >
<li>Book ID:</li><li><input type="text" name="bookid" /></li>
<li>Book Name:</li><li><input type="text" name="book_name" /></li>
<li>Author:</li><li><input type="text" name="author" /></li>
<li>Publisher:</li><li><input type="text" name="publisher" /></li>
<li>Date of publication:</li><li><input type="text" name="dop" /></li>
<li>Price (USD):</li><li><input type="text" name="price" /></li>
<li><input type="submit" /></li>
</form>
</ul>
</body>
</html>
<?php
$db = pg_connect("host=localhost port=5432 dbname=postgres user=postgres password=admin123");
$query = "INSERT INTO book VALUES ('$_POST[bookid]','$_POST[book_name]',
'$_POST[author]','$_POST[publisher]','$_POST[dop]',
'$_POST[price]')";
$result = pg_query($query); 
?>

Here is how the form to insert data looks :

insert data form

Use this form to enter some data into the "book" table.

Retrieving and updating data with PHP

In the next step, we will create a form so that we can see the detail of book records and update the existing data of the "book" table. For this, we will create a form where the user can supply the book id and it will show all the information stored in the database regarding that particular book. So, you will learn how to fetch data from the table, how to display that data with PHP and how to update the data.

Here is the code (file name enter-bookid):

<!DOCTYPE html>
 <head>
<title>Enter bookid to display data - creating a simple web application</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <style> li {list-style: none;} </style> </head> <body> <h2>Enter bookid and enter</h2> <ul> <form name="display" action="enter-bookid.php" method="POST" > <li>Book ID:</li><li><input type="text" name="bookid" /></li> <li><input type="submit" name="submit" /></li> </form> </ul> </body> </html> <?php $db = pg_connect("host=localhost port=5432 dbname=postgres user=postgres password=admin123"); $result = pg_query($db, "SELECT * FROM book where book_id = '$_POST[bookid]'"); $row = pg_fetch_assoc($result); if (isset($_POST['submit'])) { echo "<ul> <form name='update' action='enter-bookid.php' method='POST' > <li>Book ID:</li><li><input type='text' name='bookid_updated' value='$row[book_id]' /></li> <li>Book Name:</li><li><input type='text' name='book_name_updated' value='$row[book_name]' /></li><li>Author:</li><li><input type='text' name='author_updated' value='$row[author]' /></li> <li>Publisher:</li><li><input type='text' name='publisher_updated' value='$row[publisher]' /></li> <li>Date of publication:</li><li><input type='text' name='dop_updated' value='$row[date_of_publication]' /></li> <li>Price (USD):</li><li><input type='text' name='price_updated' value='$row[price]' /></li> <li><input type='submit' name='new' /></li>
</form> </ul>"; } if (isset($_POST['new'])) { $result1 = pg_query($db, "UPDATE book SET book_id = '$_POST[bookid_updated]', book_name = '$_POST[book_name_updated]', author = '$_POST[author_updated]', publisher = '$_POST[publisher_updated]',date_of_publication = '$_POST[dop_updated]', price = '$_POST[price_updated]'"); if (!$result1) { echo "Update failed!!"; } else { echo "Update successfull;"; } } ?>

Connect to PostgreSQL using PDO (PHP Data Objects)

As of version 5.1 PHP provides new database connection abstraction library, PHP Data Objects or PDO. PDO abstracts database access, and enables you to use code that can handle different types of databases.

Use the following PHP code to connect to PostgreSQL and select a database. Replace $dbname with the database name, $dbuser with your username and $dbpass with your password.

<?php
$dbuser = 'postgres';
$dbpass = 'abc123';
$host = 'localhost';
$dbname = 'postgres';
$dbh = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass);
?>

Handling connection errors:

If there is any connection errors, a PDOException object will be thrown. You may catch the exception if you want to handle the error condition, or you can leave it to global exception handler which can be set up via set_exception_handler().

Here is an example to handle errors:

<?php
$dbuser = 'postgres';
$dbpass = 'abc123';
$host = 'localhost';
$dbname='postgres';
$dbh = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass, array(
PDO::ATTR_PERSISTENT => true));	
?>

Query:

After the code connects to PostgreSQL and selects the database, you can run SQL queries and perform other operations. For example, the following PHP code runs a SQL query that extracts the first name, last name and country (order by country) from the user_details table, and stores the result in $sql.

<?php
try {
$dbuser = 'postgres'; $dbpass = 'abc123'; $dbhost = 'localhost'; $dbname='postgres';
$connec = new PDO("pgsql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); }catch (PDOException $e) { echo "Error : " . $e->getMessage() . "<br/>"; die(); } $sql = 'SELECT fname, lname, country FROM user_details ORDER BY country'; foreach ($connec->query($sql) as $row) { print $row['fname'] . " "; print $row['lname'] . "-->"; print $row['country'] . "<br>"; } ?>

So you have learned how to insert data from an HTML form and how to fetch, display and update data to PostgreSQL with PHP.

See also: SQL Injection, PHP Data Objects (PDO)

Previous: Connect to PostgreSQL
Next: Data Types



Follow us on Facebook and Twitter for latest update.