How to automatically change a value in MySQL-CodePudding

I'm working on a system for payments for a school and I need to automatically add a new monthly debt for students, related to the month price. I was thinking about using scheduled events but it hasn't worked when I'm testing.

Please could anyone tell me if this is a good approach or give me any piece of advice for another approach? Thanks in advance.

This is what I was testing but it seems that it's not running since there's no new value on the debts table.

delimiter $$
create definer=`root`@`localhost` event registrar_mensualidad 
on schedule every 5 second 
on completion preserve
enable
do
begin 
insert into debts(auto, amount, student_id, institution_payment_reason_id) 
values (1, 100, 1, 1); 
end;$$

I checked if the events_scheduler is enabled and it is in fact.

The system is being developed with laravel connected to a mysql database.

CodePudding user response：

You might consider cron jobs.

CRONTAB (at every 1st day of the month)

#: sudo nano /etc/crontab

0 0 1 * * root php /var/www/html/your_php_query.php > output.txt

PHP

$qry = "insert into debts(auto, amount, student_id, institution_payment_reason_id) 
values (1, 100, 1, 1);"
return $con->query($qry);

LOG

ouput.txt

1 // if insert is true

Or Laravel

App\Console\Kernel

<?php
 
namespace App\Console;
 
use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;
use Illuminate\Support\Facades\DB;
 
class Kernel extends ConsoleKernel
{
    /**
     * Define the application's command schedule.
     *
     * @param  \Illuminate\Console\Scheduling\Schedule  $schedule
     * @return void
     */
    protected function schedule(Schedule $schedule)
    {

        .........

        // add this
        $schedule->call(function () {
            DB::insert('insert into debts(auto, amount, student_id, institution_payment_reason_id) values (?, ?, ?, ?)', [1, 100, 1, 1]);
        })->monthly();
    }
}

To check/validate the schedule, type: php artisan schedule:list

Output:

  0 0 1 * *    Closure at: app\Console\Kernel.php:22  Next Due: 3 weeks from now