Timer script issue.

[thatshanekid]

Hello guys.
I had a few problems with this script, I fixed all the errors and such, but it isn't functioning the way I want it to :/

the Idea is this:

I was going to grab the current time using time();
and place it into the database.
Then I would set another value in the structure called check, to 1.

The database while the value is set on 1 will keep checking every time a user refreshes.
It would get the time from the database that was set, and the current time again. It would take away the stored time from the current time and if that value equals less then ten, it would keep running the script and won't change the check. But if it was over ten, it would do a random function (What ever the user wanted, right now it just an echo for a test) and delete both the current timestamp in the database, and set the check to 0.

If the value was 0, it would set the new timestamp in the database, eg the current time, and set the value to 1 so it will keep running again.

Is this possible?

If it was, I tried.
Here is my code:

<?php

$db = mysql_pconnect("localhost","root",""); 
mysql_select_db("test",$db);

$time = time();
$result = mysql_query("SELECT * FROM timer");

while($row = mysql_fetch_array($result))
  {
  $timestamp = $row['timestamp'];
  $check = $row['check'];


if ($check=="0") {
mysql_query("UPDATE timer SET check = '1' WHERE id = '1'");
mysql_query("UPDATE timer SET timestamp = '$time' WHERE id = '1'");
}
elseif ($check=="1") {
$timenow = time();
$timecheck = $timenow - $timestamp;

echo $timenow;
echo $timecheck;

if ($timecheck >= "10") {
echo "bigger then 10, do what ever at the end of the countdown here";
mysql_query("UPDATE timer SET check = '0' WHERE id = '1'");
mysql_query("UPDATE timer SET timestamp = '' WHERE id = '1'");
}

}
}

?>

Yeah.
The problem, so fare it doesn't update the database with the new timestamp, and it just echo's the current timestamp
I haft to manually change the check from 1 to 0 to get the 2 things to change.
So that means, so fare none of the updates work from what I can tell.

Anything else you guys can see?

[johanafm]

Indent your code properly and use the php tags when posting php code, rather than code tags - like this. I've also added some comments

<?php

$db = mysql_pconnect("localhost","root",""); 
mysql_select_db("test",$db);

$time = time();
# Don't use *. Specify the columns used
# SELECT id, timer, check
$result = mysql_query("SELECT * FROM timer");

while($row = mysql_fetch_array($result))
{
	$timestamp = $row['timestamp'];
	$check = $row['check'];

# Why "0" rather than 0? Not that it really matters unless you do $check === 0
if ($check=="0")
{
	# Why two queries rather than 1?
	# Why '1' rather than 1?
	#	... `check`=1 AND timestamp='$time'
	mysql_query("UPDATE timer SET check = '1' WHERE id = '1'");
	mysql_query("UPDATE timer SET timestamp = '$time' WHERE id = '1'");
}
elseif ($check=="1")
{
	$timenow = time();
	$timecheck = $timenow - $timestamp;

	echo $timenow;
	echo $timecheck;

	if ($timecheck >= "10")
	{
		echo "bigger then 10, do what ever at the end of the countdown here";
		mysql_query("UPDATE timer SET check = '0' WHERE id = '1'");
		mysql_query("UPDATE timer SET timestamp = '' WHERE id = '1'");
	}
}
}

?>

If you dealt with error messages on failure you'd probably get a better idea about what's going on. For example unquoted use of reserved words.

if (!mysql_query(...))
{
	# something went wrong
	echo mysql_errno() . ': ' . mysql_error();
	# exit? recover? inform user?
}
else
{
	# query ok...
}

Do you have an entry with id=1 in the db?
Why do you retrieve all rows, while only updating the one where id=1?

[thatshanekid]

johanafm;10971840 wrote:

Indent your code properly and use the php tags when posting php code, rather than code tags - like this. I've also added some comments

<?php

$db = mysql_pconnect("localhost","root",""); 
mysql_select_db("test",$db);

$time = time();
# Don't use *. Specify the columns used
# SELECT id, timer, check
$result = mysql_query("SELECT * FROM timer");

while($row = mysql_fetch_array($result))
{
	$timestamp = $row['timestamp'];
	$check = $row['check'];

# Why "0" rather than 0? Not that it really matters unless you do $check === 0
if ($check=="0")
{
	# Why two queries rather than 1?
	# Why '1' rather than 1?
	#	... `check`=1 AND timestamp='$time'
	mysql_query("UPDATE timer SET check = '1' WHERE id = '1'");
	mysql_query("UPDATE timer SET timestamp = '$time' WHERE id = '1'");
}
elseif ($check=="1")
{
	$timenow = time();
	$timecheck = $timenow - $timestamp;

	echo $timenow;
	echo $timecheck;

	if ($timecheck >= "10")
	{
		echo "bigger then 10, do what ever at the end of the countdown here";
		mysql_query("UPDATE timer SET check = '0' WHERE id = '1'");
		mysql_query("UPDATE timer SET timestamp = '' WHERE id = '1'");
	}
}
}

?>

If you dealt with error messages on failure you'd probably get a better idea about what's going on. For example unquoted use of reserved words.

if (!mysql_query(...))
{
	# something went wrong
	echo mysql_errno() . ': ' . mysql_error();
	# exit? recover? inform user?
}
else
{
	# query ok...
}

Do you have an entry with id=1 in the db?
Why do you retrieve all rows, while only updating the one where id=1?

Cause there is only supposed to be 1 in the database, and for some reason I thought

UPDATE timer SET check = 1

just like that without selecting a certain Id wouldn't work.
There is only 1 entry in the database, and that is what is supposed to be like. It is just supposed to be updated.

I used * cause I did want to select everything from the database so I can get rows from the loop.

But here is the updated code from your notes:

<?php

$db = mysql_pconnect("localhost","root","");
mysql_select_db("test",$db);

$time = time();
$result = mysql_query("SELECT * FROM timer");

while($row = mysql_fetch_array($result))
{
    $timestamp = $row['timestamp'];
    $check = $row['check'];

if ($check==0)
{
    mysql_query("UPDATE timer SET check = 1 AND timestamp='$time' WHERE id = 1");
}
elseif ($check==1)
{
    $timenow = time();
    $timecheck = $timenow - $timestamp;

    echo $timenow;
    echo $timecheck;

    if ($timecheck >= 10)
    {
        echo "bigger then 10, do what ever at the end of the countdown here";
        mysql_query("UPDATE timer SET check = 0 AND timestamp = 0 WHERE id = 1");
    }
}
}

?> 

This still doesn't work.

[thatshanekid]

O I also didn't add the sql error cause it doesn't cause errors, just doesn't work the right way.

[bradgrafelman]

thatshanekid;10971841 wrote:

Cause there is only supposed to be 1 in the database, and for some reason I thought

UPDATE timer SET check = 1

just like that without selecting a certain Id wouldn't work.

Executing an UPDATE query without a WHERE clause won't cause any errors; if there are any rows in the table(s), they will all be updated.

thatshanekid;10971841 wrote:

I used cause I did want to select everything from the database so I can get rows from the loop.

As a matter of good practice, you should only SELECT columns from which you actually need data (and you don't actually need data from every column; even if you did, it's still better to name the columns rather than use '').

But here is the updated code from your notes:

thatshanekid;10971845 wrote:

O I also didn't add the sql error cause it doesn't cause errors, just doesn't work the right way.

So your reasoning for not adding error checking is: It's not broken, it just doesn't work. Is that right? 😉 You should always add error handling code - whether it works (at the moment) or not.

Now, as for your actual application, I would personally do it differently. First off, I would store the datetime value as a DATETIME column (it only makes sense, after all). Second, I would let MySQL handle the math, e.g.

SELECT
  check,
  TIMESTAMPDIFF(SECOND, `timestamp`, NOW()) AS timeDiff
FROM timer
LIMIT 1

Retrieve the result (no need for a loop if you only expect one row, by the way) and do something like:

if($row['check'] == "1" && $row['timeDiff'] >= TIME_THRESHOLD)

(where TIME_THRESHOLD is however many seconds you want the threshold to be.

Also note that when updating the record, you could simply use the 'NOW()' MySQL function to store the current date and time.