Build a Telegram Bot That Talks to the Blockchain (part 2)

Why We Need a Database at All

Every bot needs a memory.

When someone subscribes to monitor their Ethereum rewards, that information can’t just live in RAM - it must persist between restarts, updates, or crashes. That’s what a database gives us:

• Persistence - data survives even if the server restarts. 
• Querying - we can ask: "Show all wallets for this user" or "Find who needs a daily report."
• Consistency - there’s a single, reliable source of truth for users, wallets, and settings.

Our Telegram bot uses MySQL for that job - it’s lightweight, fast, and works perfectly with the Longman PHP Telegram library.

The Database Structure (and Why It’s Built This Way)

When we ran structure.sql in the first article, the library created its own set of tables - including one called user. That’s where Telegram user profiles are stored. Now we’re adding our own table wallets to store subscriptions.

Together, they form a simple and solid pair:

1. The user table (from the library)

• id - Telegram user ID (stable, numeric, never changes)
• username – optional Telegram handle (can change)

Think of it as a directory of everyone who ever interacted with your bot.

2. The wallets table (our custom table)

Each row represents a single monitored wallet:

• id - Internal row ID, for sorting and referencing
• user_id - Connects to user.id – this is how we know who owns what
• blockchain - Short code like ETH, SOL, or TRX
• token - The token symbol, e.g. USDC
• wallet_address - The wallet you’re monitoring
• report_frequency - How often the bot reports: Hourly, Daily, Weekly
• threshold - Notify only when rewards cross this value
• name - Human-readable label (e.g. "Main Wallet")
• created_at, updated_at - When the record was created or changed

Why it’s designed this way

1. Stable linkage - Telegram user_id never changes, while usernames can. That’s why we store user_id in wallets.
2. Simplicity first - we only store configuration, not live balances. That keeps writes fast and queries predictable.
3. Scalable design - adding new fields later (like last_reported_at or status) won’t break existing code.
4. Precision — DECIMAL(36,18) for threshold values means no rounding errors with token decimals.

Recommended indexes

Indexes make queries instant - especially when your bot grows.

ALTER TABLE user ADD INDEX idx_username (username);
ALTER TABLE wallets ADD INDEX idx_user (user_id);
ALTER TABLE wallets ADD INDEX idx_name (name);

How We’ll Use the Database

Now that you have subscriptions saved through /subscribe, the goal is simple:

• Query MySQL for all wallets owned by the current user. 
• Return them as a clean, formatted list. 
• Make it work whether you run /subscriptions 

We’ll achieve this in three small steps:

1. Understand this tiny data layer and why we need SQL queries.
2. Write safe queries using prepared statements.
3. Use those queries inside the /subscriptions command.

Writing Safe and Reusable Queries

When working with MySQL in PHP, always use prepared statements. They prevent SQL injection and handle special characters automatically.

We’ll use the connection already managed by the Telegram library:

use Longman\TelegramBot\DB;
$pdo = DB::getPdo();

Example 1 - Get all wallets for a user

Once we know the user ID, this query gives us every wallet they monitor:

SELECT id, name, blockchain, token, wallet_address, report_frequency, threshold, created_at, updated_at
FROM wallets
WHERE user_id = :user_id
ORDER BY id ASC;

Add LIMIT and OFFSET if you ever want pagination.

Example 2 - Summaries

Quick counts by blockchain or token:

SELECT blockchain, COUNT(*) AS cnt
FROM wallets
WHERE user_id = :user_id
GROUP BY blockchain
ORDER BY cnt DESC;

That’s perfect for future features like: "You’re tracking 5 wallets on Ethereum and 3 on Polygon."

The Code: SubscriptionsCommand.php

Here’s the command that handles /subscriptions. It uses the same Conversation system as /subscribe, but instead of saving data, it reads from the database.

<?php

namespace Longman\TelegramBot\Commands\UserCommands;

use Longman\TelegramBot\Commands\UserCommand;
use Longman\TelegramBot\Entities\InlineKeyboard;
use Longman\TelegramBot\Entities\Keyboard;
use Longman\TelegramBot\Entities\ServerResponse;
use Longman\TelegramBot\Exception\TelegramException;
use Longman\TelegramBot\Request;
use Longman\TelegramBot\DB;
use Longman\TelegramBot\Conversation;
use PDO;

class SubscriptionsCommand extends UserCommand
{
    protected $name = 'subscriptions';
    protected $description = 'Subscriptions list';
    protected $usage = '/subscriptions';
    protected $version = '1.2.0';
    protected $private_only = true;

    public function execute(): ServerResponse
    {
        $message = $this->getMessage();
        $chat_id = $message->getChat()->getId();
        $user_id = $message->getFrom()->getId();
        $text = trim($message->getText(true));

        try {
            // Start or continue the conversation
            $conversation = new Conversation($user_id, $chat_id, $this->getName());
            $notes = &$conversation->notes;
            !is_array($notes) && $notes = [];
            $state = $notes['state'] ?? 0;

            switch ($state) {
                case 0: // Step 1: Show list of subscriptions
                    if ($text === '') {
                        $pdo = DB::getPdo();
                        $stmt = $pdo->prepare('SELECT * FROM wallets WHERE user_id = :user_id');
                        $stmt->bindValue(':user_id', $user_id, PDO::PARAM_INT);
                        $stmt->execute();
                        $wallets = $stmt->fetchAll(PDO::FETCH_ASSOC);
                        //$wallets = [];
                        if (empty($wallets)) {
                            return Request::sendMessage([
                                'chat_id' => $chat_id,
                                'text' => "You don't have any subscriptions yet. Use /subscribe to add a new wallet for monitoring.",
                            ]);
                        }

                        $message = "📜 *Your subscriptions :*\n\n";
                        $inlineKeyboard = [];

                        foreach ($wallets as $index => $wallet) {
                            $walletName = $wallet['name'] ?? "{$wallet['blockchain']}-{$wallet['wallet_address']}";
                            $balance = $wallet['balance'] ?? '0';
                            $rewards = $wallet['rewards'] ?? '0';

                            $message .= ($index + 1) . "️⃣ *$walletName* [{$wallet['blockchain']} | {$wallet['token']}]\n";
                            $message .= "Balance: {$balance} {$wallet['token']} | Rewards: {$rewards} {$wallet['token']}\n\n";
                        }

                        $notes['state'] = 1;
                        $conversation->update();
                        $keyboard = new Keyboard(['Refresh', 'Manage']);
                        $keyboard->setResizeKeyboard(true)->setOneTimeKeyboard(true);
                        return Request::sendMessage([
                            'chat_id' => $chat_id,
                            'text' => $message,
                            'parse_mode' => 'Markdown',
                            'reply_markup' => $keyboard,
                        ]);
                    }

                default:
                    $conversation->stop();
                    return Request::sendMessage([
                        'chat_id' => $chat_id,
                        'text' => 'Something went wrong. Please try again.',
                    ]);
            }
        } catch (TelegramException | \Exception $e) {
            error_log('Error in AddSubscriptionCommand: ' . $e->getMessage());
            return Request::sendMessage([
                'chat_id' => $chat_id,
                'text' => 'An error occurred. Please try again later.',
            ]);
        }
    }
}

How it Works - Step by Step

Step 1: Get user and chat context

$message = $this->getMessage();
$chat_id = $message->getChat()->getId();
$user_id = $message->getFrom()->getId();

Each user has their own Telegram user_id. We’ll use that to filter subscriptions from the wallets table.

Step 2: Query the database

$pdo = DB::getPdo();
$stmt = $pdo->prepare('SELECT * FROM wallets WHERE user_id = :user_id');
$stmt->bindValue(':user_id', $user_id, PDO::PARAM_INT);
$stmt->execute();
$wallets = $stmt->fetchAll(PDO::FETCH_ASSOC);

We’re using a prepared statement - the safest way to query a database. Even if the user input were malicious, the parameters are safely bound (no SQL injection).

Step 3: Format results

Each wallet is formatted with blockchain, token, address, and thresholds:

foreach ($wallets as $index => $wallet) {
    $walletName = $wallet['name'] ?? "{$wallet['blockchain']}-{$wallet['wallet_address']}";
    $message .= ($index + 1) . "️⃣ *$walletName* [{$wallet['blockchain']} | {$wallet['token']}]\n";
    $message .= "Balance: {$wallet['balance']} {$wallet['token']} | Rewards: {$wallet['rewards']} {$wallet['token']}\n\n";
}

Even if balance or rewards are empty, the bot still shows a clean list.

Step 4: Display and update conversation

At the end, the bot sends back the message to the chat:

return Request::sendMessage([
    'chat_id' => $chat_id,
    'text' => $message,
    'parse_mode' => 'Markdown',
    'reply_markup' => new Keyboard(['Refresh', 'Manage'])
]);

That’s your first read-only dashboard, right inside Telegram.

The SQL Queries Explained

Get user’s wallets

SELECT * FROM wallets WHERE user_id = :user_id ORDER BY id ASC;

Count wallets per blockchain

SELECT blockchain, COUNT(*) AS total
FROM wallets
WHERE user_id = :user_id
GROUP BY blockchain;

Get all usernames who have at least one wallet

SELECT DISTINCT u.username
FROM user u
JOIN wallets w ON w.user_id = u.id;

You can later use that for admin reports or broadcasting.

Testing Your Command

1. Open your Telegram bot.
2. Type /start (if you haven’t yet).
3. Run /subscriptions

If you’ve used /subscribe earlier, you’ll see your wallets listed. Otherwise, the bot will reply:

"You don’t have any subscriptions yet. Use /subscribe to add a new wallet for monitoring."

What You’ve Learned

• How Telegram stores user data in the user table.
• Why we use user_id as the anchor key
• How to query MySQL safely with PDO.
• How to build a clean message list in Telegram.
• How to use conversations for multi-step interactions.

Your bot now remembers and retrieves - it’s officially a two-way system. Next, we’ll make it act - checking live rewards and sending updates automatically.

Coming Next

In Part 3, we’ll:

• Fetch live liquidity data via the Allbridge Core REST API.
• Compare current rewards with saved thresholds.
• Send automatic Telegram notifications (even while you sleep)