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;
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