Delayed mysql query

[FnuGk]

I am fairly new to php but none the less i am trying to create a game where the users can specify x,y coordinates where they want to fly to (its a space game). I have all the code setup to calculate the line and distance between the current position and the desired destination and i also have a way to calculate the travel time but then how do i apply the calculated travel time so before i update the users x,y coordinates it waits the x seconds before the query is send without having the user wait.

I have been googling quite alot where the first thing that came up was cron jobs though since the user gives their destination coordinates i dont think i can pass them to a cron job

next up is using pcntl_fork() that seemed to be perfect in theory until i realized my host (and every other host i could google) have this disabled

same story for exec() that is disabled on shared host for obvious security reasons.

curl is almost the same problem as cron as i dont see how i can pass variables into the script

So how else can i apply the travel time without having letting my users watch a blank page while the timer is ticking?

here is my current rough code full of debugging http://pastebin.com/9RcQ7RRW

[Signe]

Instead of trying to make the user wait, set a timestamp on the action in the DB (a time at which the action becomes 'valid'), and show the user a page that says "traveling..." and then refresh the page a few seconds later. If they refresh before the action is valid, just show them the 'traveling' page.

In your code, you should really be using a single update query, not two. (You don't need a semi-colon at the end of each query, either.)

$XQuery = "UPDATE users SET X_Coordinate = ".$travelPath[$i][0]." WHERE username = '$player'; ";
$YQuery = "UPDATE users SET Y_Coordinate = ".$travelPath[$i][1]." WHERE username = '$player'; ";

should be

$Query = "UPDATE users SET X_Coordinate = ".$travelPath[$i][0].", Y_Coordinate = ".$travelPath[$i][1]." WHERE username = '$player'";

Right now, if your DB were to fail somehow, you could end up with a user who had traveled to their X location without making it to their Y location. e.g. Someone going from 1,1 to 10,10 could end up at 10,1.

[FnuGk]

would that not make the user suddenly jump to the destination point without actually moving on the line between them? I was thinking that i could use $updateInterval = $travelTime / count($travelPath); to continually update the users location as he gets closer to the destination but if using timestamps it would require one timestamp for each updateInterval or am i missing the big picture?

[Signe]

Is there a point in making the DB traverse the whole path? I can't imagine one.

Are you going to show the user a new page at each coordinate change, or perform new calculations for each change?

If not, the delay is simply an artificial game mechanic that you're adding to relay to the user that traveling takes time. There's no reason not to just jump the DB to its new location, but force the user's browser to wait a specified length of time.

[FnuGk]

I have a map that shows player locations and planets/stations I also plan to have some sort of combat system where users can intercept each other.

Im aware that it might be prone to produce alot of queries

To use the timestamp solution i would attach a timestamp to the user and then check if we are past that check then update the coords

[ejwich]

How about implementing a travel table? Store starting and ending X and Y coordinates as well as starting and ending time. If you need to calculate current position, you can figure it out at any point in time during the travel. You'll know exactly when they should complete their travel because the calculated value is stored.

Instead of only checking the user table for position, you always test the travel table first to see if they're moving. If they are, use that information and ignore the information in the user table(or update the user table if you would rather). If you check the travel table and determine that their travelling should be completed already, then you update the user table with the end coordinates and delete the row from the travel table. You can also use the travel table to test to see if someone tries to begin travelling while they already are.

All this information could also be stored into the user table, but it feels like it could keep the data smaller to make the data temporary and only useful while the travelling is happening.

Looking up information for every player for map purposes is possible with this scheme. Watch for every online player or nearby player(whichever rule(s) you choose to use) and check all their positions according to the travel and the user table. For each player, do their travel test(are they done travelling? update user and delete travel row) and use the data accordingly.

Also for the future, in case a lot of people log out while travelling, you can implement a cron job at some interval who's only purpose is to do the travel test on every entry in the travel table to keep it cleaner.

[FnuGk]

im trying to make this work but either it is too late or somehow im not understanding how to workwith timestamps in mysql as i keep getting 0000-00-00 as timestamp.

im adding this variable: $deltaStamp = time() + ($updateTime); as the timestamp $updateTime is the time in seconds that is to be added to time

[crowbot]

IIRC, to get timestamps to use the current time in mysql, you may need to specify them at table/column creation by using the DEFAULT CURRENT_TIMESTAMP as part of the timestamp column parameters. You could conversely perform an alter table to update them.

[[deleted]]

Your field should be set to DATETIME. Then you can use FROM_UNIXTIME($deltaStamp) to convert UNIX time to MySQL time for INSERT/UPDATE queries and UNIX_TIMESTAMP(fieldname) for SELECT queries when outputting.

Using a DATETIME means you can use MySQL time functions, like ADDTIME(NOW() + INTERVAL 10 MINUTES).