0

is it possible to run a query in a specific time like for example every seconds/minutes. By the way I am building a simple auction system and the query I am talking about is to check if there are products that are already expired. Means their datetime_end is less than the current date and time. So I have a query like this:

SELECT id, datetime_end FROM auction_product WHERE datetime_end < NOW() AND `status` = 0;

And after identifying if there are products that are already expired I will create a query that will change their status to 1 means the status is cancelled/closed.

Is there a function in MySQL that can run a certain query? Like automatic query. Or should I make an Ajax that will run every seconds and check if there is an expired product? Can you give me some idea about this? Because in my current setup I just put the AJAX in product page and homepage. Means if the user is in the other page my AJAX will not run. So I think I will put my created AJAX somewhere in the header or footer or in a global JS. If I did that does it affect the performance of my page load? Because it will run every seconds.

That's all thanks. I hope you can give me some idea about this. :)

Jerielle
  • 7,144
  • 29
  • 98
  • 164

2 Answers2

2

You could do this in MySQL using scheduled events e.g.:

CREATE EVENT IF NOT EXISTS expire_products
ON SCHEDULE 
EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP 
DO
  <insert your update statement here>;

You need to enable the event scheduler first either via the command line argument:

 --event-scheduler=ON

or the my.cnf/my.ini option:

   event_scheduler=ON

or by running the following in MySQL:

   SET GLOBAL event_scheduler = ON;

See https://dev.mysql.com/doc/refman/5.7/en/create-event.html for more details.

tanderson
  • 1,169
  • 12
  • 29
1

You don't need to check it every second on the client side. In case someone visits the page of the product, you'll run an ajax there to check if there are enough products left or not. If there are no products left you can update the database on the page itself.

Now you also want to make sure it is regularly updated, so you can run a script on the server side on a Cron Job. But, you also need to make sure you don't run some heavy resource intensive scripts on it. You can run a cron job about every hour or two hours to regularly update it from the server side. And in case any of the users views a product, you will update it automatically with the ajax, so the next time a user visits, in between two cron jobs, they will see the page being already updated because of the earlier user. This will keep the pressure out of your server by distributing the work.

So the idea is somewhat like this:

 1)user enters-> visits page-> runs ajax to check if products are left -> update db if products are over
2)cron job checks if products are left every two hours-> updates db if products are over
twodee
  • 606
  • 5
  • 24