Jump to content
Sign in to follow this  
boionfire81

MYSQLI/PHP & TIMESTAMPS

Recommended Posts

Staff if this is the wrong forum, please move to proper category.

My question is this. How can I use unix timestamps to control and display a precise(down to the second) of a time remaining until the time wait is over?

I know

 

UNIX_TIMESTAMP()

 

will insert the current date/time. But I'm thinking there has to be a way to use this to keep things updated to the second in timestamp crons (including a constant cron for the 'very active'/'much needed' real time countdowns.

If I insert a specific time format, use a typical java countdown, with a constant timestamp cron to update and delete should be all? That's what I'm guessing. Not exactly sure how to go about it though. Any ideas?

[uSER=64684]Dayo[/uSER] I know you know this :)

Share this post


Link to post
Share on other sites

I'm unsure on the question. Can you clarify a little please.

Are you asking how to keep things updated to the second? Or the best way to display countdowns?

Share this post


Link to post
Share on other sites

Well this is just me trying to put two and two together.

By inserting a unix timestamp + number of seconds the wait time is will enable a java countdown on the jail/hosp page to show the exact amount of time remaining.

By having a single query run in globals there should be a way to update the release if time =

Share this post


Link to post
Share on other sites

the best way to do this is to insert the timestamp and the time to wait into the database i.e.

$sql = "UPDATE users SET attackTimer = " . (time() + 60) . " WHERE id = " . $id; // stop the user attacking for 60 seconds


// then you have to check if the user can attack by something like this
if ($user["attackTimer"] > time()) {
 echo "you cant attack for another" . ($user["attackTimer"] - time()) . " seconds!";
 exit;
}

//Javascript code for the browser

var time = ajaxRequest.result.timestamp;
setInterval(function () {
 $(".attack-timer").text((time - Math.round(new Date() / 1000)) + " Seconds");
}, 1000);

Share this post


Link to post
Share on other sites

ok, so basically to trunicate my wait table is simply

 

$db->query(DELETE FROM `wait` WHERE `time` < time()");

 

I'm trunicating for query speeds. As this query will eventually be run ALOT. The less columns and rows the better you know. Plus I'm trying to reduce my users table as much as possible.

Share this post


Link to post
Share on other sites

Relying on crons... is not actually to the second, and if you are intending to make a cron job for that... means you need to run the cron on every second, which is far less than a good idea. Dayo's idea could work on an ideal world, but then again... it will not be to the second. If you bare to have some minimal delay on a not crowded server, go for Dayo's otherwise you could take a look at Mysql Event Scheduler, it might do the trick you need :). I had a simple article i wrote a few years back in here, but I failed to find it. Nevertheless MYsql docs are pretty much explanatory on those.

Share this post


Link to post
Share on other sites
ok, so basically to trunicate my wait table is simply

 

$db->query(DELETE FROM `wait` WHERE `time` < time()");

 

I'm trunicating for query speeds. As this query will eventually be run ALOT. The less columns and rows the better you know. Plus I'm trying to reduce my users table as much as possible.

this has a small fail... as it will remove every single user from the wait table, basically... immediatelly after you run it... so no hospital/jail time ;)

Share this post


Link to post
Share on other sites

Well for one it wont actually be a cron job. More like an update in the globs.

And I don't see the error?

This table only has 3 rows, userid (to tie in with other iznit), time (to deduce their wait time remaining), and a why(ENUM) to simply print out an easy reason to why they are having to wait rather than querying the user table with what seems like 100+ rows. Removing a good 10 rows from the user table and replacing with 1 `accesslevel`.

Share this post


Link to post
Share on other sites
Well for one it wont actually be a cron job. More like an update in the globs.

And I don't see the error?

This table only has 3 rows, userid (to tie in with other iznit), time (to deduce their wait time remaining), and a why(ENUM) to simply print out an easy reason to why they are having to wait rather than querying the user table with what seems like 100+ rows. Removing a good 10 rows from the user table and replacing with 1 `accesslevel`.

It is not an error, just fails to serve the purpose... lets imagine this... a user gets in jail for lets say 30 minutes, and lets say the wait[time] is 12345 (fictional value just for explanation), and you are running the job/cron/whatever every single second, it will trigger when time = 12346 which means that in practice... the user will be in jail for 1 second not 30 minutes...

Share this post


Link to post
Share on other sites

It is not an error, just fails to serve the purpose... lets imagine this... a user gets in jail for lets say 30 minutes, and lets say the wait[time] is 12345 (fictional value just for explanation), and you are running the job/cron/whatever every single second, it will trigger when time = 12346 which means that in practice... the user will be in jail for 1 second not 30 minutes...

It could work - it just depends on how you set it up. If you set "time" in the wait table to be the time that the user should leave the jail/hospital, then the delete query will work, as it will only delete those rows where the time has passed. Otherwise, I agree, it will not serve its intended purpose.

FYI: You should probably avoid using "time" as a column in a table as it is a reserved word in MYSQL. I'm not saying that it won't work the way that you have it, but using reserved words as columns in a table is a bad practice.

~G7470

Share this post


Link to post
Share on other sites

Yeah but that's the delete time.

The insert time is more like

$db->query("INSERT VALUES({$ir['userid']},(time() + 999), 'in jail')");

 

But just doing that I do see one mistake. The accesslevel should actually be in the wait table I think. Will also add reason to the wait table.

Plus I use two additional files with my game. access.php and available.php. These files are what locks the pages if the user is busy. By setting an accesslevel I can easy modify to allow access to specific pages only.

And instead of querying a 100+ column user table everytime something happens somewhere. I can delete about a dozen columns, and only query the wait table (with 5 columns) when needed. For example access.php, jail.php, and hospital.php. The remaining queries would be based on where the access.php file is included. Which still cuts back on query time as the columns are much less, and rows will be deleted when not applicable.

Opinions? Do you think this is a reasonable solution to the overloaded user table? And/Or speed of queries?

 

Share this post


Link to post
Share on other sites

users table is set to global correct? If yes... you are doubleing the effort, once again defeating the purpose of being faster ;)

  • Like 1

Share this post


Link to post
Share on other sites

At this moment yes. But the table structure is being redone ALOT.

I was ticked off when I saw 140 columns in the user table. Literally slowed my comp down just to browse the structure.

And since my game is developing far off the gangster games into a gangster/tycoon/detective game. I'm really wanting to change up the table structure. With fight stats in one, user cash flow in another, and the wait times in another.

While I will have a user/account table to store ids etc, basically everything is branching out. The only constant update will need to be the wait table. Deleting un-needed rows will help speed up that one constant.

As per the rest they can be run from within the areas themselves.

Ideally I think one table per action/feature with an update/remove when used is going to be the best solution in the end.

 

Share this post


Link to post
Share on other sites

I was ticked off when I saw 140 columns in the user table. Literally slowed my comp down just to browse the structure.

You running a pentium 3 over there, dude?

  • Like 1

Share this post


Link to post
Share on other sites

Nothing wrong with an ASUS if you get the right specs along with it...

What you're essentially trying to do is normalise your users table.

But as for your "to the second"

Abandon that idea. You're gonna kill the performance massively.

Just use timestamps and run them at set intervals but have a fall back so that they don't need to run every 10 mins, every 5 mins, every min if no user is online, it'll do run the update as many times is needed for the game to catch up with itself.

Next if you want jail/hosp to look like its counting in real time, pass the timestamps from the database to a JavaScript / jQuery function which counts down.

If need be, pass a callback so that when it reaches 0 it'll fire a call to a php file that will update the database

Share this post


Link to post
Share on other sites

Well the angle I'm using is the wait table has a timestamp. Whenever globals are run it checks the wait table for timestamps expired. Obviously if no one is online it doesn't matter for someone's release or not. But all other crons are still run normally. Allowing energy etc to update regardless. Minimizing the wait table columns and rows to speed up that one query.

Share this post


Link to post
Share on other sites

OK, I'm now starting to get into the code of it all on a per instance/feature basis.

Here is the example for brewery.

Index check if exist/ready

$b=$db->query("SELECT * FROM `brewery` WHERE `userid`={$ir['userid']}");
  if(!$db->num_rows($b)) {
  echo "What type of brew would you like to begin?<br><a href='?action=beer'>Beer</a> | <a href='?action=whiskey'>Whiskey</a> | <a href='?action=wine'>Wine</a>";
  }
else {
$brew=$db->fetch_row($b);
$brews=$db->fetch_row($db->query("SELECT `itmname` FROM `items` WHERE `itmid`={$brew['item']}"));
if($brew['time'] < ".time ().") {
echo "Your {$brews['itmname']} is ready!";
item_add($brew['userid'],$brew['item'],$brew['qty']);
$db->query("DELETE FROM `brewery` WHERE `time` < time()");
}
else {
echo "Your {$brews['itmname']} are still brewing. Come back in ".time_format($brew['time'])." to finish the brew.";
}
}

 

Begin brew

$inv=$db->query("SELECT `qty` FROM `inventory` WHERE `userid`={$ir['userid']} AND `itemid`=206");
   $invc=$db->num_rows($inv);
   if ($invc = 0) {
       echo "You need supplies before you can brew beer.";
   }
   else {
   $db->query("INSERT INTO `brewery` VALUES ({$ir['userid']},".(time() + 3600).",46,12)");
   item_remove($ir['userid'],206,1);
       echo "You have begun the brew. Come back in an hour to collect your beer";
       }

 

As of right now it does detect and wait for completion. However, the insert is incorrect. Instead of 1 hour, it is 14 hours? 60sec. x 60 mins. = 3600 right?

For some reason I could not use timestamp for mysqli, had to use just time.

Share this post


Link to post
Share on other sites
the best way to do this is to insert the timestamp and the time to wait into the database i.e.

 

$sql = "UPDATE users SET attackTimer = " . (time() + 60) . " WHERE id = " . $id; // stop the user attacking for 60 seconds


// then you have to check if the user can attack by something like this
if ($user["attackTimer"] > time()) {
echo "you cant attack for another" . ($user["attackTimer"] - time()) . " seconds!";
exit;
}

//Javascript code for the browser

var time = ajaxRequest.result.timestamp;
setInterval(function () {
$(".attack-timer").text((time - Math.round(new Date() / 1000)) + " Seconds");
}, 1000);

[uSER=64684]Dayo[/uSER] I'm only having 2 issues with this.

1) I'd like to echo the time right after the db query. But it looks like this:

"in -17056 days, -22:, 0-13:-13"

$time=rand(60,180);
$totaltime=$time*$_POST['energy'];
$ttime=".(time.()+$totaltime).";
   echo "You will see daylight again in ".time_format($ttime).". ";

 

function time_format($time)
{
  $ttime=($time-time());
  $days = floor($ttime / 60 / 60 / 24);
  $hours = floor($ttime / 60 / 60) % 24;
  $mins = floor($ttime / 60) % 60 % 24;
  $sec = floor($ttime) % 60 % 60 % 60 % 24;
  if($days) { $days = "$days days, and "; } else { $days = ""; }
  if($hours) { $hours = "$hours:"; } else { $hours = ""; }
  if($mins == 1) { $mins = "1"; } else { $mins = "$mins"; }
  if($sec == 1) { $sec = ":01"; } else { $sec = ":$sec"; }
  return $days.$hours.$mins.$sec;
}

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...