List items from DB (InlineKeyboard)

[MyZik]

Hi everyone!
In my db I have a table fruits with rows: name, count and price. I need to list all items from that table with buttons "Next" and "Previous" in 1 message with InlineKeyboard.
How can I use that? It will be cool if you show an example...
Thanks in advance!

[MyZik]

Can anyone help me? :D

[noplanman]

Hi @MyZik!

Right, for a pagination with numbers, check out this new project:
https://github.com/php-telegram-bot/inline-keyboard-pagination

It doesn't support "Next" and "Previous" buttons just yet, but it's planned 👍

[MyZik]

@noplanman Thanks for your answer. Can this solution list items from table? To avoid having to assign the total number of pages manually... And how can I output any data on any page?
For example: page 10
Shows me data:
id: 10
name: Banana
message: yummm
and photo c:

How can I do that?

[noplanman]

Absolutely! It needs a fair bit of code though, here we go!

Here and example /fruits command (this can be further optimised, but it should illustrate how it works):

<?php

namespace Longman\TelegramBot\Commands\AdminCommands;

use Longman\TelegramBot\Commands\UserCommand;
use Longman\TelegramBot\Entities\CallbackQuery;
use Longman\TelegramBot\Request;
use TelegramBot\InlineKeyboardPagination\Exceptions\InlineKeyboardPaginationException;
use TelegramBot\InlineKeyboardPagination\InlineKeyboardPagination;

class FruitsCommand extends UserCommand
{
    protected $name = 'fruits';
    protected $description = 'Display fruits, with inline pagination.';
    protected $usage = '/fruits';
    protected $version = '1.0.0';
    protected static $per_page = 3;

    /** @var array Fruits to display inline (can also be dynamically generated) */
    public static $fruits = [
        'apple'         => ['id' => 1, 'name' => 'Apple', 'message' => 'Mmhhh, delicious'],
        'orange'        => ['id' => 2, 'name' => 'Orange', 'message' => 'Mmhhh, delicious'],
        'cherry'        => ['id' => 3, 'name' => 'Cherry', 'message' => 'Mmhhh, delicious'],
        'banana'        => ['id' => 4, 'name' => 'Banana', 'message' => 'Mmhhh, delicious'],
        'mango'         => ['id' => 5, 'name' => 'Mango', 'message' => 'Mmhhh, delicious'],
        'passion_fruit' => ['id' => 6, 'name' => 'Passion fruit', 'message' => 'Mmhhh, delicious'],
    ];

    public static function callbackHandler(CallbackQuery $query)
    {
        $params = InlineKeyboardPagination::getParametersFromCallbackData($query->getData());
        if ($params['command'] !== 'fruits') {
            return null;
        }

        $data = [
            'chat_id'    => $query->getMessage()->getChat()->getId(),
            'message_id' => $query->getMessage()->getMessageId(),
            'text'       => 'Empty',
        ];

        // Using pagination
        if ($pagination = self::getInlineKeyboardPagination($params['newPage'])) {
            $data['text']         = self::getPaginationContent($pagination['items']);
            $data['reply_markup'] = [
                'inline_keyboard' => [$pagination['keyboard']],
            ];
        }

        return Request::editMessageText($data);
    }

    public static function getFruits()
    {
        return self::$fruits;
//        return DB::getPdo()->query('SELECT * FROM `fruits`')->fetchAll(PDO::FETCH_ASSOC);
    }

    public static function getPaginationContent(array $items)
    {
        $text = '';

        foreach ($items as $row) {
            $text .= "id: {$row['id']}\n";
            $text .= "name: {$row['name']}\n";
            $text .= "message: {$row['message']}\n";
        }

        return $text;
    }

    public static function getInlineKeyboardPagination($page = 1)
    {
        $fruits   = self::getFruits();

        if (empty($fruits)) {
            return null;
        }

        // Define inline keyboard pagination.
        $ikp = new InlineKeyboardPagination($fruits, 'fruits', $page, self::$per_page);

        // If item count changes, take wrong page clicks into account.
        try {
            $pagination = $ikp->getPagination();
        } catch (InlineKeyboardPaginationException $e) {
            $pagination = $ikp->getPagination(1);
        }

        return $pagination;
    }

    public function execute()
    {
        $data = [
            'chat_id' => $this->getMessage()->getChat()->getId(),
            'text'    => 'Empty',
        ];

        if ($pagination = self::getInlineKeyboardPagination(1)) {
            $data['text']         = self::getPaginationContent($pagination['items']);
            $data['reply_markup'] = [
                'inline_keyboard' => [$pagination['keyboard']],
            ];
        }

        return Request::sendMessage($data);
    }
}

Then, you need to register a callback in your hook.php:

CallbackqueryCommand::addCallbackHandler([FruitsCommand::class, 'callbackHandler']);

That's it! Give this a try 😊

To also add the picture, you'll need to use Request::sendPhoto(...) and also get the URL or Telegram ID of the photo to pass to it.

[MyZik]

@noplanman oh, https://github.com/php-telegram-bot/inline-keyboard-pagination is that required PHP 7? My server has 5.6 and I cann't install :(
Here is screenshot from console: https://imgur.com/a/hogGE

[noplanman]

Yes, you'll need PHP7. Can you upgrade your server?

[MyZik]

@noplanman Standard version on the server - 5.6.30 and I installed version 7, but composer still doesn't see that :(
And I get an error from screenshot.
Debian 8, ISP Manager.

[MyZik]

@noplanman a lot of thanks!!!

@noplanman, I tried your example and everything was going well, but here CallbackqueryCommand::addCallbackHandler([FruitsCommand::class, 'callbackHandler']); my script stoped. There is no "addCallbackHandler"

[noplanman]

@Kurusa What version of the bot do you have?
addCallbackHandler(...) was added in 0.46.0

I don`t know o.o where i can seethis version? 2018-05-22 0:36 GMT+03:00 Armando Lüscher <notifications@github.com>:

…

What version of the bot do you have? addCallbackHandler(...) was added in 0.46.0 — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#589 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AbrZ6RNSxl7ZZxf4ekvs2vOnyocWWFx7ks5t0zNkgaJpZM4O1ptb> .

[noplanman]

In vendor/longman/telegram-bot/src/Telegram.php you can find the $version property.
If you've installed it via composer recently, you should have the latest 0.53.0