x386.org: Debian and Ubuntu Documentation

|  Home  |  About  |  Contact  |  License  |  Privacy Policy  |

Copyright (C) 2020 - 2024 Exforge exforge@x386.org

This document is free text: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or any later version.

This document is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see .


LAPP Stack On Debian and Ubuntu

0. Specs

Sources: https://stackoverflow.com/questions/49157928/how-to-fetch-data-from-postgresql-using-php

1. Install Packages

1.0. Update Repositories

sudo apt update

1.1. Install Apache

sudo apt install --yes apache2

1.2. Install Postgres

sudo apt install --yes postgresql

1.3. Install PHP, Postgres and Apache dependencies

sudo apt install --yes php libapache2-mod-php php-pgsql

1.4. Install other PHP dependencies

Depending on the PHP code, you may need some more PHP library packages. For example, Wordpress needs the following packages:

sudo apt install --yes php-curl php-gd php-mbstring php-xml php-xmlrpc \
     php-soap php-intl php-zip

1.5. Restart Apache

sudo systemctl restart apache2

2. Test LAPP Stack

We'll create a test database, a table in that database, add some rows to the table on Postgres. We will also create a test PHP file with the PHP code to retrieve the data from the database and display it as HTML.

2.1. DB Operations

Create a test Postgres user and give its password

sudo -u postgres createuser --pwprompt testuser

Create a test Database

sudo -u postgres createdb testdb

Connect to Postgres shell

sudo -u postgres psql testdb

Create a table, fill the table, give test user access permission to that database and the table.

Run on Postgres shell

CREATE TABLE Employees (Name char(15), Age int, Occupation char(15));
INSERT INTO Employees VALUES ('Joe Smith', '26', 'Ninja');
INSERT INTO Employees VALUES ('John Doe', '33', 'Sleeper');
INSERT INTO Employees VALUES ('Postgres Server', '14', 'RDBM');

2.2. Create Test PHP

sudo nano /var/www/html/test.php

Fill it as below, remember to change to your password

    $dbh = 'localhost';
    $dbn= 'testdb';
    $dbu = 'testuser';
    $dbp = 'password';
    $dbconn = pg_connect("host=$dbh dbname=$dbn user=$dbu password=$dbp")
        or die('Connection Error: ' . pg_last_error());
   $query = 'SELECT * FROM Employees';
   $result = pg_query($query) or die('Error message: ' . pg_last_error());
<!DOCTYPE html>
               while ($row = pg_fetch_row($result)) {
                <td><?php echo $row[0]; ?></td>
                <td><?php echo $row[1]; ?></td>
                <td><?php echo $row[2]; ?></td>
            <?php } ?>

2.3. Test it

Now, from your workstation's browser, load the page (replace srv with your server's IP: