How do I persist data collect from Botman to MySQL

Question

I am using laravel and botman to create a simple issue tracking chatbot. I'm struggling to persist data to MySQL from a Botman reply.

Any suggestions?

Answer 1

I assume that you use botman/studio (see documentation here) and that you want to save the user's answer in the framework of a class that extends the BotMan\BotMan\Messages\Conversations\Conversation class (let's call it MainConversation for this example purpose).

In principle, the steps are roughly as follows.

first, cd to the root of your project directory and open the .env and make sure that your MySQL database is correctly configured. It should look like that:

DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=mybot DB_USERNAME=root DB_PASSWORD=

Then run the following command:

php artisan make:model blabla -m

That will create a migration in the database/migrations directory of your project. Open this php file and edit the up() function and add the columns you want in your new database column:

public function up() { 
    Schema::create('blabla', function (Blueprint $table) {
        $table->increments('id');
        $table->string('id_chat');
        $table->string('response');
        $table->timestamps(); 
    }); 
}

Now run the following command that will create the new table in your database:

php artisan migrate

It is now time to connect your model to the MainConversation class. The principle is illustrated here:

namespace App\Conversations; 

use app\blabla as database; // your model
use BotMan\BotMan\Messages\Incoming\Answer as BotManAnswer;
use BotMan\BotMan\Messages\Outgoing\OutgoingMessage; 
use BotMan\BotMan\Messages\Outgoing\Question as BotManQuestion;
use BotMan\BotMan\Messages\Conversations\Conversation; 

class mainConversation extends conversation { 
    
    public $response;

    public function run () {
        $this->askResponse();
    }

    private function askResponse() { 
        $question = BotManQuestion::create("Blabla ?"); 
        $this->ask( $question, function ( BotManAnswer $answer ) { 
            if( $answer->getText () != '' ){ 
                $this->response=$answer->getText()); 
                $this->exit(); 
            } 
        }); 
    }
    // this function create the object that is linked to your db's table
    private function exit() { 
        $db = new database(); 
        $db->id_chat = $this->bot->getUser()->getId(); 
        $db->response = $this->response;
        $db->save();
        $message = OutgoingMessage::create('Bye!'); 
        return true; 
    } 
}