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
.
1 | 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:
1 | php -v |
Be sure an Apache server
localhost
is active on your OS. To do this, open a browser tab127.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.
1 2 | 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();
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | >__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.
1 2 | 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:
- When you’re ready, restart the service:
1 | sudo service apache2 restart |
- For macOS users, use this command:
1 | 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.
1 2 3 | // 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | // '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.
1 2 | // 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:
1 2 3 4 5 6 | // 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.
1 2 3 4 5 | // 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:
1 | "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:
1 2 3 4 5 6 7 | // 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 commandSELECT
with the*
asterisk symbol in the query.
1 2 | // 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:
1 2 3 4 5 6 7 | // 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()
.
1 2 3 4 5 6 7 8 9 10 |
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.
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
“;
1 |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started