How to update MySQL column/row when datetime column < now?

Question

I am making a platform where people can make bids on items for a price.

I want to make it so that after a the expire datetime of the item, the database will automatically update the status of the item from "Open" to "Expired" and then send an email to the item owner and bidders to let them know of the new status.

How do I achieve this?

I am using PHP as my scripting language and AWS to send emails.

Is this done in PHP or in MySQL?

Answer 1

Do not go through the table changing the status. Compute the status as you do the SELECT.

It will be significantly faster, and possibly simpler when you look at all the moving parts.

It queries all users who posted a bid on the item, then sends an email to each, letting them know it expired and check the results

That must be done by code outside MySQL. Such a program would poll the database periodically (every minute?) with the statement below. When it finds some matches, it sends the emails and sets the status to "email_sent" (or whatever).

SELECT ...
    FROM tbl
    WHERE this_date < NOW()   -- (I don't know the exact test you need)
      AND status != 'email_sent'

and have

INDEX(this_date)

Updating the status is needed, else you could miss some rows over time, or maybe duplicate some emails. Since sending an email is a bit costly, you could update them one row at a time.