Real-Time Water Billing with PHP, Redis Pub/Sub & MySQL

Redis® Pub/Sub is an architectural model that enables publishers to transmit data to subscribers through designated channels. This technology is especially useful for constructing real-time apps that depend on Inter-Process Communications (IPCs), such as financial platforms, live chat systems, and more. In such scenarios, low latency is essential—users anticipate near-instantaneous delivery of messages when connected to a channel. When building applications with traditional SQL databases, performance bottlenecks can arise due to scalability limitations. In contrast, Redis® operates using your server's RAM and is capable of performing numerous read/write operations per second.

Another major benefit of utilizing Redis® for the Pub/Sub model is the decoupling of publishers and subscribers. This setup allows data to be sent to a channel without explicitly specifying the recipients. Similarly, subscribers can listen to channels and await messages, independent of any knowledge about the sources of those messages.

In such loosely integrated systems, frontend and backend developers can independently build and test their components. This approach shortens development time since different teams work in parallel, increases developer autonomy, and simplifies hiring due to the reduced complexity of the system architecture.

This tutorial walks you through creating a modular water billing system using PHP and Redis®. In this setup, a frontend component (acting as the publisher) collects water usage data from households and transmits it via a billing channel. On the backend, a subscriber script processes this information and stores it permanently in a MySQL database.

Prerequisites

Ensure the following prerequisites are met to follow along with this Redis® Pub/Sub guide:

• Ubuntu 20.04 server
• A user with sudo privileges
• An installed LAMP stack
• Redis® Server

1. Install the PHP Redis® Extension

To allow your PHP scripts to communicate with Redis®, install the php-redis extension. Begin by accessing your server via SSH and updating the package index:

$ sudo apt update

Then install the extension:

$ sudo apt install -y php-redis

Restart Apache to apply the changes:

$ sudo systemctl restart apache2

With the environment ready for PHP-Redis® communication, proceed to database creation.

2. Set Up a Sample Database and User

Redis® stores information in memory and while it can save data to disk, its core purpose is high-speed, transient data handling. In this billing application, Redis® will quickly collect consumption data from users which you'll later store permanently in MySQL.

Log into MySQL as root:

$ sudo mysql -u root -p

Enter your MySQL root password. Run the following to create the billing_db database and a user. Replace EXAMPLE_PASSWORD with a secure password:

mysql> CREATE DATABASE billing_db;
       CREATE USER 'billing_db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
       GRANT ALL PRIVILEGES ON billing_db.* TO 'billing_db_user'@'localhost';           
       FLUSH PRIVILEGES;

You should see output like:

Query OK, 0 rows affected (0.00 sec)

Switch to the new database:

mysql> USE billing_db;

The response should confirm:

Database changed

Create the customers table to store user information:

mysql> CREATE TABLE customers (
           customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           first_name VARCHAR(50),
           last_name VARCHAR(50)           
       ) ENGINE = InnoDB;

Expected result:

Query OK, 0 rows affected (0.01 sec)

Add sample records:

mysql> INSERT INTO customers (first_name, last_name) values ('JOHN', 'DOE');
       INSERT INTO customers (first_name, last_name) values ('MARY', 'SMITH');
       INSERT INTO customers (first_name, last_name) values ('STEVE', 'JONES');

Each successful insert should show:

Query OK, 1 row affected (0.01 sec)

Now define the billings table to store monthly usage. The ref_id uniquely identifies each bill. billing_period denotes the billing month’s end. units_consumed tracks water usage in cubic meters. cost_per_unit holds the billing rate in USD.

mysql> CREATE TABLE billings (
           ref_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           customer_id BIGINT,
           billing_period VARCHAR(50),
           units_consumed DOUBLE,
           cost_per_unit  DOUBLE          
       ) ENGINE = InnoDB;

You should see:

Query OK, 0 rows affected (0.02 sec)

Exit MySQL CLI:

mysql> QUIT;

Output:

Bye

You’ve now initialized the database, structured essential tables, and populated some demo entries. The next stage involves coding the frontend component that captures water consumption data from clerks to initiate the billing cycle.

3. Develop a PHP-Based Frontend Script

In this billing platform, household water consumption is tracked in cubic meters. Unless smart metering solutions are in place, data clerks must manually record readings and forward them to the company’s backend systems.

Today, such data can be transmitted through mobile apps communicating with APIs hosted on cloud servers. In this section, you will craft a frontend script using PHP to handle this data. Start by creating a new file called frontend.php in the web server’s root directory using the following:

$ sudo nano /var/www/html/frontend.php

Paste the following script into that file:

<?php 

    try {   
             
        $redis = new Redis(); 
        $redis->connect('127.0.0.1', 6379);

        $channel      = 'billings';
        $billing_data = file_get_contents('php://input');          

        $redis->publish($channel, $billing_data); 

        echo "Data successfully sent to the billings channel.\n";         

        } catch (Exception $e) {
            echo $e->getMessage();
        }

Save and exit the editor. Here's how the code functions:

• It connects to Redis at 127.0.0.1:6379 using:

  $redis = new Redis(); $redis->connect('127.0.0.1', 6379);

• It captures incoming JSON data with:

  $billing_data = file_get_contents('php://input');

• The data is sent to the billings channel using:

  $redis->publish($channel, $billing_data);

• If any issues arise, the catch block handles them gracefully.

You’ve now set up a script that collects billing data and pushes it to a Redis® channel. The next task is building the backend script.

4. Create the PHP Backend Script

To process incoming billing records and persist them to MySQL, you will subscribe to the billings channel using a script called backend.php. Start by creating the file:

$ sudo nano /var/www/html/backend.php

Insert the script below:

<?php 

    try {   
             
        $redis = new Redis(); 
        $redis->connect('127.0.0.1', 6379);
        $redis->setOption(Redis:: OPT_READ_TIMEOUT, -1);       

        $redis->subscribe(['billings'], function($instance, $channelName, $message) { 
         
            $billing_data = json_decode($message, true); 

            $db_name     = 'billing_db';
            $db_user     = 'billing_db_user';
            $db_password = 'EXAMPLE_PASSWORD';
            $db_host     = 'localhost';

            $pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   

            $sql = 'insert into billings(
                        customer_id, 
                        billing_period, 
                        units_consumed,
                        cost_per_unit
                    ) 
                    values(
                        :customer_id, 
                        :billing_period, 
                        :units_consumed,
                        :cost_per_unit
                    )'; 

            $data = [];

            $data = [
                    'customer_id'    => $billing_data['customer_id'],
                    'billing_period' => $billing_data['billing_period'],
                    'units_consumed' => $billing_data['units_consumed'],
                    'cost_per_unit'  => 2.5                                 
                    ];

            $stmt = $pdo->prepare($sql);
            $stmt->execute($data); 

            echo "The Redis® data was sent to the MySQL database successfully.\n" ;               
                    
        });         

        } catch (Exception $e) {
            echo $e->getMessage();
        }

This backend script:

• Establishes a connection to Redis®.
• Subscribes to the billings channel.
• Disables timeout to keep the listener alive indefinitely.
• Parses incoming JSON messages.
• Prepares and executes a SQL INSERT to store the message data into MySQL.

5. Run a Test for Redis® Pub/Sub

Open two terminal windows. In the first, launch the backend listener:

$ php /var/www/html/backend.php

Note: this script will remain running as it continuously waits for incoming messages.

In the second terminal, use the following curl commands to simulate data submission:

$ curl -X POST http://localhost/frontend.php -H 'Content-Type: application/json' -d '{"customer_id":1, "billing_period": "2021-08-31", "units_consumed":12.36}'
$ curl -X POST http://localhost/frontend.php -H 'Content-Type: application/json' -d '{"customer_id":2, "billing_period": "2021-08-31", "units_consumed":40.20}'
$ curl -X POST http://localhost/frontend.php -H 'Content-Type: application/json' -d '{"customer_id":3, "billing_period": "2021-08-31", "units_consumed":24.36}'

Each successful post should return:

Data successfully sent to the billings channel.

The backend terminal should simultaneously display:

The Redis® data was sent to the MySQL database successfully.

To verify, log into the MySQL server:

$ mysql -u billing_db_user -p

Use your user password (e.g., EXAMPLE_PASSWORD). Then run:

mysql> USE billing_db;

Expected output:

Database changed.

Then, retrieve billing records with this SQL JOIN:

mysql> SELECT
           billings.ref_id as bill_reference_no,
           customers.customer_id,
           customers.first_name,
           customers.last_name,
           billings.billing_period,
           billings.units_consumed,
           billings.cost_per_unit,
           CONCAT('$', (billings.units_consumed * billings.cost_per_unit)) as bill_amount
       FROM billings
       LEFT JOIN customers
       ON billings.customer_id = customers.customer_id;

You should receive output similar to this:

+-------------------+-------------+------------+-----------+----------------+----------------+---------------+-------------+
| bill_reference_no | customer_id | first_name | last_name | billing_period | units_consumed | cost_per_unit | bill_amount |
+-------------------+-------------+------------+-----------+----------------+----------------+---------------+-------------+
|                 1 |           1 | JOHN       | DOE       | 2021-08-31     |          12.36 |           2.5 | $30.9       |
|                 2 |           2 | MARY       | SMITH     | 2021-08-31     |           40.2 |           2.5 | $100.5      |
|                 3 |           3 | STEVE      | JONES     | 2021-08-31     |          24.36 |           2.5 | $60.9       |
+-------------------+-------------+------------+-----------+----------------+----------------+---------------+-------------+
3 rows in set (0.00 sec)

Conclusion

Throughout this tutorial, you’ve utilized Redis® Pub/Sub to transmit and process water usage data on Ubuntu 20.04 using PHP. The data was successfully routed from Redis® to a MySQL database, showcasing Redis®’s powerful ability to decouple system components in real-time applications.