automatically update mysql field based on value of other field

I have a mysql table for subscription details

It has a start_date, end_date and status field

Now, what i would like to do is whenever end_date of subscription is reached, i would like the status field to c开发者_如何学Change from active to inactive

for example when end_date > curdate, update subscription table set status=inactive

Is there a way to run a check at the end of each day to check and update this?

how can i do this?

Why not just create a view and calculate the status. Then use the view instead of the table:

CREATE VIEW vwSubscription
   AS 
  SELECT
      start_date,
      end_date,
      IF(end_date > curdate, 'inactive', status) as status
  FROM subscription

you can use the cron job in linux based server to check and manipulate the data in db with php

what you need to write is a script that runs in the background like this

while (true) {
   echo 'updating';
   //check rows here
   echo 'done';
   sleep(10);
 }

Just execute it in the shell (php -f script.php) and put that in the background and it will do what you want. Just remember that memory is important. Make sure you unset($var) everything on each cycle so your memory usage doesn't go through the roof.