changing timestamp in mysql with php

KillerBeeZ · May 24, 2012

I am creating a racing game and hit a snag. Been working on this all day (and most of the night)

I need to set up the races so that players can enter them and race, then when the races is over it resets it with a random 10 to 30 minutes til the next race

I have tried all sorts of things to get this to work. I have gotten closer and closer but here is where I stand now.

lets say a race just ended, instead of resetting the time and adding 10 to 30 minutes, it sets everything to 0, it makes me think the variable is not valid and it tries to enter the date as a string or something. this is what I've come up with after trying it at least 50 different ways (the error is in the IF statement)

Let me know what else I need to post, and thank you very much in advance for any help you can give me, both to fix this problem, and in helping me understand it

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Car Entered</title>
<style type="text/css">
<!--
.style1 {color: #003300}
.style2 {color: #990000}
-->
</style>
</head>

<body>
<?php
	include 'connect.php';
	mysql_connect("localhost",$user,$password);
	@mysql_select_db($database) or die( "Unable to select database");

	$query="SELECT * FROM races";
	$result=mysql_query($query);
	$num=mysql_num_rows($result);

	$query2="SELECT * FROM account";
	$result2=mysql_query($query2);
	$num2=mysql_num_rows($result2);

	$query3="SELECT * FROM tracks";
	$result3=mysql_query($query3);
	$num3=mysql_num_rows($result3);	

	$i=0;
	$datetimeNOW = date("Y-m-d H:i:s ");
?>
<span class="style1">Current date/Time:</span> <? echo $datetimeNOW; ?>
<table width="100%"border="1" cellspacing="2" cellpadding="2">
  <tr>
    <th><span class="style2">Track</span></th>
    <th><span class="style2 style2">Race Started? </span></th>
    <th><span class="style2 style2">Race Start Time </span></th>
  </tr>
  <p>
<?
	while ($i < $num)
	{
		$RaceStart = mysql_result($result,$i,"start");
		$Track = mysql_result($result,$i,"track");
		$i++;
		if ($RaceStart > $datetimeNOW)
		{
			?>
			  <tr>
				<th><? echo $Track; ?></th>
			    <th><? echo "not started yet"; ?></th>
			    <th><? echo $RaceStart; ?></th>
			  </tr>
			<?
		}
		else
		{
			?>
			  <tr>
			    <th><? echo $Track; ?></th>
			    <th><? echo "Race Started"; ?></th>
			    <th><? echo $RaceStart; ?></th>
			  </tr>
			<?
			if ($Track == "Monthly Test" )
			{
			// 1 exact month = 43830
				$addrandminutes = rand(43200, 46080); // 30 to 32 days
				$newdate = strtotime('$datetimeNOW + $addrandminutes minutes');
				$queryreset = "UPDATE races SET start = '$newdate' WHERE track = '$Track'";
				mysql_query($queryreset);
			}
			else if ($Track == "Weekly test" )
			{
			// 1 week = 10080
				$addrandminutes = rand(9360, 10800); // 6.5 to 7.5 days
				$newdate = strtotime('$datetimeNOW + $addrandminutes minutes');
				$queryreset = "UPDATE races SET start = '$newdate' WHERE track = '$Track'";
				mysql_query($queryreset);
			}
			else
			{
				$addrandminutes = rand(10, 30);
				$newdate = strtotime('$datetimeNOW + $addrandminutes minutes'); // 10 to 20 minutes
				$queryreset = "UPDATE races SET start = '$newdate' WHERE track = '$Track'";
				mysql_query($queryreset);
			}
		}
	}
	mysql_close();
?>
</table>
</body>
</html>

This is the SQL setup, in case you need to look at that too.

1 	start 	timestamp 		on update CURRENT_TIMESTAMP 	No 	CURRENT_TIMESTAMP 	ON UPDATE CURRENT_TIMESTAMP 	Change Change 	Drop Drop 	More Show more actions
2 	track 	varchar(128) 	latin1_general_ci 		No 	None 		Change Change 	Drop Drop 	More Show more actions
3 	acnt 	varchar(128) 	latin1_general_ci 		No 	None 		Change Change 	Drop Drop 	More Show more actions
4 	car 	varchar(128) 	latin1_general_ci 		No 	None

and the races entries as they ended up

Full texts 	start 	track 	acnt 	car
Edit Edit 	Edit Inline Edit 	Copy Copy 	Delete Delete 	0000-00-00 00:00:00 	test 		
Edit Edit 	Edit Inline Edit 	Copy Copy 	Delete Delete 	0000-00-00 00:00:00 	test2 		
Edit Edit 	Edit Inline Edit 	Copy Copy 	Delete Delete 	0000-00-00 00:00:00 	test3 		
Edit Edit 	Edit Inline Edit 	Copy Copy 	Delete Delete 	0000-00-00 00:00:00 	Monthly Test 		
Edit Edit 	Edit Inline Edit 	Copy Copy 	Delete Delete 	0000-00-00 00:00:00 	Weekly test

Barand · May 24, 2012

$newdate = strtotime('$datetimeNOW + $addrandminutes minutes');

You need double quotes for strings containing variables

KillerBeeZ · May 25, 2012

thank you for helping, I tried that but it is doing the same thing, it sets the date in sql to all 0's

Track 	Race Started? 	Race Start Time
test 	Race Started 	0000-00-00 00:00:00
test2 	Race Started 	0000-00-00 00:00:00
test3 	not started yet 	2012-05-25 00:07:00

new code...

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Car Entered</title>
<style type="text/css">
<!--
.style1 {color: #003300}
.style2 {color: #990000}
-->
</style>
</head>

<body>
<?php
	include '../admin/php/connect.php';
	mysql_connect("localhost",$user,$password);
	@mysql_select_db($database) or die( "Unable to select database");

	$query="SELECT * FROM races";
	$result=mysql_query($query);
	$num=mysql_num_rows($result);

	$query2="SELECT * FROM account";
	$result2=mysql_query($query2);
	$num2=mysql_num_rows($result2);

	$query3="SELECT * FROM tracks";
	$result3=mysql_query($query3);
	$num3=mysql_num_rows($result3);	

	$i=0;
	$datetimeNOW = date("Y-m-d H:i:s ");
?>
<span class="style1">Current date/Time:</span> <? echo $datetimeNOW; ?>
<table width="100%"border="1" cellspacing="2" cellpadding="2">
  <tr>
    <th><span class="style2">Track</span></th>
    <th><span class="style2 style2">Race Started? </span></th>
    <th><span class="style2 style2">Race Start Time </span></th>
  </tr>
  <p>
<?
	while ($i < $num)
	{
		$RaceStart = mysql_result($result,$i,"start");
		$Track = mysql_result($result,$i,"track");
		$i++;
		if ($RaceStart > $datetimeNOW)
		{
			?>
			  <tr>
				<th><? echo $Track; ?></th>
			    <th><? echo "not started yet"; ?></th>
			    <th><? echo $RaceStart; ?></th>
			  </tr>
			<?
		}
		else
		{
			?>
			  <tr>
			    <th><? echo $Track; ?></th>
			    <th><? echo "Race Started"; ?></th>
			    <th><? echo $RaceStart; ?></th>
			  </tr>
			<?
			if ($Track == "Monthly Test" )
			{
			// 1 exact month = 43830
				$addrandminutes = rand(43200, 46080); // 30 to 32 days
				$newdate = strtotime("$datetimeNOW + $addrandminutes minutes");
				$queryreset = "UPDATE races SET start = '$newdate' WHERE track = '$Track'";
				mysql_query($queryreset);
			}
			else if ($Track == "Weekly test" )
			{
			// 1 week = 10080
				$addrandminutes = rand(9360, 10800); // 6.5 to 7.5 days
				$newdate = strtotime("$datetimeNOW + $addrandminutes minutes");
				$queryreset = "UPDATE races SET start = '$newdate' WHERE track = '$Track'";
				mysql_query($queryreset);
			}
			else
			{
				$addrandminutes = rand(10, 30);
				$newdate = strtotime("$datetimeNOW + $addrandminutes minutes"); // 10 to 20 minutes
				$queryreset = "UPDATE races SET start = '$newdate' WHERE track = '$Track'";
				mysql_query($queryreset);
			}
		}
	}
	mysql_close();
?>
</table>
</body>
</html>

Barand · May 25, 2012

try doing the date calc in the sql

$queryreset = "UPDATE races SET start = NOW() + INTERVAL $addrandminutes   MINUTE WHERE track = '$Track' ";

KillerBeeZ · May 25, 2012

that did the trick, thank you so much. I was unaware that you could do any calculations in sql, I thought it was just a database. I have much to learn.

I have just one more question...

After looking thoroughly on amazon for php/sql books it seems they all have some bad reviews for one reason or another. If you were to recommend a book on php and/or sql to someone with prior C# experience but little php and no sql knowledge, what book(s) would you recommend?

Barand · May 25, 2012

To give an idea of the power of sql regarding calculations

http://dev.mysql.com/doc/refman/5.6/en/functions.html

KillerBeeZ · May 26, 2012

thank you. I have found this board is a lot more helpful than other boards of this type.

Sign In

changing timestamp in mysql with php

Recommended Posts

KillerBeeZ

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

KillerBeeZ

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

KillerBeeZ

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

KillerBeeZ

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information