Creating a postgresql database connection with PHP and Apache on FreeBSD

For those who like web programming, especially PHP programming, it is no secret that the most common combination of language and DBMS is PHP and MySQL, but sometimes it is necessary to interact with other databases, for example with PostgreSQL or MariaDB. Because PHP supports work not only with MySQL and PostgreSQL, but also with many other DBMS based on the SQL language.

Once we learn how to install and configure PostgreSQL databases for various scenarios, having a database and filling it with data will be useless until we can take it and use it in some way. Currently, using mobile-friendly web applications is the most commonly used and popular method.

In this tutorial we will explain how to connect to a PostgreSQL database server using PHP with the help of the Apache24 Web Server as the Frontend. In this case, PHP functions to connect the PostgreSQL database, then the data stored in PostgreSQL will be displayed via the Chrome, Yandex, Firefox web browser via Apache24.


List of contents:
System Specifications
PHP Dependencies
PHP and PostgreSQL configuration
PHP and PostgreSQL connection with PDO


1. System Specifications
  • OS: FreeBSD 13.2
  • IP Address: 192.168.5.2
  • Apache24
  • phpPgAdmin 7.14.4-mod
  • PHP82
  • mod dan extension PHP
  • PHP-FPM
  • PostgreSQL 15.3

2. PHP Dependencies

So that PostgreSQL can be connected via PHP, several PHP modules and extensions are needed so that the PostgreSQL database can run perfectly. This dependency is a PHP library file that will be used by PostgreSQL to communicate with PHP. In this article we will not discuss PHP installation, you can read how to install PHP in the previous article.



The following are the PHP modules and extensions that must be installed on the FreeBSD system so that the PostgreSQL database can connect to PHP.

root@ns1:~ # cd /usr/ports/databases/php82-pgsql
root@ns1:/usr/ports/databases/php82-pgsql #
make install clean
root@ns1:/usr/ports/databases/php82-pgsql #
cd /usr/ports/lang/php82-extensions
root@ns1:/usr/ports/lang/php82-extensions #
make install clean
root@ns1:/usr/ports/lang/php82-extensions #
cd /usr/ports/www/mod_php82
root@ns1:/usr/ports/www/mod_php82 #
make install clean
root@ns1:/usr/ports/www/mod_php82 #
cd /usr/ports/www/php82-session
root@ns1:/usr/ports/www/php82-session #
make install clean
root@ns1:/usr/ports/www/php82-session #
cd /usr/ports/textproc/php82-simplexml
root@ns1:/usr/ports/textproc/php82-simplexml #
make install clean
root@ns1:/usr/ports/textproc/php82-simplexml #
cd /usr/ports/databases/php82-pdo
root@ns1:/usr/ports/databases/php82-pdo #
make install clean
root@ns1:/usr/ports/databases/php82-pdo #
cd /usr/ports/databases/php82-mysqli
root@ns1:/usr/ports/databases/php82-mysqli #
make install clean
root@ns1:/usr/ports/databases/php82-mysqli #
cd /usr/ports/databases/pear-DB
root@ns1:/usr/ports/databases/pear-DB #
make install clean
root@ns1:/usr/ports/databases/pear-DB #
cd /usr/ports/devel/pear
root@ns1:/usr/ports/devel/pear #
make install clean
root@ns1:/usr/ports/devel/pear #
cd /usr/ports/databases/php82-pdo_mysql
root@ns1:/usr/ports/databases/php82-pdo_mysql #
make install clean

After that, we continue by installing pear.

root@ns1:~ # pear install DB
WARNING: "pear/DB" is deprecated in favor of "pear/MDB2"
downloading DB-1.11.0.tgz ...
Starting to download DB-1.11.0.tgz (132,549 bytes)
.............................done: 132,549 bytes
install ok: channel://pear.php.net/DB-1.11.0

root@ns1:~ #
pear install MDB2
downloading MDB2-2.4.1.tgz ...
Starting to download MDB2-2.4.1.tgz (121,557 bytes)
..........................done: 121,557 bytes
install ok: channel://pear.php.net/MDB2-2.4.1
MDB2: Optional feature fbsql available (Frontbase SQL driver for MDB2)
MDB2: Optional feature ibase available (Interbase/Firebird driver for MDB2)
MDB2: Optional feature mysql available (MySQL driver for MDB2)
MDB2: Optional feature mysqli available (MySQLi driver for MDB2)
MDB2: Optional feature mssql available (MS SQL Server driver for MDB2)
MDB2: Optional feature oci8 available (Oracle driver for MDB2)
MDB2: Optional feature pgsql available (PostgreSQL driver for MDB2)
MDB2: Optional feature querysim available (Querysim driver for MDB2)
MDB2: Optional feature sqlite available (SQLite2 driver for MDB2)
MDB2: To install optional features use "pear install pear/MDB2#featurename"
If all the dependencies above have been installed, we can test by opening the Google Chrome web browser, typing 192.168.5.2/info.php, making sure the PHP info and specifications appear. This aims to ensure whether PHP and the Apache24 web browser are connected.



3. PHP and PostgreSQL configuration

PHP supports PostgreSQL database, the pg_connect function is used to connect to the PostgreSQL database server. Once the connection is established, SQL commands can be executed with the pg_query function. pg_connect has two main functions to operate with a PostgreSQL database server.

a. Opening a PostgreSQL connection
pg_connect(“host={namahost} port={portPostgreSQL} dbname={naamdatabasePostgreSQL} user={namauserPostgreSQL} password={passwordPostgreSQL}"); 

b. Closes the PostgreSQL connection
pg_close(connection_name);

The following are the basic PHP functions for working with PostgreSQL.
  • pg_connect: open a connection to the database, returns the connection pointer.
  • pg_query: executes a query to the database, returning the query results.
  • pg_fetch_assoc: converts query results into an associative array.
  • pg_close :  closes the connection to the database.
After we know the pg_connect function, we will try to create a connection to the PostgreSQL database. We create a con.php file which is placed in the /usr/local/www/apache24/data folder. In the con.php file, enter the script below. We suggest you also read the previous article.

root@ns1:~ # ee /usr/local/www/apache24/data/con.php
<?php
$dbconn = pg_connect("host=192.168.5.2 port=5432 dbname=postgres user=postgres password=router");
//connect to a database named "postgres" on the host "host" with a username and password
if (!$dbconn){
echo "<center><h1>Doesn't work =(</h1></center>";
}else
echo "<center><h1>OK, Koneksi adalah Good connection</h1></center>";
pg_close($dbconn);
?>
To carry out the test, we open the Yandex or Chrome web browser, type "192.168.5.2/con.php" in the browser's address bar menu.

Another example we take from the article "Using phpPgAdmin on FreeBSD with the Apache24 Web Server".

root@ns1:~ # ee /usr/local/www/apache24/data/gunungrinjani.php
<?php
$dbconn = pg_connect("host=192.168.5.2 port=5432 dbname=puncakanjani user=gunungrinjani password=sembalun");
//connect to a database named "postgres" on the host "host" with a username and password
if (!$dbconn){
echo "<center><h1>Doesn't work =(</h1></center>";
}else
echo "<center><h1>OK, Koneksi adalah Good connection</h1></center>";
pg_close($dbconn);
?>
You open the Google Chrome web browser again, and see the results.

We still take the example from the article "Using phpPgAdmin on FreeBSD with the Apache24 Web Server", now we create a file called "/usr/local/www/apache24/data/person.php", then enter the script below in that file.

root@ns1:~ # ee /usr/local/www/apache24/data/person.php
<?php
$conn = pg_connect("host=192.168.5.2 port=5432 dbname=postgres user=postgres password=router");
if (!$conn) {
echo "An error occurred.\n";
exit;
}
$result = pg_query($conn, "SELECT * FROM person");
if (!$result) {
echo "An error occurred.\n";
exit;
}
while ($row = pg_fetch_row($result)) {
echo "value1: $row[0] value2: $row[1] value3: $row[2] value4: $row[3]";
echo "<br />\n";
}
?>
We see the results by opening Yandex Browser or Google Chrome, in the address bar menu type "http://192.168.5.2/person.php"


4. PHP and PostgreSQL connection with PDO

In part 3, we learned how to connect a PostgreSQL database with PHP via pg_connect. There is still a PostgreSQL database connection technique other than pg_connect, namely using PHP PDO. The main requirement for connecting with PDO is that you have installed all the dependencies mentioned above perfectly, especially the php82-pdo dependencies.

Below we will provide an example of how to connect to a PostgreSQL database using PHP PDO. The first step is to create the file /usr/local/www/apache24/data/config.php and enter the script below. As a reference we still use articles "Using phpPgAdmin on FreeBSD with Apache24 Web Server"

root@ns1:~ # ee /usr/local/www/apache24/data/config.php
<?php
$host= '192.168.5.2';
$db = 'postgres';
$user = 'postgres';
$password = 'router'; // change to your password
?>
After that, we create another file /usr/local/www/apache24/data/pdocon.php, and enter the script below.

root@ns1:~ # ee /usr/local/www/apache24/data/pdoperson.php
<?php
require_once 'config.php';

try {
$dsn = "pgsql:host=$host;port=5432;dbname=$db;";
// make a database connection
$pdo = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

if ($pdo) {
echo "Selamat! Koneksi Ke Database $db Sukses.";
}
} catch (PDOException $e) {
die($e->getMessage());
} finally {
if ($pdo) {
$pdo = null;
}
}
?>

To see the results, type "http://192.168.5.2/pdoperson.php" in the web browser Yandex or Google Chrome.

This tutorial covers how to connect PHP to PostgreSQL with pg_connect and the PDO driver. In the discussion above, we have learned how the PostgreSQL database can be connected via PHP, so that with the help of Apache24 the data in PostgreSQL can be displayed in a web browser.
Iwan Setiawan

I Like Adventure: Mahameru Mount, Rinjani Mount I Like Writer FreeBSD

Post a Comment

Previous Post Next Post