Member Avatar for s_kanika

I have a field Status in my table message
Its value when any message is entered in table is 1
but I want to alter it to 0 after 1 day of message posting
how can I do so
Plz help me

  • 3 Contributors
  • 2 Replies
  • 78 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Member #733618

Dani AI

Generated

wanted rows with Status=1 to become 0 one day after insertion. and pointed toward doing this from outside the database; an alternative that avoids an external scheduler is to let MySQL handle it with its Event Scheduler. That keeps the logic in the DB, reduces external dependencies, and is easy to maintain when the host allows events. The scheduler must be enabled and the account needs the EVENT privilege — see the MySQL docs for details (Event Scheduler, CREATE EVENT).

Example event (runs periodically and updates rows older than 1 day):

CREATE EVENT expire_message_status
ON SCHEDULE EVERY 5 MINUTE
DO
  UPDATE message
  SET Status = 0
  WHERE Status = 1
    AND created_at < NOW() - INTERVAL 1 DAY;

If keeping a stored Status field is not required, compute it at read time instead — no background job needed and the value is always correct:

SELECT id, text,
  (created_at >= NOW() - INTERVAL 1 DAY) AS StatusComputed
FROM message;

Notes and cautions: enable the event scheduler on the server or in my.cnf, watch timezones (use UTC for consistency), and avoid large single updates on big tables — update in indexed batches (use LIMIT) to prevent long locks. If events are not available on the host, an external scheduler remains the fallback option.

Recommended Answers

All 2 Replies

Member Avatar for pritaeas

You can setup a cronjob to run a php file that runs an update query.

Member Avatar for Member #733618

you can use php or java !

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.