Timestamp Question

[son]

I have a calendar script with a small calendar to select the day which then shows the relevant entries for the selected day in one-day-calendar format (so I display two calendars in total, one for month selection and one for display of entered activities). I would like to show the days in small calendar with red background where date matches one or more entries of my activies table (date stored in timestamp format), but am not sure how to compare relevant day in calendar with timestamp entry in table that holds activities. The code is so far (in bold my bad first attempts for the comparison):// accept incoming URL parameter$timestamp = (isset($_GET['t'])) ? $_GET['t'] : time();// determine useful aspects of the requested monthlist($month, $day, $year) = explode('/', date('m/d/Y', $timestamp));$first_day_of_month = date('w', mktime(0, 0, 0, $month, 1, $year));$total_days = date('t', $timestamp);// output table headerob_start();echo '<table id="calendar">';echo '<tr id="calendar_header"><th colspan="7">';echo '<a href="' . htmlspecialchars($_SERVER['PHP_SELF']) . '?t=' . strtotime('-1 month', $timestamp) . '"><</a>  ';echo date('F', $timestamp) . ' ' . $year;echo '  <a href="' . htmlspecialchars($_SERVER['PHP_SELF']) . '?t=' . strtotime('+1 month', $timestamp) . '">></a>';echo '</th></tr>';echo '<tr><th>Sun</th><th>Mon</th><th>Tue</th><th>Wed</th><th>Thu</th>' . '<th>Fri</th><th>Sat</th></tr>';// output date cells$current = 1;while ($current <= $total_days){ echo '<tr class="calendar_dates">'; for ($i = 0; $i < 7; $i++) { if (($current == 1 && $i < $first_day_of_month) || ($current > $total_days)) { echo '<td class="empty"> </td>'; continue; }$qd = 'SELECT EVENT_NAME, UNIX_TIMESTAMP(EVENT_TSTAMP) AS EVENT_TSTAMP FROM %scalendar WHERE EVENT_TSTAMP = $current',$rd = mysql_query($qd, $GLOBALS['DB']); echo '<td'; if ($current == EVENT_TSTAMP) echo ' style="background-color:red"; echo '>'; echo $current; echo '</td>'; $current++; } echo '</tr>';}echo '</table>';$GLOBALS['TEMPLATE']['content'] = ob_get_clean();How do I obtain the day of timestamp?Son

[justsomeguy]

First off, you're not using the database right. You're getting the result from the database but you're not getting the row. Look at the reference for mysql_fetch_assoc or mysql_fetch_array.Second, it looks like you're trying to compare the $current variable with the EVENT_TSTAMP column. But in your query, your WHERE clause says to only select rows where EVENT_TSTAMP equals $current. So the if statement is always going to be true, there's no reason to check for it.If you're trying to use Unix timestamps with PHP, look into the getdate function. mktime might also be useful for this.

[son]

First off, you're not using the database right. You're getting the result from the database but you're not getting the row. Look at the reference for mysql_fetch_assoc or mysql_fetch_array.Second, it looks like you're trying to compare the $current variable with the EVENT_TSTAMP column. But in your query, your WHERE clause says to only select rows where EVENT_TSTAMP equals $current. So the if statement is always going to be true, there's no reason to check for it.If you're trying to use Unix timestamps with PHP, look into the getdate function. mktime might also be useful for this.

Tried to remedy, but just cannot get work. The code now is (relevant bits in bold):

// display month calendarecho '<table id="calendar">';echo '<tr id="calendar_header"><th colspan="7">';echo '<a href="' . htmlspecialchars($_SERVER['PHP_SELF']) . '?t=' .    strtotime('-1 month', $timestamp) . '"><</a>  ';echo date('F', $timestamp) . ' ' . $year;echo '  <a href="' . htmlspecialchars($_SERVER['PHP_SELF']) . '?t=' .    strtotime('+1 month', $timestamp) . '">></a>';echo '</th></tr>';echo '<tr><th>Sun</th><th>Mon</th><th>Tue</th><th>Wed</th><th>Thu</th>' .    '<th>Fri</th><th>Sat</th></tr>';$current = 1;while ($current <= $total_days){    echo '<tr class="calendar_dates">';    for ($i = 0; $i < 7; $i++)    {        if (($current == 1 && $i < $first_day_of_month) ||            ($current > $total_days))        {            echo '<td class="empty"> </td>';            continue;        }        echo '<td';		$qd = 'SELECT UNIX_TIMESTAMP(EVENT_TSTAMP) AS EVENT_TSTAMP FROM %scalendar';		$rd = mysql_query($qd) or die(mysql_error());		while($row = mysql_fetch_array($rd)){ 		$eventTime = $row['EVENT_TSTAMP'];		$curTime = mktime(0, 0, 0, $month, $current, $year);		if ($eventTime == $curTime) echo ' style="background-color:red"';		}		echo '>';		echo '<a href="' . htmlspecialchars($_SERVER['PHP_SELF']) .            '?t=' . mktime(0, 0, 0, $month, $current, $year) . '">' .            $current . '</a></td>';        $current++;    }    echo '</tr>';}echo '</table>';var_dump ($curTime);

Would be great if you could have another look...Son

[justsomeguy]

Does the timestamp in the database include time, or is it just date?

[son]

Does the timestamp in the database include time, or is it just date?

It includes both, an example is:2009-05-13 01:00:00Son

[justsomeguy]

The timestamp you're comparing it with is just a date, so it's set to midnight. It's only going to work if the timestamp in the database is also set to midnight. If you want to find all times in a certain day, it's easiest to create one timestamp at 00:00:00 and one at 23:59:59 and check if your database timestamp is between those.

[son]

The timestamp you're comparing it with is just a date, so it's set to midnight. It's only going to work if the timestamp in the database is also set to midnight. If you want to find all times in a certain day, it's easiest to create one timestamp at 00:00:00 and one at 23:59:59 and check if your database timestamp is between those.

But am only interested in date. All cells in table of days of month that also have an activity on same day should be highlighted. Did not understand what you said...Son

[justsomeguy]

You're comparing dates. You have a timestamp in a table with both a date and time, and you convert it to a Unix timestamp. You generate a Unix timestamp for the current day at midnight. You are comparing the timestamp for the current day at midnight to the timestamps from the database, which are not at midnight. They aren't going to be equal, because "midnight" and "not midnight" are not the same thing. So, in order to find all of the timestamps in the current day, no matter what time they are for, you create one timestamp for the start of the day, and one for the end of the day, and check if the database timestamp is between those.

[son]

You're comparing dates. You have a timestamp in a table with both a date and time, and you convert it to a Unix timestamp. You generate a Unix timestamp for the current day at midnight. You are comparing the timestamp for the current day at midnight to the timestamps from the database, which are not at midnight. They aren't going to be equal, because "midnight" and "not midnight" are not the same thing. So, in order to find all of the timestamps in the current day, no matter what time they are for, you create one timestamp for the start of the day, and one for the end of the day, and check if the database timestamp is between those.

I tested:

echo '<td';		$qd = 'SELECT UNIX_TIMESTAMP(EVENT_TSTAMP) AS EVENT_TSTAMP FROM %scalendar';		$rd = mysql_query($qd) or die(mysql_error());		while($row = mysql_fetch_array($rd)){ 		$eventTime = $row['EVENT_TSTAMP'];		$curTime1 = mktime(0, 0, 0, $month, $current, $year);		$curTime2 = mktime(23, 59, 59, $month, $current, $year);		if ($eventTime < $curTime2 && $eventTime > $curTime1) echo ' style="background-color:red"';		echo '>';		echo '<a href="' . htmlspecialchars($_SERVER['PHP_SELF']) .            '?t=' . mktime(0, 0, 0, $month, $current, $year) . '">' .            $current . '</a></td>';

but am looking now at an empty page... Where am I going wrong?Son

[justsomeguy]

Make the comparisons less than or equal and greater than or equal instead of just less than and greater than. Also, is that the correct table name? For each row it might help to print the timestamp from the database and the 2 that you're comparing it with. Some implementations give the Unix timestamp in seconds, and some in milliseconds. PHP uses seconds, I assume that MySQL would also but maybe it uses milliseconds. Also, check the source of the HTML page to check if it's outputting HTML code that just isn't being rendered for some reason. Another thing, you have the closing > for the td inside of the while loop, if it finds more than one event on the same day it's going to repeat that code, it would print something like this:<td style="background-color:red">style="background-color:red">Actually the while loop is getting all events, so it's going to print 1 > and link for each event in the database, regardless of which day it's on. It will print one style attribute for each event on the day. You might want to generate the 2 timestamps before the query and just select the events within that range instead of selecting everything and checking the range later.

[son]

Make the comparisons less than or equal and greater than or equal instead of just less than and greater than. Also, is that the correct table name? For each row it might help to print the timestamp from the database and the 2 that you're comparing it with. Some implementations give the Unix timestamp in seconds, and some in milliseconds. PHP uses seconds, I assume that MySQL would also but maybe it uses milliseconds. Also, check the source of the HTML page to check if it's outputting HTML code that just isn't being rendered for some reason. Another thing, you have the closing > for the td inside of the while loop, if it finds more than one event on the same day it's going to repeat that code, it would print something like this:<td style="background-color:red">style="background-color:red">Actually the while loop is getting all events, so it's going to print 1 > and link for each event in the database, regardless of which day it's on. It will print one style attribute for each event on the day. You might want to generate the 2 timestamps before the query and just select the events within that range instead of selecting everything and checking the range later.

You are right, it prints out the red background several times. The table cell is now highlighted and all elements on page show up correctly. How could I make the style info only appear once (even if for one day there are several activities)? My code is now:echo '<td'; $curTime1 = mktime(0, 0, 0, $month, $current, $year); $curTime2 = mktime(23, 59, 59, $month, $current, $year); $qd = 'SELECT UNIX_TIMESTAMP(EVENT_TSTAMP) AS EVENT_TSTAMP FROM Metastar_calendar'; $rd = mysql_query($qd) or die(mysql_error()); while($row = mysql_fetch_array($rd)){ $eventTime = $row['EVENT_TSTAMP']; if ($eventTime <= $curTime2 && $eventTime >= $curTime1) echo ' style="background-color:red;"'; } echo '>';Appreciate your inputs,Son

[son]

I have tried now a different approach, but still without luck. The code so far is:

$curTime1 = mktime(0, 0, 0, $month, $current, $year);		$curTime2 = mktime(23, 59, 59, $month, $current, $year);		$qd = 'SELECT UNIX_TIMESTAMP(EVENT_TSTAMP) AS EVENT_TSTAMP FROM Metastar_calendar WHERE (UNIX_TIMESTAMP(EVENT_TSTAMP) <= $curTime2) AND (UNIX_TIMESTAMP(EVENT_TSTAMP) >= $curTime1)';		$rd = mysql_query($qd) or die(mysql_error());		if (mysql_num_rows($rd) > 0)		{        echo '<td style="background-color:#1c3b6a;">';				echo '<a href="' . htmlspecialchars($_SERVER['PHP_SELF']) .            '?t=' . mktime(0, 0, 0, $month, $current, $year) . '">' .            $current . '</a></td>';		}		else		{        echo '<td>';				echo '<a href="' . htmlspecialchars($_SERVER['PHP_SELF']) .            '?t=' . mktime(0, 0, 0, $month, $current, $year) . '">' .            $current . '</a></td>';		}

It complains "Unknown column '$curTime2' in 'where clause'" and other elements on page do not look the way they should look. The problem must be with the query syntax, but cannot see how I would do this different...Son

[Ingolme]

It complains "Unknown column '$curTime2' in 'where clause'" and other elements on page do not look the way they should look. The problem must be with the query syntax, but cannot see how I would do this different...Son

Variables within strings surrounded by single quotes are not parsed:

$x = 'world';echo "Hello $x!"; // Outputs "Hello world!"echo 'Hello $x!'; // Outputs "Hello $x!"

[son]

Variables within strings surrounded by single quotes are not parsed:

$x = 'world';echo "Hello $x!"; // Outputs "Hello world!"echo 'Hello $x!'; // Outputs "Hello $x!"

But I am having no quotes in my query:$qd = 'SELECT UNIX_TIMESTAMP(EVENT_TSTAMP) AS EVENT_TSTAMP FROM Metastar_calendar WHERE (UNIX_TIMESTAMP(EVENT_TSTAMP) <= $curTime2) AND (UNIX_TIMESTAMP(EVENT_TSTAMP) >= $curTime1)';????Son

[justsomeguy]

The string is surrounded by single quotes, not double quotes:$qd = 'SELECT ...';Also, to only print it once you can just use a flag to check if you've already printed it.

$shown = false;while (...){  if (!$shown)  {	$shown = true;	...// other stuff  }}

[son]

The string is surrounded by single quotes, not double quotes:$qd = 'SELECT ...';Also, to only print it once you can just use a flag to check if you've already printed it.

echo '<td';		$curTime1 = mktime(0, 0, 0, $month, $current, $year);		$curTime2 = mktime(23, 59, 59, $month, $current, $year);		$qd = "SELECT UNIX_TIMESTAMP(EVENT_TSTAMP) AS EVENT_TSTAMP FROM Metastar_calendar";		$rd = mysql_query($qd) or die(mysql_error());		while($row = mysql_fetch_array($rd)){ 		$eventTime = $row['EVENT_TSTAMP'];		$shown = FALSE;		if (!$shown)		{		if ($eventTime <= $curTime2 && $eventTime >= $curTime1 && !$shown) echo ' style="background-color:#1c3b6a;"';		$shown = TRUE;		}		}		echo '>';

still brings the same problem, it prints the background-color several times???Son

[justsomeguy]

That's because you set $shown to false every time through the loop. Initialize it before the loop starts, like my example showed.

[son]

That's because you set $shown to false every time through the loop. Initialize it before the loop starts, like my example showed.

But this cannot work as I want all table cells with dates to be highlighted where there is an entry. With:

        echo '<td';		$curTime1 = mktime(0, 0, 0, $month, $current, $year);		$curTime2 = mktime(23, 59, 59, $month, $current, $year);		$qd = "SELECT UNIX_TIMESTAMP(EVENT_TSTAMP) AS EVENT_TSTAMP FROM Metastar_calendar";		$rd = mysql_query($qd) or die(mysql_error());		$shown = FALSE;		while($row = mysql_fetch_array($rd)){ 		$eventTime = $row['EVENT_TSTAMP'];		if (!$shown)		{		if ($eventTime <= $curTime2 && $eventTime >= $curTime1) echo ' style="background-color:#1c3b6a;"';		$shown = TRUE;		}		}		echo '>';

it prints the background colour only once and leaves out any other table cells that should have background-colour once. Hope you know what I mean...Son

[justsomeguy]

It shouldn't do that, it should print 1 background color per td element. You're not outputting another td in the loop, you output the td at the start, an optional background color, then the end of the td. That sounds like it will set the background color for each td that has an event.

[son]

It shouldn't do that, it should print 1 background color per td element. You're not outputting another td in the loop, you output the td at the start, an optional background color, then the end of the td. That sounds like it will set the background color for each td that has an event.

I do not understand either. Have it now on different server and still same result...Son

[justsomeguy]

Show all of the code. You're saying that it only highlights one day, regardless of how many days have events?

[son]

Show all of the code. You're saying that it only highlights one day, regardless of how many days have events?

That is right, but is working now. Just solved it. The code now is:

        echo '<td';		$curTime1 = mktime(0, 0, 0, $month, $current, $year);		$curTime2 = mktime(23, 59, 59, $month, $current, $year);		$qd = "SELECT UNIX_TIMESTAMP(EVENT_TSTAMP) AS EVENT_TSTAMP FROM calendar";		$rd = mysql_query($qd) or die(mysql_error());		$shown = FALSE;		while($row = mysql_fetch_array($rd))		{					if (!$shown)			{ 			$eventTime = $row['EVENT_TSTAMP'];				if ($eventTime <= $curTime2 && $eventTime >= $curTime1) 				{				echo ' class="highlight"';							$shown = TRUE;				}		}		}		echo '>';		echo '<a href="' . htmlspecialchars($_SERVER['PHP_SELF']) .            '?t=' . mktime(0, 0, 0, $month, $current, $year) . '">' .            $current . '</a></td>';        $current++;    }

Many thanks for your helpful inputs,Son