Comparing times in MySQL

[BelowZero]

Well I'm hoping someone will have some fun with this.

My database has a list of times for a given date, and a schedule for each time. I'm trying to change the input backgrounds for rows between 2 different times. Like this:

 

8:00  enter some data

8:10  enter some data

8:20  enter some data

etc.

What I want is to change the background color of any input field between say 9:00 and 10:00, but leave everything else white.

I can change the color, just can't figure out how to compare the times.

 

Here's part of the code:

while($row = mysql_fetch_array( $result )) 
{


$opentimeID = date("g:i",strtotime(1530)); //not sure if these statements are correct. 
$closetimeID = date("g:i",strtotime(1730));
if (strtotime($row['Time']) //...not sure how to finish this statement to compare the time in the database row to $opentime and $closetime.


{
echo "<tr><td>";
echo date("g:i ", strtotime($row[Time]));
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player1]' name='player1[$i]' 
style ='background-color: #FFFF66;'>";
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player2]' name='player2[$i]'
style ='background-color: #FFFF66;'>"; 
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player3]' name='player3[$i]'
style ='background-color: #FFFF66;'>"; 
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player4]' name='player4[$i]'
style ='background-color: #FFFF66;'>";
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player5]' name='player5[$i]'
style ='background-color: #FFFF66;'>";
}
else
{
echo "<tr>";
echo "<td>";
echo date("g:i ", strtotime($row[Time]));
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player1]' name='player1[$i]'>";
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player2]' name='player2[$i]'>";
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player3]' name='player3[$i]'>";
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player4]' name='player4[$i]'>";
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player5]' name='player5[$i]'>";
}

Thanks for any advice!

[Pikachu2000]

You can easily do this in the query. This query string assumes your times are stored in a DATETIME type field (YYYY-MM-DD HH:mm:ss)

 

SELECT field1, field2, Time, IF( TIME(Time) BETWEEN '15:30:00' AND '17:30:00', 'FFFF66', '000000') AS color FROM table

 

The the color value will be in the $row['color'] array element, or at least it should be judging by the code you posted, so then you can just echo it in the style attribute of the <input> tag. That would eliminate the PHP conditional entirely.

[BelowZero]

Thanks Pikachu2000.

 

Now I'm assuming that I can assign these times to variables in the select statement? The times will change on any given day.

Will this work?

SELECT field1, field2, Time, IF( TIME(Time) BETWEEN '$opentime' AND '$closetime', 'FFFF66', '000000') AS color FROM table

 

[Pikachu2000]

That shouldn't be a problem at all, as long as the format is right.

[BelowZero]

Can you take a look again? The whole table still shows up with a white background...

Here is the complete code:

<FORM Method = "POST" action ="insert_tee_times.php">

<?php

include("opendatabase.php"); 

$dateID = $_POST[teetimedate];

$result = mysql_query("
SELECT Date, Player1,Player2,Player3,Player4,Player5,Time, IF( TIME(Time) BETWEEN '15:30:00' AND '17:30:00','FFFF66','000000') AS color
FROM Daysheets
WHERE Date ='$dateID'"); 

echo "<h1>";
echo date("l, F d, Y", strtotime($dateID));
echo "</h1>";
echo "Please enter names in the respective times and press 'Submit Tee Times' at the bottom of the page. Use the TAB key to move from cell to cell. Thank you.";

echo "<table align=center width=700 border=1>\n";
echo "<th>Time</th><th>Player 1</th><th>Player 2</th><th>Player 3</th><th>Player 4</th><th>Player 5</th>";

$i=0;

while($row = mysql_fetch_array( $result )) 
{
echo "<tr>";
echo "<td>";
echo date("g:i ", strtotime($row[Time]));
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player1]' name='player1[$i]'>";
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player2]' name='player2[$i]'>";
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player3]' name='player3[$i]'>";
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player4]' name='player4[$i]'>";
echo "</td><td>";
echo "<input type='text' size='20' value= '$row[Player5]' name='player5[$i]'>";

$dateID = $row['Date'];
$timeID = $row['Time'];
echo "<input type='hidden' name='date[$i]' value='$dateID'>";
echo "<input type='hidden' name='time[$i]' value='$timeID'>";
echo "</td></tr>";
$i++;
}
echo "</table>";

mysql_close($con);

?>
<br /><br /> 

<input type="Submit" name="Submit" value="Submit Tee Times">
</FORM>	

Sorry my programming skills are still in the beginner stage at best! Thanks.

[Pikachu2000]

Looks like you forgot to add the style ='background-color:#{$row['color']};' attribute to the <input> tag

[BelowZero]

Oops...

Now it works as expected.

Many thanks!