How to update a column after an expiration date in MySQL?

- 1 answer

Ad

I am working on a laravel 5 app. I have a table with fields like below:

Table Name: applications

  • application_status tinyint(1)
  • expiration_date datetime (20 days from today)

    1. How can I update the application_status of each row in the table after any expiration_date is passed?
    2. How can I enable that nothing happens when the expiration_date is NULL (The issue is once an application is accepted, the expiration is disabled or set to NULL)
Ad

Answer

Ad

Look in to task scheduling with Laravel. You could schedule a function to check this for you and make changes as required, or create an artisan command and schedule that - the choice is yours.

You really need to give it a crack first and then if you run in to trouble post your code where it is going wrong for us to really answer this for you. However some examples of how you can do this are below:

Scheduling a function

Inside app/Console/Kernel.php:

//Rest of file above

protected function schedule(Schedule $schedule)
    {
        $schedule->call(function () {
            $applications = ModelName::where('expiration_date', '<=', \Carbon\Carbon::now())->get();

            foreach($applications as $application)
            {
                //Update each application as you want to
                $application->application_status = 'example';
                $application->save();
            }
        })->daily();
    }

//Rest of file below

Create a command and schedule that instead

The process for this is very much similar, rather than spell it out word for word you can just follow this blog post.


Once you have the steps inside the scheduler, just set up the scheduler to run with a cron job; * * * * * php /path-to-your-application-artisan schedule:run >> /dev/null 2>&1

And you can of course adjust the frequency at which the function runs.

Because you are looking at records that have a date less than or equal to now, it will ignore those that have a NULL value for the expiration_date.

Ad
source: stackoverflow.com
Ad