CODEWITHSUNDEEP
phpmysqljobs
Sindar
Sindar

Reputation: 10839

Create a MySql job or loop who's constantly checking a field on a table

Well i'm currently developping a browser multiplayers game and having some problem about the conception. This is a Ogame like (http://www.ogame.org/) using PHP / Js / MySql.

What i want is that players will launch an action (cut the wood) and this action will be ending in XX minutes.

So basicely my idea was to create an on the fly cron which will launch after XX minutes a SQL query, adding in the DB XX ressources to the player.

Another problem is that players can navigate on the sea with their ship, so if they are moving to a certain destination they will make XX meter each 5 minutes for example.

So the thing is that all the players can see each others on the sea. So basicaly, i can't wait for 10 minutes to add XX meter to the player ship, it have to be done after 5 minutes...

EDIT : So basicely i need a MySql job like or a infinite loop who's constantly checking on a table. This table will contain the end time (timestamp) of all the actions. And so the job have to execute a Sql Query when this time is corresponding to the CURRENT_TIME.

Hope you guys have understood my problem ;)

Upvotes: 0

Views: 564

Answers (2)

Rowan Parker
Rowan Parker

Reputation: 794

Rather than having a specific script run in the future, you could have an events table in your database. So when the user initiates the chop wood task (at say 10:30 AM), you add an event to increment their wood balance by 5 at 10:35 AM.

Then you have a cron script which runs every minute. It gets the current time and looks up the database table for any events with a time that is less than or equal to the current time. You then perform that action and remove the event record from the table. Or if you need to keep a history of these things for your players, you can have a column called processed which tracks whether the event has been performed. In which case your cron script looks for unprocessed events before the current time.

Upvotes: 0

Fleep
Fleep

Reputation: 429

What you want is a "job system", or "queueing system", or "event system", or a "message queue".

These can be quite complex to build, but a simple version might just look like this:

  1. You have a database table that just stores a queue of "messages", one message per row, each with some field specifying when it should be handled. Either immediately, or after some timespan, etc.
  2. Your application inserts to this table as-necessary
  3. You have a separate daemon running whose job it is to smartly handle this queue. For example, it pulls down any events that are ready to be handled, based on the criteria you specified when you inserted the event row. Then it handles them. You can run this in a neverending while() loop, and then run the script itself as a background process on your server. If you are handling memory-management well, the script can run forever.

There are a ton of issues with this setup, such as:

  • What happens if you need to divide up the tasks among multiple servers?
  • What happens when the daemon processes events more slowly than new ones are getting inserted?

A better solution to avoid these ways is to throw some money at a 3rd party managed message queue service (like Amazon SQS) or use a prebuilt framework that supports this (like gearman). That way you can smartly pull one event at a time from multiple machines who don't really have to care about how backlogged the system is - they just have to happily churn away at events.

Upvotes: 3

Related Questions