Creating event in Mysql

Asked

Viewed 899 times

2

I would like to create an event based on 2 situations.

I have a table and a column situat_id with values

[1 - free], [2 - busy], [3 - Unavailable];

And another modified column - generates the modification date of the register.

The event shall occur as follows.

Run the event all day at 5 am.

If the situao_id is = 2 - take the change value(date) > 3 days. UPDATE TABLE situacao_id returns to 1.

But I don’t know how to create the event this way.

And create the event that if the registration date is > 6 months give a DELETE in the table.

I don’t know how to start commands to create events in mysql.

Solution: From Mysql 5.1 there are events that we can perform actions in MYSQL.

We make a change in the status of the user’s registration. A registration after 3 days of the modification date, returns the available situation.

Through the Phpmyadmin panel by clicking on the bank in the tabs we have EVENTS.

By clicking there we can create events that can be for minutes, hours, days, weeks.

Can be executed 1 single time as "One Time" or recurring, running as Execute Every, stating as day or week or months, etc...

In the settings we insert the execution in mysql That in my case it would be so:

UPDATE cadastros SET situacao_id = '1' WHERE DATEDIFF(CURDATE(), modified) > 3;

Running, all day at 5 o'clock in the morning checks all entries, if the change date is over 3 days it makes the update of the situation to 1 Free.

php mysql mysqli

  • The problem is running php every day at 5 o'clock in the morning. Who will run the script every day at this time?

  • You will have to create a CRON to run a script in PHP to do this. In CRON you can set it to run every day at any time.

  • It has a documentation, I don’t know if it works because I don’t understand English https://dev.mysql.com/doc/refman/5.7/en/create-event.html

  • @Leocaracciolo and @Kayo, do not need CRON, the event in mysql is set to run every day, is a routine event that mysql does from version 5.1, vc creates the event to run it always all day. CREATE DEFINER=root@localhostEVENTMuda_statusON SCHEDULE EVERY 1 DAY STARTS '2017-06-16 11:38:00' ON COMPLETION NOT PRESERVE ENABLE DO UPDATEcurriculaSETsituaca_id`= 1 The terms EVERY 1 DAY STARTS '2017-06-16 11:38:00' ON - means every day starting '2017-06-16 11:38:00'

  • @Marcelo Rossi thanks, I had never heard of it, news for me!

  • @Leocaracciolo I’m studying this documentation, but I’m not able to create the event based on my need, so I’m asking for help.

  • See if this post helps https://stackoverflow.com/questions/3070277/mysql-event-scheduler-on-a-specific-time-everyday

  • more in https://answall.com/questions/41467/comorcriar-evento-diario-no-mysql-5-6

  • http://respostas.guj.com.br/28429-como-criar-um-evento-limpar-tabela-mysql-todos-os-dias

  • I’m trying to do this at the event: UPDATE curriculasetsituaca_id= 1,modified= SYSDATE() wheremodified> (SYSDATE() -3) andsituaca_id in (2); But running SQL does not run errors, but does not perform any action.

Show 5 more comments

1 answer

1

CREATE EVENT 
    evt_change_status ON SCHEDULE EVERY 24 HOUR 
    STARTS '2017-11-15 05:00:00' DO 
        UPDATE cadastros SET situacao_id = '1' WHERE DATEDIFF(CURDATE(), modified) > 3;

Browser other questions tagged php mysql mysqli

You are not signed in. Login or sign up in order to post.