As insert records every certain date with php mysql

Question

As insert records every certain date with php mysql

Navigation

#1 by (1 votes)

1

It turns out that I have an app for preventive maintenance. My problem is this:

In the "orders" table, the orders of each "resource" or team are loaded. But preventive maintenance is done every so often, so when loading a Preventive work order, it is asked how often it should be done, so the user should see in his "desk" if he has orders to expire.

How do I do so, eg: Resource 1 has a work order every 30 days? the first one is easy, because when creating the first work order (let's call it OT A) 30 days are added to the creation date, but I need that once the OT A expires, another work order B is created at the same time resource automatically, and the OT B expired, the OT C is created and so on ...

The base orders have the following structure:

 id_ot, id_recurso, protocolo, tipoman, fechaot, fechavto, usercarga, 
    estado_ot, observa_orden, cierra_ot, user_cierra, fecha_cierra

I hope you explained, thank you very much!

mysql php

asked by Daniel 08.09.2018 в 05:50

source

1 answer

Error stack trace in MVC Error when opening a Project in laravel

score 1 · Accepted Answer

What you want to do is a scheduled task, this is achieved in the following way:

The first thing we do is see if the task manager is active with the following query;

SHOW VARIABLES LIKE 'event_scheduler';

by default this is off, we activate it with;

SET GLOBAL event_scheduler = ON;

Until here if everything is correct, executing the first command you should see that the result of the first query is ON , here we are going to program your task

USE mi_base_datos; -- Base de datos sobre la que ejecutaremos la tarea

CREATE EVENT ordenes
  ON SCHEDULE 
    EVERY 1 DAY
  DO -- De aca hacia abajo vas a escribir lo que quieres que haga la tarea

Let's say you want to create an order for those that have not closed in 30 days or more, taking the data you've provided I can do the following;

USE mi_base_datos;

CREATE EVENT ordenes
  ON SCHEDULE 
    EVERY 1 DAY
  DO
  INSERT INTO ordenes (campo1, campo2,campo3)  SELECT campo1,campo2,campo3 FROM ordenes WHERE DATEDIFF(fechaot,NOW()) >= 30

Notice that the task inserts 3 fields and the SELECT selects 3 fields also, after that it selects all those that are greater than 30 days by the field fechaot with the function DATEDIFF this returns the difference in day between 2 dates.