Nginx and php - a practical example.

dos2unix

dos2unix

Well-Known Member
Joined
May 3, 2019
Messages
3,511
Reaction score
3,256
Credits
31,430
Yes you can do this with apache/httpd as well. But for this tutorial I am using nginx.

For Redhat/Fedora/Rocky
Code: 
sudo dnf install -y nginx nginx-all-modules nginx-filesystem nginx-mod-fancyindex php php-cli php-fpm php-gd php-gmp php-pgsql php-pdo

For Debina/Ubuntu/Mint
Code: 
sudo apt update sudo apt install -y nginx nginx-extras php php-cli php-fpm php-gd php-gmp php-pgsql php-pdo

For Arch/EndeavorOS
Code: 
sudo pacman -Syu sudo pacman -S nginx php php-fpm php-gd php-gmp php-pgsql

Arch does not have a separate php-pdo package. Find your /etc/php/php.ini file and uncomment this line.
Code: 
extension=pdo

Next we will enable and start nginx
Code: 
sudo systemctl enable nginx
sudo systemctl start nginx

If you want to see your web pages on other computers on your network you'll need to open the firewall.

For Redhat/Fedora/Rocky
Code: 
sudo firewall-cmd --add-service=http --permanent
sudo firewall-cmd --add-service=https --permanent
sudo firewall-cmd --reload

For Debian/Ubuntu/Mint
Code: 
sudo ufw allow http
sudo ufw allow https
sudo ufw reload

OK, now you have an nginx web server running. By default nginx usually puts it's html pages in /usr/share/nginx/html.If your distro is different let me know, I didn't test them all. We will create a new text file here with your text editor.name it phpinfo.php It's important that it ends with the .php filename extension, or else it will not work. This file is short and easy, it should look like this.
Code: 
<?php
   phpinfo();
?>

Now open your web browser to http://10.0.0.67/phpinfo.php
Just change the IP address to the IP address of your web server. If all goes according to plan, you should see a web page hundreds of lines long. If all that works, congratulations, you have a web server running php.

... but that's not very exciting, at least not yet.

...to be continued.
 


For this next section you need a postgresql database. Ideally you have one just like this one.

dos2unix

Thread 'Introduction to postgresql'

What is postgresql? Is a database engine. What is a database? A way to store data in an indexed order. I will tell you before I begin here. I am not a DBA. (database administrator) this is not really my forte. I know enough SQL to be dangerous. I'm going to keep this pretty basic. Once you get familiar with databases and SQL (structured query language) you will want to do more advanced stuff like relational tables, foreign keys, left joins and other things. This tutorial will not cover those things. This is basic installation, basic use, and introduction to SQL language.

SQL...

If not, you should go through the exercises in that tutorial before moving on to the rest of this one.
Once that is completed, you need to create two more text files. The first one will be called dbconn.php
It will look like this. These go in your /usr/share/nginx/html directory.
Code: 
<?php
    $dbtype="pgsql";
    $dbname="contacts";
    $hostname="10.0.0.67";
    $username="postgres";
    $password="n0tMyR3@lPas$w0rd";
?>

Change the password to whatever your postgres user's passwd is. The second file will be named sw_version.php, and it will look like this.
Code: 
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Current Software Versions</title>
</head>
<body>
<table border="1" cellspacing="0" align="center" width="45%">
<tr><td align="center" bgcolor="#ffcc00">Postgresql Version</td></tr>
<tr><td align="center">
<?php

try {
   include ("dbconn.php");
   $dbh = new PDO("$dbtype:host=$hostname;dbname=$dbname", $username, $password);

    /*** The SQL SELECT statement ***/
    $sql = "SELECT version()";
    foreach ($dbh->query($sql) as $row)
        {
        print $row['version'];
        }

    /*** close the database connection ***/
    $dbh = null;
}
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>
</td></tr>
</table>
</br></br>
<table border="1" cellspacing="0" align="center" width="40%">
<tr><td align="center" bgcolor="#ffcc00">PHP Version</td></tr>
<tr><td align="center">
<?php
echo 'Current PHP version: ' . phpversion();
?>
</td></tr>
</table>
</br></br>
<table border="1" cellspacing="0" align="center" width="35%">
<tr><td align="center" bgcolor="#ffcc00">Web Server Version</td></tr>
<tr><td align="center">
<?php
echo $_SERVER['SERVER_SOFTWARE'];
?>
</td></tr>
</table>
</body>
</html>

Hopefully you will see a webpage with the version of php you are using and the version of the database engine you are connecting too. If it doesn't work, you can try disabling SElinux or appArmor, and try again. (I will show you how to fix this, but for now we can just disable them). Assuming all that works, congratulations, your web/php server now has the ability to connect to your database. Notice this file looks for the dbconn.php file, and will not work without it.

... to be continued.
 
Now we will create yet another php web page in your /usr/share/nginx/html directory. We will call it contacts.php
It will look like this.
Code: 
<?php
// Include the database connection details
include("dbconn.php");

// Create a connection
$dbh = new PDO("$dbtype:host=$hostname;dbname=$dbname", $username, $password);

// Check connection
if (!$dbh) {
    die("Connection failed: " . $dbh->errorInfo());
}

// Query to select all data from phonebook
$query = "SELECT * FROM phonebook";
$stmt = $dbh->prepare($query);
$stmt->execute();

// Fetch all rows
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Phonebook</title>
    <style>
        body {
            font-family: Arial, sans-serif;
        }
        table {
            width: 100%;
            border-collapse: collapse;
        }
        table, th, td {
           1px solid black;
        }
        th, td {
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #add8e6; /* Light blue */
        }
    </style>
</head>
<body>
    <h1>Phonebook</h1>
    <table>
        <tr>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Street Address</th>
            <th>City</th>
            <th>State</th>
            <th>Zip</th>
            <th>Phone Number</th>
            <th>Phone Type</th>
        </tr>
        <?php foreach ($results as $row): ?>
        <tr>
            <td><?php echo htmlspecialchars($row['first_name']); ?></td>
            <td><?php echo htmlspecialchars($row['last_name']); ?></td>
            <td><?php echo htmlspecialchars($row['street_addr']); ?></td>
            <td><?php echo htmlspecialchars($row['city']); ?></td>
            <td><?php echo htmlspecialchars($row['state']); ?></td>
            <td><?php echo htmlspecialchars($row['zip']); ?></td>
            <td><?php echo htmlspecialchars($row['phone_numb']); ?></td>
            <td><?php echo htmlspecialchars($row['phone_type']); ?></td>
        </tr>
        <?php endforeach; ?>
    </table>
</body>
</html>

Now open your web browser to http://10.0.0.67/contacts.php

Change the IP address to whatever the IP address of your web server is. You should see something similar to this.

1741584756563.png



Congratulations - your first database connected php web page!! Think of the possibilities. If anyone is really interested I might continue this series with other examples. I might even show how we can use web pages to do custom queries to our database.

Happy Linux'ing!
 
Now, you might be thinking to yourself, well Ray that's cool, but I already have my own database.
I don't want to use your funky database.

If that case you only have to change four things.

1. In the dbconn.php file. Change the name of the database to match whatever your database name is.
Code: 
    $dbname="servers";

2. Using the contacts.php web page as a template, change the following
Code: 
$query = "SELECT * FROM servers";
Just change the name of the table here, to whatever the name of the table in your database is, that you want to query.

3. Match the table headers in the html to match your table headers in your database.
For example if the top your select statement looks like this...
Code: 
 select * from assets;
 hostname    | os |      mac_addr       |    ip_addr     | location |   purpose    |
------------+-----------+-----------------------------------+------------
 rayswebserver    | fedora42       | ab:cd:ef:01:02   | 10.0.0.67 |  basement closet    | php web server

You would change this section of the html to match.
Code: 
   <table>
        <tr>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Street Address</th>
            <th>City</th>
            <th>State</th>
            <th>Zip</th>
            <th>Phone Number</th>
            <th>Phone Type</th>
        </tr>

Get rid of first Name, Last Name, etc... and put in Hostname, OS, IP Address, etc...

4. Now match the columns of the table to your database. Change this section of the html...
Code: 
        <?php foreach ($results as $row): ?>
        <tr>
            <td><?php echo htmlspecialchars($row['first_name']); ?></td>
            <td><?php echo htmlspecialchars($row['last_name']); ?></td>
            <td><?php echo htmlspecialchars($row['street_addr']); ?></td>
            <td><?php echo htmlspecialchars($row['city']); ?></td>
            <td><?php echo htmlspecialchars($row['state']); ?></td>
            <td><?php echo htmlspecialchars($row['zip']); ?></td>
            <td><?php echo htmlspecialchars($row['phone_numb']); ?></td>
            <td><?php echo htmlspecialchars($row['phone_type']); ?></td>
        </tr>

Again, change the first_name to hostname, and last_name to os, and street_addr to mac_addr, etc...

That's really pretty much it. Just make sure the table cells match your database fields.

But what if I don't like postgresql... I want to do this with mariadb. In the dbconn.php file. Just change the dbtype in the dbconn.php file.

$dbtype = "mysql";

or

$dbtype = "mariadb";

Back in the first post of this thread, we installed php-pgsql. For mysql you would install php-mysql.

That's the beauty of using pdo. It hides the database behind an abstraction layer, so it doesn't care what database engine you are using.
 
Last edited:
Now, we will get a little fancier. Let's create another database, and auto-populate the data in it.

As user postgres...
Code: 
psql
CREATE DATABASE charts;
\c charts

Code: 
CREATE TABLE public.computerstats (
    id integer NOT NULL,
    "timestamp" timestamp without time zone,
    cpu double precision,
    mem double precision,
    temp double precision,
    telemetry integer,
    logcount double precision,
    interface character varying(10),
    ipaddr inet,
    macaddr macaddr,
    lastboot character varying
);

This next part, you can do on any local computer(s). It doesn't have to be your database computer.
Oh yeah, you'll need to install lm_sensors on whatever computer we are doing this from.

dos2unix

Thread 'Is it hot in here?'

Monitoring Hardware Temperatures in Linux
Monitoring the temperature of your hardware components is crucial for maintaining system health and performance. In this article, we'll cover how to use lm_sensors, hddtemp, nvidia-smi, and inxi to get temperature readings for various hardware devices in Linux.

1. lm_sensors​

lm_sensors is a widely used tool for monitoring CPU, motherboard, and other system sensors.

Installing lm_sensors​

On Debian-based systems:

Code: 
 sudo apt install lm-sensors

On RPM-based systems:

Code: 
 sudo dnf install lm_sensors

Detecting Hardware...​

  • Like

Now we need to create a bash shell script on that same computer that we installed lm_sensors on.
You can call this anything
Code: 
#!/bin/bash

# State file location
STATEFILE="/var/log/system_monitor_state.log"

# PostgreSQL database credentials
DB_NAME="charts"
DB_USER="postgres"
DB_PASSWORD="s3cr3t123"
DB_HOST="10.0.0.67"
DB_PORT="5432"

# Initialize logcount
LOGCOUNT=0
INCREMENT=270.83

# Function to read state from file
read_state() {
    if [[ -f $STATEFILE ]]; then
        source $STATEFILE
    fi
}

# Function to write state to file
write_state() {
    echo "LOGCOUNT=$LOGCOUNT" > $STATEFILE
}

# Function to log system usage and check thresholds
log_usage() {
    CPU_USAGE=$(top -bn1 | grep "Cpu(s)" | sed "s/.*, *\([0-9.]*\)%* id.*/\1/" | awk '{print 100 - $1}')
    MEM_USAGE=$(free -m | awk 'NR==2{printf "%.2f", $3*100/$2 }')
    CPU_TEMP_C=$(sensors | grep 'Package id 0:' | awk '{print $4}' | tr -d '+°C')
    CPU_TEMP_F=$(echo "scale=2; $CPU_TEMP_C * 9 / 5 + 32" | bc)
    TIMESTAMP=$(date +%s)
    TELEMETRY=$(shuf -i 30-120 -n 1)

    # Get IP address and MAC address
    IPADDR=$(ip addr | grep eno2 | grep inet | awk '{print $2}' | awk -F'/' '{print $1}')
    MACADDR=$(ip link | grep eno2 -A1 | grep link | awk '{print $2}')

    # Update logcount
    LOGCOUNT=$(echo "$LOGCOUNT + $INCREMENT" | bc)
    if (( $(echo "$LOGCOUNT >= 65000" | bc -l) )); then
        LOGCOUNT=0
    fi

    # Check interface status
    INTERFACE="eno2"
    STATUS=$(ip link show $INTERFACE | grep -oP '(?<=state )\w+')

    # Get the most recent last boot time
    CURRENT_LASTBOOT=$(last | grep reboot | head -n1 | awk '{print $5" "$6" "$7" "$8}')

    # Query the newest lastboot from the database
    LASTBOOT1=$(PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -U $DB_USER -d $DB_NAME -p $DB_PORT -t -c \
    "SELECT lastboot FROM computerstats WHERE lastboot IS NOT NULL ORDER BY timestamp DESC LIMIT 1;")

    # Trim whitespace from LASTBOOT1
    LASTBOOT1=$(echo $LASTBOOT1 | xargs)

    # Insert or update the record with conditional lastboot
    if [ -z "$LASTBOOT1" ]; then
        LASTBOOT_CONDITION="'$CURRENT_LASTBOOT'"
    else
        if [ "$LASTBOOT1" == "$CURRENT_LASTBOOT" ]; then
            LASTBOOT_CONDITION="NULL"
        else
            LASTBOOT_CONDITION="'$CURRENT_LASTBOOT'"
        fi
    fi

    # Debugging: Print the full SQL query
    SQL_QUERY="INSERT INTO computerstats (timestamp, cpu, mem, temp, telemetry, logcount, interface, ipaddr, macaddr, lastboot) VALUES (to_timestamp($TIMESTAMP), $CPU_USAGE, $MEM_USAGE, $CPU_TEMP_F, $TELEMETRY, $LOGCOUNT, '$STATUS', '$IPADDR'::inet, '$MACADDR'::macaddr, $LASTBOOT_CONDITION);"

    # Execute the SQL query
    PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -U $DB_USER -d $DB_NAME -p $DB_PORT -c "$SQL_QUERY"

    # Verify the insertion by querying the database again
    LASTBOOT2=$(PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -U $DB_USER -d $DB_NAME -p $DB_PORT -t -c \
    "SELECT lastboot FROM computerstats WHERE lastboot IS NOT NULL ORDER BY timestamp DESC LIMIT 1;")
    LASTBOOT2=$(echo $LASTBOOT2 | xargs)
}

# Read state from file
read_state

# Infinite loop to keep the script running
while true
do
    log_usage
    write_state
    sleep 600 # Log every 10 minutes
done

To speed things along here, change that sleep time at the end to 10 seconds instead of 600 seconds. You'll need to change the password to whatever your postgres user password is, and you'll need to change the IP address to the IP address of your database server. There are two lines that mention interface eno2. You will need to change those lines to match your interface name. Make this executable, and let it run for.... 5 to 10 minutes should do it.

You should see several lines like this...
Code: 
INSERT 0 1

You can just use cntrl-c to exit out of this after 5 or 10 minutes. Next go back to your database computer and log into the database as user postgres.
Code: 
psql charts
select * from computerstats;

 id  |      timestamp      | cpu | mem  | temp  | telemetry | logcount | interface |  ipaddr   |      macaddr      |    lastboot
-----+---------------------+-----+------+-------+-----------+----------+-----------+-----------+-------------------+-----------------
  98 | 2025-03-07 18:27:56 | 2.2 |  7.4 |  93.2 |       119 |  2166.64 | UP        | 10.0.0.67 | 98:e7:43:7a:c6:1d |
 100 | 2025-03-07 18:29:57 | 2.2 |  7.4 |  82.4 |        71 |   2708.3 | UP        | 10.0.0.67 | 98:e7:43:7a:c6:1d |
 102 | 2025-03-07 18:31:58 |   0 | 7.35 |  91.4 |       107 |  3249.96 | UP        | 10.0.0.67 | 98:e7:43:7a:c6:1d |
 104 | 2025-03-07 18:33:59 | 2.3 |  7.4 |  96.8 |        41 |  3791.62 | UP        | 10.0.0.67 | 98:e7:43:7a:c6:1d |
 106 | 2025-03-07 18:36:00 | 1.1 | 7.35 |  84.2 |        62 |  4333.28 | UP        | 10.0.0.67 | 98:e7:43:7a:c6:1d |
....

You should see dozens of lines like this. This will be more impressive if you have around 40 or 50 rows of data here.

.... to be continued.
 
Now on your nginx/php web server. In your /usr/share/nginx/html directory we will create two more php files.

One called dbconn2.php that will look like this.
Code: 
<?php
    $dbtype="pgsql";
    $dbname="charts";
    $hostname="10.0.0.67";
    $username="postgres";
    $password="abcd1234";
?>

Make sure you don't overwrite the existing dbconn.php file you may already have here.
Now let's make another php file called chart_data.php , it will look like this...
Code: 
<?php
include("dbconn2.php");
$dbh = new PDO("$dbtype:host=$hostname;dbname=$dbname", $username, $password);

// Fetch data from the database
$data = [];
$sql = "SELECT * FROM computerstats ORDER by timestamp ASC";
foreach ($dbh->query($sql) as $row) {
    $data[] = $row;
}

$dbh = null;
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Database Graphs</title>
    <style>
        body {
            font-family: 'Arial', sans-serif;
            margin: 0;
            padding: 20px;
            background-color: #f4f4f4;
        }
        h2 {
            color: #333;
        }
        .chart-container {
            width: 100%;
            margin-top: 20px;
        }
    </style>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
    <h2>Database Graphs</h2>

    <div class="chart-container">
        <h3>CPU Load</h3>
        <canvas id="cpuChart"></canvas>
    </div>
    <div class="chart-container">
        <h3>Memory Usage</h3>
        <canvas id="memChart"></canvas>
    </div>
    <div class="chart-container">
        <h3>CPU Temperature</h3>
        <canvas id="tempChart"></canvas>
    </div>
    <div class="chart-container">
        <h3>Telemetry</h3>
        <canvas id="telemetryChart"></canvas>
    </div>
    <div class="chart-container">
        <h3>Log Count</h3>
        <canvas id="logcountChart"></canvas>
    </div>

    <script>
        var data = <?php echo json_encode($data); ?>;
        var timestamps = data.map(function(row) { return row.timestamp; });
        var cpuData = data.map(function(row) { return row.cpu; });
        var memData = data.map(function(row) { return row.mem; });
        var tempData = data.map(function(row) { return row.temp; });
        var telemetryData = data.map(function(row) { return row.telemetry; });
        var logcountData = data.map(function(row) { return row.logcount; });

        var ctxCpu = document.getElementById('cpuChart').getContext('2d');
        var cpuChart = new Chart(ctxCpu, {
            type: 'bar',
            data: {
                labels: timestamps,
                datasets: [{
                    label: 'CPU Load',
                    data: cpuData,
                    backgroundColor: 'rgba(75, 192, 192, 0.2)',
                    borderColor: 'rgba(75, 192, 192, 1)',
                    borderWidth: 1
                }]
            },
            options: {
                scales: {
                    x: { title: { display: true, text: 'Timestamp' } },
                    y: { title: { display: true, text: 'CPU Load' } }
                }
            }
        });

        var ctxMem = document.getElementById('memChart').getContext('2d');
        var memChart = new Chart(ctxMem, {
            type: 'bar',
            data: {
                labels: timestamps,
                datasets: [{
                    label: 'Memory Usage',
                    data: memData,
                    backgroundColor: 'rgba(153, 102, 255, 0.2)',
                    borderColor: 'rgba(153, 102, 255, 1)',
                    borderWidth: 1
                }]
            },
            options: {
                scales: {
                    x: { title: { display: true, text: 'Timestamp' } },
                    y: { title: { display: true, text: 'Memory Usage' } }
                }
            }
        });

        var ctxTemp = document.getElementById('tempChart').getContext('2d');
        var tempChart = new Chart(ctxTemp, {
            type: 'bar',
            data: {
                labels: timestamps,
                datasets: [{
                    label: 'CPU Temperature',
                    data: tempData,
                    backgroundColor: 'rgba(255, 159, 64, 0.2)',
                    borderColor: 'rgba(255, 159, 64, 1)',
                    borderWidth: 1
                }]
            },
            options: {
                scales: {
                    x: { title: { display: true, text: 'Timestamp' } },
                    y: { title: { display: true, text: 'CPU Temperature' } }
                }
            }
        });

        var ctxTelemetry = document.getElementById('telemetryChart').getContext('2d');
        var telemetryChart = new Chart(ctxTelemetry, {
            type: 'line',
            data: {
                labels: timestamps,
                datasets: [{
                    label: 'Telemetry',
                    data: telemetryData,
                    backgroundColor: 'rgba(54, 162, 235, 0.2)',
                    borderColor: 'rgba(54, 162, 235, 1)',
                    borderWidth: 1,
                    fill: false
                }]
            },
            options: {
                scales: {
                    x: { title: { display: true, text: 'Timestamp' } },
                    y: { title: { display: true, text: 'Telemetry' } }
                }
            }
        });

        var ctxLogcount = document.getElementById('logcountChart').getContext('2d');
        var logcountChart = new Chart(ctxLogcount, {
            type: 'line',
            data: {
                labels: timestamps,
                datasets: [{
                    label: 'Log Count',
                    data: logcountData,
                    backgroundColor: 'rgba(255, 99, 132, 0.2)',
                    borderColor: 'rgba(255, 99, 132, 1)',
                    borderWidth: 1,
                    fill: false
                }]
            },
            options: {
                scales: {
                    x: { title: { display: true, text: 'Timestamp' } },
                    y: { title: { display: true, text: 'Log Count' } }
                }
            }
        });
    </script>
</body>
</html>

Notice this uses the dbconn2.php file, and it won't work without it. Now browse to http://10.0.0.67/chart_data.php
You should see something like this.

1741609442628.png


1741609478964.png


1741609513349.png


... and a few other charts as well. You can do a lot here.

1741609614502.png


These charts require the php-gd module. There is some javascript libraries being used as well.
 
Last edited:
You must log in or register to reply here.

Similar threads

dos2unix
Installing and running nginx
Replies
2
Views
505
dos2unix
dos2unix
deminart
nginx error
Replies
4
Views
5K
deminart
deminart
dos2unix
Setting up syslog-ng
Replies
2
Views
1K
dos2unix
dos2unix
Jarret B
LFCS – High Availability (HA) Cluster
Replies
0
Views
2K
Jarret B
Jarret B
Rob
(Example) Help me update my php
Replies
5
Views
1K
TuxBot
TuxBot


Members online

Total: 989 (members: 7, guests: 982)

Latest posts

Top