camparing date and time in php

liomon41 · June 12, 2012

I'm working on a program that allows a user to access a particular page via href at a particular date and time... the idea is if the date given is reached and the current time is 1 hour prior to the time given, the link to access the page is disabled...so basically the link is active until 1 hour prior to the given time. I'm having issues comparing the date and time to make this happen... The code below shows what i have done so far and offcourse its working as it should... Need help with this you guys... Thanks alot....

<?php

$query = "select * from fixtures group by date having count(*) > 1";

$result = mysql_query($query);

while($row = mysql_fetch_array($result))

{

$date= $row['date'];

$oDate = strtotime($date);

$sDate = date("D M j Y",$oDate);

?>

<?php

$query2 = "select * from fixtures where date = '$date'";

$result2 = mysql_query($query2);

while($row2 = mysql_fetch_array($result2))

{

$first = $row2['firstTeam'];

$second = $row2['secondTeam'];

$time = $row2['time'];

$gameid = $row2['gameId'];

$date_now = date("Y-m-d");

$time_now = gmdate("H:i:s");

$time2 = strtotime($time);

$real_time = date("H:i", $time2);

$timeMinus1Hour = gmdate("H:i", strtotime("-1 hour", strtotime($time)));

if( $date <= $date_now && $time_now <= $timeMinus1Hour) {

?>

$('#fixtures').click(function (){

return false;

});

</script>

<td width="56" align="left" style="color:#709b52;"><a href="" class="result_hover">results</a></td>

<td width="182" align="right"><a href="forecast.php?first1=<?php echo $first; ?>&second1=<?php echo $second; ?>&game_id=<?php echo $gameid; ?>" id="fixtures"> <?php echo $first; ?></a></td>

<td width="-31" align="center"><a href="forecast.php?first1=<?php echo $first; ?>&second1=<?php echo $second; ?>&game_id=<?php echo $gameid; ?>" id="fixtures">vs</a></td>

<td width="-12" align="left"><a href="forecast.php?first1=<?php echo $first; ?>&second1=<?php echo $second; ?>&game_id=<?php echo $gameid; ?>" id="fixtures"><?php echo $second; ?></a></td>

</tr>

<?php

}

?>

</table>

liomon41 · June 12, 2012

really sorry for posting multiple entries earlier on .... had some network issues... really didn't mean to ...

twistedvengeance · June 12, 2012

<table width="525" border="0" cellpadding="40" height="63" >
          <?php 

		$query = "select * from fixtures group by  date having count(*) > 1";
		$result = mysql_query($query);


	while($row = mysql_fetch_array($result))
	{
		$date= $row['date'];
		$oDate = strtotime($date);
		$sDate = date("D M j Y",$oDate);


	?>
	<tr><td colspan="8" align="left" class="td"><b><?php echo $sDate; ?></b></td></tr>

	<?php

		$query2 = "select * from fixtures where date = '$date'";

		$result2 = mysql_query($query2);

		while($row2 = mysql_fetch_array($result2)) 
		{

			$first = $row2['firstTeam'];

			$second = $row2['secondTeam'];

			$time = $row2['time'];

			$gameid = $row2['gameId'];

			$date_now = date("Y-m-d");

			$time_now = gmdate("H:i:s");

			$time2 = strtotime($time);

			$real_time = date("H:i", $time2);

			$timeMinus1Hour = gmdate("H:i", strtotime("-1 hour", strtotime($time)));

			if( $date <= $date_now && $time_now <= $timeMinus1Hour) {

			?>
                
                	<script type="text/javascript">



					$('#fixtures').click(function (){

						return false;

					});





				</script>
      
  <tr class="big">
  
  	
  
  	<td width="56" align="left" style="color:#709b52;"><a href="" class="result_hover">results</a></td>
    
    <td width="182" align="right"><a href="forecast.php?first1=<?php echo $first; ?>&second1=<?php echo $second; ?>&game_id=<?php echo $gameid; ?>" id="fixtures"> <?php echo $first; ?></a></td>
    
    <td width="-31" align="center"><a href="forecast.php?first1=<?php echo $first; ?>&second1=<?php echo $second; ?>&game_id=<?php echo $gameid; ?>" id="fixtures">vs</a></td>
    
   <td width="-12" align="left"><a href="forecast.php?first1=<?php echo $first; ?>&second1=<?php echo $second; ?>&game_id=<?php echo $gameid; ?>" id="fixtures"><?php echo $second; ?></a></td>
   
   
   <td><a href="forecast.php" id="fixtures"><?php echo $real_time; ?></a></td>
    
  </tr>
  

<?php 

		}

	}

	}

?>

Reposting for code syntax

twistedvengeance · June 12, 2012

You have too many spaces in your query.

liomon41 · June 12, 2012

is that going to be a problem ??/

Barand · June 12, 2012

You have too many spaces in your query.

Whitespace in queries is not a problem, and usually desirable to make it more readable

Jessica · June 12, 2012

You posted that the code is working as it should, so what is the problem?

liomon41 · June 12, 2012

the problem is the link are still active .... even when the time is reached... just can't seem to disable them... I think it has something to do with the comparison i used..

liomon41 · June 12, 2012

is there another way of comparing currenttime with $$timeMinus1Hour and currentdate with the date from the database.. ?

Psycho · June 12, 2012

To start, you should not run queries in loops. You are running one query and then using the results of that query to run other queries. I see that a lot, but in this case I'm a little confused by your approach. You first run this query

$query = "select * from fixtures group by  date having count(*) > 1";

Then you use the 'date' value from those results to query the records from the same table having the date

$query2 = "select * from fixtures where date = '$date'";

Either I am missing something or that is completely unnecessary. You should just query the table for all the records, since that is what you were getting anyway. I think you did that to display a date header for each group, but there is a better way to do that.

As for disabling the links you can either do the data comparison in PHP or in the query itself. I would do it in the query, but either will work. Below is a complete rewrite that should do what you want, but a couple of notes:

1. Disabling a link does not prevent someone from typing the URL into their browser or picking it from their history in this case. So, on the forcast.php page you need to verify the record is still valid before displaying it.

2. You are setting the same ID for the three links for each record. In a valid HTML page you cannot use the same ID more than once.

3. The parameters in the URLs should be using the team IDs, not the names. I didn't make a change for that since you would have to modify the forcast.php page first to use the IDs.

4. Using JavaScript to disable the links is a bad choice since you are already processing the data in PHP. I would just create a function to output each link using the different text/id based upon the date/time of the record. I created a dynamic 'active' value within the query

I was not able to test this as I don't have your database, so there might be some minor errors to debug

<?php
    //Function to create a link or not based upon 'active' status
    function forecastLink($text, $url, $id, $clickable)
    {
        if(!$clickable) { return $text; }
        return "<a href='{$url}' id='{$id}'>{$text}</a>";

    }

    //Create var to hold output
    $output = '';

    //Create/run query for all records ordered by date
    $query = "SELECT firstTeam, secondTeam, gameId, date, time,
                     IF(SUBTIME(TIMESTAMP(`date`, `time`), '1:0:0') > NOW(), 1, 0) as active
              FROM fixtures
              ORDER BY date";
    $result = mysql_query($query);

    //Create flag variable to keep track of date
    $currentDate = false;
    while($row = mysql_fetch_array($result))
    {
        //Convert date to common format
        $sDate = date("D M j Y", strtotime($row['date']));

        //Check if this is a new date
        if($currentDate != $row['date'])
        {
            //Display date header
            $currentDate = $row['date'];
            $output .= "<tr><td colspan='8' align='left' class='td'><b>{$currentDate}</b></td></tr>";
        }

        //Extract and process the data for display
        $first  = htmlspecialchars($row['firstTeam']);
        $second = htmlspecialchars($row['secondTeam']);
        $gameid = $row['gameId'];
        $linkActive = $row['active']
        $real_time  = date("H:i", strtotime($row['time']));

        //Display record
        $url = "forecast.php?first1={$first}&second1={$second}&game_id={$gameid}";
        $output .= "<tr class='big'>\n";
        $output .= "<td width='56' align='left' style='color:#709b52;'><a href='' class='result_hover'>results</a></td>\n";
        $output .= "<td width='182' align='right'>" . forecastLink($first, $url, 'fixtures_f', $linkActive) . "</td>\n";
        $output .= "<td width='-31' align='center'>" . forecastLink('vs', $url, 'fixtures_v', $linkActive) . "</td>\n";
        $output .= "<td width='-12' align='left'>" . forecastLink($second, $url, 'fixtures_s', $linkActive) . "</td>\n";
        $output .= "<td><a href='forecast.php' id='fixtures_t'>{$real_time}</a></td>\n";
        $output .= "</tr>\n";
    }
?>

<table width="525" border="0" cellpadding="40" height="63" >
<?php echo $output; ?>
</table>

liomon41 · June 12, 2012

The reason i have two queries is cos there are two with the same date but different times so the first query select the date from the fixtures table, displays it then the second query selects the records that applies to that date..... I uploaded a copy of my fixtures table... would really appreciate it if you took a look at it, maybe you'd get what im trying to achieve... Thanks for your replies so ..

18587_.zip

Barand · June 12, 2012

The reason i have two queries is cos there are two with the same date but different times so the first query select the date from the fixtures table, displays it then the second query selects the records that applies to that date

Did you actually look at Psycho's code?

liomon41 · June 12, 2012

yeah i did ... im working on it right now... thanks..

Psycho · June 12, 2012

The reason i have two queries is cos there are two with the same date but different times so the first query select the date from the fixtures table, displays it then the second query selects the records that applies to that date..... I uploaded a copy of my fixtures table... would really appreciate it if you took a look at it, maybe you'd get what im trying to achieve... Thanks for your replies so ..

No, I did understand what you were trying to achieve and I even stated why I thought you did what you did. But, as I stated, you were doing it wrong. You only need one query. You can use the records to dynamically determine when to display a new date when processing the query results! NEVER run queries in loops.

There were only a couple minor error in the code I provided. Other than that it worked flawlessly based upon your criteria. However, all the records in the provided data were in the past. So they were all displayed without links because, per your requirements, once the time reaches one hour prior to the date/time of the event the links should no longer be displayed. Once I added a record to be at least 1 hour in the future the links were displayed. At 61 minutes in the future there are links, at 60 minutes there are no links.

Although I would question why you have a "results" link displayed for every record since some events haven't occurred. but, I think I've given you enough to work with that you should be able to make any changes needed.

Here is the code with the couple of problems fixed.

<?php
    //Function to create a link or not based upon 'active' status
    function forecastLink($text, $url, $id, $clickable)
    {
        if(!$clickable) { return $text; }
        return "<a href='{$url}' id='{$id}'>{$text}</a>";

    }

    //Create var to hold output
    $output = '';

    //Create/run query for all records ordered by date
    $query = "SELECT firstTeam, secondTeam, gameId, date, time,
                     IF(SUBTIME(TIMESTAMP(`date`, `time`), '1:0:0') > NOW(), 1, 0) as active
              FROM fixtures
              ORDER BY date, time";
    $result = mysql_query($query);

    //Create flag variable to keep track of date
    $currentDate = false;
    while($row = mysql_fetch_array($result))
    {
        //Convert date to common format
        $displayDate = date("D, M j, Y", strtotime($row['date']));

        //Check if this is a new date
        if($currentDate != $displayDate)
        {
            //Display date header
            $currentDate = $displayDate;
            $output .= "<tr><td colspan='8' align='left' class='td'><b>{$currentDate}</b></td></tr>";
        }

        //Extract and process the data for display
        $first  = htmlspecialchars($row['firstTeam']);
        $second = htmlspecialchars($row['secondTeam']);
        $gameid = $row['gameId'];
        $linkActive = $row['active'];
        $real_time  = date("H:i", strtotime($row['time']));

        //Display record
        $url = "forecast.php?first1={$first}&second1={$second}&game_id={$gameid}";
        $output .= "<tr class='big'>\n";
        $output .= "<td width='56' align='left' style='color:#709b52;'><a href='' class='result_hover'>results</a></td>\n";
        $output .= "<td width='182' align='right'>" . forecastLink($first, $url, 'fixtures_f', $linkActive) . "</td>\n";
        $output .= "<td width='-31' align='center'>" . forecastLink('vs', $url, 'fixtures_v', $linkActive) . "</td>\n";
        $output .= "<td width='-12' align='left'>" . forecastLink($second, $url, 'fixtures_s', $linkActive) . "</td>\n";
        $output .= "<td>" . forecastLink($real_time, $url, 'fixtures_t', $linkActive) . "</td>\n";
        $output .= "</tr>\n";
    }
?>

<table width="525" border="1" cellpadding="40" height="63" >
<?php echo $output; ?>
</table>

liomon41 · June 13, 2012

everything checks out well but what i forgot to tell you was that the time in the database must be in GMT... really sorry i omiitted that part out....

Psycho · June 13, 2012

everything checks out well but what i forgot to tell you was that the time in the database must be in GMT... really sorry i omiitted that part out....

Sounds like a topic for a different post

liomon41 · June 13, 2012

what do you mean a different post???

Psycho · June 13, 2012

This post was about a problem with getting records out of the database and displaying them in a specific way. That problem has been resolved. You have a new problem you need help in solving, thus it should be a different post.

1. Adding additional problems to a post do not help others when they are searching the forums for solutions to similar problems.

2. You don't get a dedicated post to solve all your problems from beginning to end.

liomon41 · June 13, 2012

the $time from the database is in GMT, so whatever timezone you are in, you are checking if your current time is (-1 GMT($time)) ... I did this in the very first code i sent to you....

$timeMinus1Hour = gmdate("H:i", strtotime("-1 hour", strtotime($time)));

Sign In

camparing date and time in php

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information