PHP and MYSQL Calendar [Archive]

View Full Version : PHP and MYSQL Calendar

Canada4Life

01-06-2005, 02:57 AM

I have a calendar script that I will be using on my site. It displays the calendar for the current month. I have events stored in a mysql database. The dates for the events is currently stored ina unix timestamp. I'm not sure if that will make at I'm trying to do impossible or not. What I would like is for the days that have events in the database to link to another page that will display the events(s) for that day. The problem is I don't know what kind of code is required to do this.

Here's the calendar code:
<table width="80%" align="center" cellpadding="0" cellspacing="1" border="0">
<tr>
<td colspan="7" align="left" valign="middle" class="calheader">

<?php echo date("F Y") . "\n"; ?>

</td>
</tr>
<tr>
<td colspan="7" align="left" valign="middle">
<img src="images2/spacer.gif" width="1" height="4" alt="" /></td>
</tr>
<tr>
<td align="center" valign="middle">

S

</td>
<td align="center" valign="middle">

M

</td>
<td align="center" valign="middle">

T

</td>
<td align="center" valign="middle">

W

</td>
<td align="center" valign="middle">

T

</td>
<td align="center" valign="middle">

F

</td>
<td align="center" valign="middle">

S

</td>
</tr>
<?php
$month = date(n);
$year = date(Y);
$totaldays = 0;
while ( checkdate( $month, $totaldays + 1, $year ) )
$totaldays++;
$offset = date( "w", mktime( 0, 0, 0, $month, $day, $year ) ) + 1;
echo ' <tr>' . "\n";
if ( $offset > 0 )
echo str_repeat( ' <td align="center" valign="middle" class="calendar">
 
</td>' . "\n", $offset );

for ( $day = 1; $day <= $totaldays; $day++ )
{
echo ' <td align="center" valign="middle" class="calendar">
' . "
$day" . '

</td>' . "\n";
$offset++;

if ( $offset > 6 )
{
$offset = 0;
echo ' </tr>' . "\n";
if ( $day < $totaldays )
echo ' <tr>' . "\n";
}
}
if ( $offset > 0 )
$offset = 7 - $offset;

if ( $offset > 0 )
echo str_repeat( ' <td align="center" valign="middle" class="calendar">
 
</td>' . "\n", $offset );

echo ' </tr>
</table>' . "\n";
?>
 

And here's the structure of the db table:
CREATE TABLE `events` (
`event_id` int(11) NOT NULL auto_increment,
`event_title` varchar(255) NOT NULL default '',
`event_date` varchar(50) NOT NULL default '',
`event_text` text NOT NULL,
PRIMARY KEY (`entry_id`)
) TYPE=MyISAM AUTO_INCREMENT=22 ;

firepages

01-06-2005, 04:32 AM

you have event_date as a varchar in your DB so we have no way of knowing what format the date is in.

If you used a mysql or unix timestamp the rest should be straightforward.. assuming mysql timestamp

load date info into an array ....
<?
$yaks = mysql_query("
SELECT event_id,
DATE_FORMAT(event_date,'%d') as day
FROM events
WHERE event_date > $start_of_month
AND event_date < $end_of_month
") ;
while($r = mysql_fetch_assoc($yaks)){
$events[$r['day']] = $r ;
}
?>

then in your code ...
<?
for ( $day = 1; $day <= $totaldays; $day++ )
{
$day_str = $day ;
if( isset($events[$day] ) ){
/*build the link*/
$day_str = '<a href="entry.php?event_id='.$events[$day]['title'].'">'.$day.'</a>';
}
?>

and change $day to $day_str wherever you echo it
but you need to sort out your DB first ~

Canada4Life

01-06-2005, 05:23 AM

you have event_date as a varchar in your DB so we have no way of knowing what format the date is in.
As mentioned in my previous post, the date is stored in unix timestamp format.

but you need to sort out your DB first ~
What do you mean by sort out my database?

firepages

01-06-2005, 06:23 AM

hi sorry I missed the bit about unix timestamp :D

Well the code I posted is basically the general idea , create a valid timestamp for the start and end of the month displayed (see mktime() (http://www.php.net/mktime)) then compare those against the DB to get all the events id's between the start and end of the month.

now you need to grab the day from the unix_timestamp , my GUESS is

select event_id, DATE_FORMAT(FROM_UNIXTIME(event_date) , '%d') as day FROM etc

the bit about sort out your DB is irrelevant if you are using a unix timestamp (though you dont need 50 chars for it which is why I was unsure)

if you are going to be doing date calculations in MySQL the mysql timestamp format is often easier to work with (within mysql itself)

Canada4Life

01-23-2005, 11:44 AM

Ok. I looked into mysql timestamp and decided to use it. I've modified all the entries in my datatbase to use that format now. I took the code you posted and modified it a bit to fit into mine, and it's working great except that I omitted the "WHERE" clause. My calendar will only be displaying the current month. What would be the easiest way to create mysql timestamps for the beginning and end of the month?

Canada4Life

01-26-2005, 07:24 AM

^bump^

firepages

01-27-2005, 12:41 AM

MySQL timestamp format would be YYYYMMDDHHMMSS , the HHMMSS bits can all be '0' so for january 2005

$start_of_month = '20050101000000' ;
$end_of_month = '20050131000000' ;

to get those values automatically , not tested but similar to below should work (for the current month/year)

<?php
$start_of_month = date(Y).date(m).'01000000' ;
$end_of_month = date(Y).date(m).date(t).'000000' ;
?>

Canada4Life

01-27-2005, 03:22 PM

Thanks. That gives me the correct timestamps, but there's something wrong. Some of the entries are not appearing on the calendar. For testing I made an entry for every day in December and January. On both calendars the first nine days are not made into links. Either is the last day. I ran the same query through phpMyAdmin and it returned everyday. So there's something wrong with my code, but I can't see what it is.

Canada4Life

01-31-2005, 01:38 PM

Ok I fixed the problem with it not showing the last day of the month. Probably not the best solution, but it works. It's still not showing links for the first 9 days of the month. Can anyone see anything in my code that would cause that? I've changed the database and code a bit since I posted last so here it is again.
<?php
$connect = mysql_connect('localhost', 'user', 'pass');
mysql_select_db("database");

$start_of_month = date(Y).date(m).'01000000';
$end_of_month = date(Y).date(m).date(t).'235959';

$query = "SELECT entry_id,
DATE_FORMAT(entry_date,'%d') as day
FROM test
WHERE entry_date > $start_of_month
AND entry_date < $end_of_month";

$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
{
$test[$row['day']] = $row ;
}
?>

<table width="80%" align="center" cellpadding="0" cellspacing="1" border="0">
<tr>
<td colspan="7" align="left" valign="middle" class="calheader">

<?php echo date("F Y") . "\n"; ?>

</td>
</tr>
<tr>
<td colspan="7" align="left" valign="middle">
<img src="images2/spacer.gif" width="1" height="4" alt="" /></td>
</tr>
<tr>
<td align="center" valign="middle">

S

</td>
<td align="center" valign="middle">

M

</td>
<td align="center" valign="middle">

T

</td>
<td align="center" valign="middle">

W

</td>
<td align="center" valign="middle">

T

</td>
<td align="center" valign="middle">

F

</td>
<td align="center" valign="middle">

S

</td>
</tr>
<?php
$month = date(n);
$year = date(Y);

$totaldays = 0;
while ( checkdate( $month, $totaldays + 1, $year ) )
$totaldays++;
$offset = date( "w", mktime( 0, 0, 0, $month, $day, $year ) ) + 1;
echo ' <tr>' . "\n";
if ( $offset > 0 )
echo str_repeat( ' <td align="center" valign="middle" class="calendar">
 
</td>' . "\n", $offset );

for ( $day = 1; $day <= $totaldays; $day++ )
{
$day_str = $day ;
if( isset($test[$day] ) )
{
$day_str = '<a class="small" href="entry.php?entry_id='.$test[$day]['entry_id'].'">'.$day.'</a>';
}
echo ' <td align="center" valign="middle" class="calendar">
' . "
$day_str" . '

</td>' . "\n";
$offset++;

if ( $offset > 6 )
{
$offset = 0;
echo ' </tr>' . "\n";
if ( $day < $totaldays )
echo ' <tr>' . "\n";
}
}
if ( $offset > 0 )
$offset = 7 - $offset;

if ( $offset > 0 )
echo str_repeat( ' <td align="center" valign="middle" class="calendar">
 
</td>' . "\n", $offset );

echo ' </tr>
</table>' . "\n";
?>
 

There's also a problem with months where the first is on a Monday like it is in May 2005. In that case, it shows 8 days in the first week of the month. :confused:

Canada4Life

02-07-2005, 04:48 AM

Ok, I've decided to get rid of the calender for now, but if anyone can offer any suggestions on how to fix my code please reply. Thanks in advance.