Connect to a PostgreSQL Database using PHP and pg_connect

Introduction

Returning data in a variety of forms to meet your needs is what can happen when you run a connect PostgreSQL database PHP pgconnect. It’s all up to you. For example, specifically, you can return a PostgreSQL database table into a table in HTML form. Use the pgconnect PHP method to connect to your database in PostgreSQL and save time displaying data in the way that you want.

If you’re already familiar with the steps of how to connect a PostgreSQL database with PHP using pgconnect, feel free to skip the details of this tutorial and go straight to Just the Code.

Prerequisites

  • If you haven’t already, install PostgresSQL.

  • Next, confirm the installation by opening a terminal window and inputting the command psql.

psql -V

NOTE: Create a database and add some data to it so you can test the method call pgconnect using the examples in this tutorial.

  • Check that on your Apache server, the latest ready PHP Version 7.x is running. Confirm the version in a terminal window like this:
php -v
  • Be sure an Apache server localhost is active on your OS. To do this, open a browser tab 127.0.0.1 and verify that a response is returned.

  • Use the cd command to log into your Apache server’s root directory.

  • Construct a script in PHP. You’ll use it to make a database PostgreSQL connection.

cd /var/www/html
nano postgres_test.php

NOTE: If you have a macOS and have trouble finding your Apache server’s root directory, check in its directory called /Library/WebServer/Documents.

  • It’s important to confirm that PostgreSQL and the PHP library are configured correctly. To find out, copy and paste the code into the script when you edit it:

A pgconnect example

“;

// it should return: “Warning: Wrong parameter count” if installed correctly echo pg_connect();

// show information about PHP and the packages installed phpinfo();

>__NOTE:__ Unless you're using them for debugging, skip the code section representing `error_reporting` and `ini-set`. Those lines are for revealing web page errors.

* Open a tab in your browser and go to `http://localhost/postgres-test.php` after you saved the edited script with the code you copied. Inspect the web page. Reinstall PHP on your Apache server if the web page only shows the code of PHP.

![Screenshot of PHP returning information and pg_connect response in a browser](https://i.gyazo.com/9bf61aa0905bce449cc63697d2a7aa04.jpg)

### Addressing a fatal error response

You'll need to install or reinstall libraries of PHP along with its packages if you receive the `Fatal error: Uncaught Error: Call to undefined function pg_connec()` response. These are necessary for using the method pgconnect.

* Apply the following sudo command to add the PHP PostgreSQL library and any required associations:

```bash
sudo apt-get install php-pgsql
  • For Unbuntu and Linux servers that are Debian-based, use the commands below to install additional dependencies.
sudo apt install apache2 apache2-utils
sudo apt install postgresql libpq5 postgresql-9.5 postgresql-client-9.5 postgresql-client-common postgresql-contrib

If you need to make configuration file edits, look in /etc/php/ for the .ini for PHP. A number indicating the version edition will be shown in the file name at the end of it (for example, /etc/php/7.3 or something similar to that).

  • A nano-edited PHP ini file shows /etc/php/7.3 includes the file version in a response:

Screenshot of a UNIX terminal editing the php.ini file with nano

  • When you’re ready, restart the service:
sudo service apache2 restart
  • For macOS users, use this command:
sudo apachectl restart

Make a PHP class declaration

  • Construct a PHP object for the parameters of the PostgreSQL host.

  • Next, take the attributes and form a string concatenation.

// Declare a new class for the pg_connect() connect parameters
class connectionParams {}
$param = new connectionParams;

Make a declaration of PostgreSQL attributes

  • Each attribute must have a parameter that matches the name it represents. For instance, the parameter dbname matches $param->dbname set before it.

  • See these examples below:

// 'host' for the PostgreSQL server
$param->host = "localhost";

// default port for PostgreSQL is "5432"
$param->port = 5432;

// set the database name for the connection
$param->dbname = "python_test";

// set the username for PostgreSQL database
$param->user = "objectrocket";

// password for the PostgreSQL database
$param->password = "mypass";

NOTE: In PHP, the act of concatenation for attributes (such as ports) immediately turns integers into strings. Therefore, this happens naturally in PHP.

Prepare for the connect PostgreSQL database PHP pgconnect method by making a new declaration

  • Make a declaration of a fresh string for the parameters for the method pgconnect.
// declare a new string for the pgconnect method
$hostString = "";

Perform a key-value pair iteration

  • Make a PostgreSQL connection by first iterating over the parameter object’s key-value pairs. The foreach loop statement is helpful for quick iterations. Try this:
// use an iterator to concatenate a string to connect to PostgreSQL
foreach ($param as $key => $value) {

// concatenate the connect params with each iteration
$hostString = $hostString . $key . "=" . $value . " ";
}

IMPORTANT: Remember to only run the host string on a localhost environment because the string contains your password credentials.

// WARNING: For demonstration purposes only
// NEVER echo password credentials in PHP
echo "
\$hostString: "
. $hostString. "
"
;
  • Shown here is what the PHP string should look like when you’re finished with the iterations:
"host=localhost port=5432 dbname=python_test user=objectrocket password=mypass"

Have the method pgconnect return an object to connect to PostgreSQL

  • The host string needs to be passed to method call pgconnect. When you pass it, a connection object is returned like this:
// use the pg_connect() to create a connection
$conn = pg_connect($hostString);

// echo the connection response
echo "
\$conn: "
. $conn. "
"
;

NOTE: On the front end, you should see the $conn object that was echoed to be seen like this: $conn: Resource id #1 or something similar. It represents a PostgreSQL database connection resource.

Get the table’s data in the database when you pass the connection object

  • Pass to the pg_query() method call the PostgreSQL connection instance. The response will have the entire table’s data from the database. This happens when you use the command SELECT with the * asterisk symbol in the query.
// pass the connect instance to the pg_query() method
$response = pg_query($conn, "SELECT * FROM some_table");

NOTE: The name some_table in the string’s second parameter is a placeholder. Replace it with the real name of your table in your PostgreSQL database.

Echo styling attributes for the elements of the HTML table

  • The table HTML’s CSS styling attributes requires echoing like this:
// echo an opening tab for the

" . $row['str_col'] . "

" . $row['int_col'] . "

";

Stop the connect PostgreSQL database PHP pgconnect connection

It’s always good practice to close the connection when want to end you’ve completed the entire process. This is because it stops leaks in memory and frees up memory.

  • Pass the instance of the connection to the method pg_close().
// close the connection to free memory
pg_close($conn);

// end of the PHP script
echo "


fin

"
;
  • After you’re done making the script’s PHP changes, save it.

  • Perform a URL script tab refresh in the browser. You should see in the front end the HTML table with the data from PostgreSQL. It has been echoed successfully.

Screenshot of PHP echo to front end of pg_connect PostgreSQL connection returning table data

Conclusion

This tutorial explained how you can perform a connect PostgreSQL database PHP pgconnect. By doing so, you can enjoy a main benefit of using pgconnect, which is being able to show PostgreSQL data as an HTML table. Begin displaying HTML tables in your projects today.

Just the Code

Here’s the entire sample script for creating a connect PostgreSQL database PHP pgconnect.

A pgconnect example

“;

/* // it should return: “Warning: Wrong parameter count” if installed correctly echo pg_connect();

// show information about PHP and the packages installed phpinfo(); */

// Declare a new class for the pg_connect() connect parameters class connectionParams {} $param = new connectionParams;

// ‘host’ for the PostgreSQL server $param->host = “localhost”;

// default port for PostgreSQL is “5432” $param->port = 5432;

// set the database name for the connection $param->dbname = “python_test”;

// set the username for PostgreSQL database $param->user = “objectrocket”;

// password for the PostgreSQL database $param->password = “mypass”;

// declare a new string for the pg_connect() method $hostString = “”;

// use an iterator to concatenate a string to connect to PostgreSQL foreach ($param as $key => $value) {

// concatenate the connect params with each iteration $hostString = $hostString . $key . “=” . $value . ” “; }

// WARNING: For demonstration purposes only // NEVER echo password credentials in PHP echo ” $hostString: “. $hostString. ” “;

// use the pgconnect() to create a connection / $conn = pgconnect(“host=localhost port=5432 dbname=python_test user=objectrocket password=mypass”); / $conn = pg_connect($hostString);

// echo the connection response echo ” $conn: “. $conn. ” “;

// pass the connect instance to the pg_query() method $response = pg_query($conn, “SELECT * FROM some_table”);

// echo an opening tab for the

” . $row[‘str_col’] . “

” . $row[‘int_col’] . “

“;

// close the connection to free memory pg_close($conn);

// end of the PHP script echo “

fin

“;

 

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.