Count down time in database

[wignall6]

Hello,

I am trying to create a game inspired by the film In Time. I was wondering is it possible to make a count down timer in the database. The idea is everyone is given 1 week time, and then there will be ways in which they can make more time and there are things they can do to spend the time.

This is the first bit I am looking at and before I start making anything else for the game I want to make sure this is possible or making everything else will be a waste of time.

Thanks, Tom.

[jacek]

The way I would do this would be to store the time that their timer would run out in the database, then when they get more time on their timer you can add a bit more time to this number.

[bowersbros]

Maybe set the expiry time (eg. Now + 1 week) and setup a cron job to check if that time has been reached?

[wignall6]

I have this code which will work out how many days, hours, minutes and seconds, from seconds. Is there a possibility to keep counting down a number in the database. For example I auto set it so when they start they get 1 week which is 604800 seconds, then every second that number is subtracted by 1 in the database. Is this possible? Or is it not as efficient.

[syntax=php]<?php
$seconds = 100000;

$days = $seconds / 86400;
$day_explode = explode(".", $days);
$day = $day_explode[0];

$hours = '.'.$day_explode[1].'';
$hour = $hours * 24;
$hourr = explode(".", $hour);
$hourrs = $hourr[0];

$minute = '.'.$hourr[1].'';
$minutes = $minute * 60;
$minute = explode(".", $minutes);
$minuter = $minute[0];

$seconds = '.'.$minute[1].'';
$second = $seconds * 60;
$second = round($second);
echo $day.' Days '.$hourrs.' Hours '.$minuter.' minutes, '.$second.' seconds';
?>[/syntax]

[bowersbros]

That isnt very efficient atall.

What you should do, since the person doesnt need to see it decrease. Is have the time that it will expire.

For 1 week it will be now + 604800 seconds. Then use a cron job on the PHP side to show that it is deleting 1 second every second if the user does need to see it.

When you set it, for example now. It will be 1325157384 + 604800 = 1 325 762 184

Thats when it will expire. Therefore, if i were to store that in a database, since its a timestamp. I could just use a cron job to run a PHP page which will check whether that has been reached yet.

Eg:

[syntax=php](now() == $timestamp) ? "reached" : "not reached";[/syntax]

Run that in a loop for each database instance, maybe once an hour or so. If it has been reached you can either delete the row, or update part of it to show that it has been reached so you dont need to check that again in the future.

And if the user needs to see how long left until something is done, you would just do

timestamp - now() and then use date("Y-m-d H:i:s",$timestamp); or something and get and output.

Hopefully that helps

[wignall6]

That is a lot more understandable, Thanks very much.

[wignall6]

Since I want people to die once their time runs out, would it be efficient to run the cron job every second?

[bowersbros]

Not particarly. That would run the exact same number of queries as before, hence inefficient.

This will run 1 query with a loop inside the query = more efficient then a loop of one query (in php)

[syntax=sql]CREATE PROCEDURE timecheck()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE holder1 LONG;
DECLARE holder2,holder3 INT;
DECLARE result1 CURSOR FOR SELECT id,time,dead FROM database.table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN result;

read_loop: LOOP
FETCH result1 INTO holder1,holder2,holder3;
IF done THEN
LEAVE read_loop;
END IF;

IF NOT holder3 = 1 THEN
IF UNIX_TIMESTAMP() = holder2 THEN
UPDATE `table` SET `dead` = '1' WHERE `id` = holder1;
END IF;
END IF;
END LOOP;

CLOSE result1;
END;
[/syntax]

[jacek]

Since I want people to die once their time runs out, would it be efficient to run the cron job every second?

It won't kill the server but it's really not necessary.

The user only needs to know that they died when they log in so you can use the method as above and just check if they are dead when every you need to know.