A Simple Calendar Page To Display Date-Specific Data In PHP

Recently asked on Yahoo! Answers:

PHP-MYSQL – filling a calendar?

i wrote this code for writing a calendar. The calendar is MTWRFSS.The inputs that are required are:
1)Month Name
2)Starting column to begin the fill. For example, Dec2006 starts on Friday, so there are 4 blanks (m-t-w-r)
3)Number of days in month.
// link with code (txt) format
http://www.target209.com/sample.txt

Im probably just going to make a table thats stores 1-3.

In my main table, it stores, key(primary auto increment, date(datetime format), and journal entry.

OBJECTIVE – While the code in sample.txt is executing, it will make a hyperlink if an entry exists for that day. So the user can just click on the calendar day, and then the results will display in the main section of the page.

My theory is to
1)do a sql query to find out all the days that exist for month xx. then set them to an temp array.
1a–assume there are entries on the 3,8,13,23,28th day of month xx. Then write an if statement to check the For $i value against the the temp array.
Any thougts?

With all due respect to the questioner, who’s a pretty nice fellow in my opinion, one of the things I remember from when I first started programming — and something I see oft repeated in my travels — is that newbies tend to view problems as a lot more complex than they are. I think that’s actually an issue anytime someone takes on something new; your skill set is limited, so everything seems tough, and the more you try to fit it into what you do know, the harder it becomes to resolve.

However, a calendar is a pretty straightforward problem, when you break it down into its components.

  • We need to know the month and year that you want to display in your monthly calendar. We have to get those variables somehow; I’ll be doing this tutorial with the assumption that the variables will be in the querystring (i.e., $_GET).
  • We need to discover on what day of the week the first day of that month falls.
  • We need to know how many days are in the month.

Let’s not worry about linking anything just yet; let’s just focus on getting the calendar to display.

We’ll output the calendar in a simple HTML table, with seven columns; one per weekday. The spec for this wants to run from Monday to Sunday, which makes things a bit tricky, but not anything we can’t handle with a bit of creativity.

We need to do the following before we begin outputting the table:

  • We need to make sure we are getting valid parameters for the month and year requested, or at least check that we aren’t getting data that is wildly inappropriate. We’ll do that by checking if the variables are set, and by running them through regular expressions that ensure we get only 2-digit months or 4-digit years.
  • We need to construct the first day of the specified month and year using mktime().
  • We need to get, from that date, the day of the week, which we’ll retrieve via getdate().
  • Finally, we need to know how many days there are in the requested month, which we’ll get with date().
if(!isset($_GET['m']) || !isset($_GET['y']) || !eregi('^[0-9]{1,2}$', $_GET['m']) || !eregi('^[0-9]{4}$', $_GET['y'])) {
	die('You have not provided the month or year for this calendar, or they are not in the proper format');
}

$month = $_GET['m'];
$year = $_GET['y'];

$this_month_day1 = mktime(0, 0, 0, $month, 1, $year, 0);
$this_month_vals = getdate($this_month_day1);
$days_in_month = date('t', $this_month_day1);

OK, with that information, we can begin outputting the table that will hold our requested month. First, we’ll output the header rows:

//begin outputting calendar table
echo "<table id=\"calendar\">\n";
echo "<tr><th colspan=\"7\">" . date('F Y', $this_month_day1) . "</th></tr>n";
echo "<tr><th>Mo</th><th>Tu</th><th>We</th><th>Th</th><th>Fr</th><th>Sa</th><th>Su</th></tr>n";

Now comes the thinking. We need to know on what day of the week the first day falls. Fortunately, one of the keys of the array returned by getdate() is wday, or the day of the week on which the specified date falls. That key’s value will be an integer between 0 and 6, where 0 is Sunday and 6, Saturday.

Because this calendar will run Monday through Sunday, we need to blank out any cell that is higher than 1 (Monday). We need to stop blanking out cells when we get to the cell immediately before the first of the month.

To do that, we first declare the beginning of the first data row for the table, then put out as many blank cells as are needed. We’ll do that with a for loop.

After we output all the blank cells, we need to know the value of the weekday immediately preceding the weekday on which the month begins. For example, if the month begins on a Thursday (4), we need to hold on to Wednesday (3). The reason will become known in a bit.

Finally, because this calendar runs Monday through Sunday, we have to have a separate condition to fill in all the weekdays other than Sunday if the month begins on Sunday.

echo "<tr>";

if($this_month_vals['wday'] > 1) {
	for($i = 1; $i < $this_month_vals['wday']; $i++) {
		echo "<td class=\"blank\">&nbsp;</td>";
	}
	$x = $i - 1;
}
elseif($this_month_vals['wday'] == 0) {
	for($i = 1; $i <= 6; $i++) {
		echo "<td class=\"blank\">&nbsp;</td>";
	}
	$x = 6;
}

With that done, we’re now ready to output the calendar proper. But before we do that, we need to get all the records in the database that fall within the requested month and year.

A Quick Aside On Why Good Variable Names And Proper Data Formats Matter

One of the problems with the specification is the questioner has named a column in his table date. Unfortunately, DATE is a reserved word in MySQL. A reserved word is a word that a programming language uses to handle its internal business; in this case, DATE is a data type in MySQL.

If you use reserved words as variable or function names, you get unexpected behaviors when you try to manipulate them.

For example, in MySQL, you can use the MONTH() function to get the month part of a date. However, if you try to do this:

SELECT MONTH(date) FROM mytable

You’ll get a syntax error in MySQL, because MySQL doesn’t evaluate the (date) argument as a call to a column name; it evaluates it as an expression, and in this case, it uses the reserved-word meaning, which is a data type. In that context, providing a data type is meaningless to the function, and that’s why you get the error.

That’s why you should always follow good naming conventions and not use reserved words. For this tutorial, I have changed the supplied column names key and date to mykey and mydate.

Also, when you are going to primarily work with the dates in PHP, it makes more sense to store your dates in MySQL as integers, rather than DATE or DATETIME.

Since PHP works with dates the same way Unix does — by measuring time as the number of seconds that have passed since January 1, 1970 — and since most of the built-in PHP date functions work with integers, it just makes more sense to store your timestamps, rather than DATE or DATETIME types.

True, you can always convert those DATE or DATETIME values via PHP or SQL, but why waste effort doing that? Keep it simple.

A final note: MySQL TIMESTAMP and PHP timestamps are not the same thing. Don’t attempt to save a PHP timestamp in a MySQL TIMESTAMP column. Just create an INT(10) column in your MySQL database and store your PHP timestamps in that.

Outputting Calendar Entries

OK, back to the problem at hand.

The SQL to extract from the database all the requested month’s journal entries is simple, once we have good column names. So, we connect to the database and run our query:

$sql = "SELECT DISTINCT DAYOFMONTH(mydate) AS entryday FROM tbltest WHERE MONTH(mydate) = $month AND YEAR(mydate) = $year ORDER BY DAYOFMONTH(mydate) DESC";
$rs = mysql_query($sql) or die('Cannot get entries');

Notice that we create a column, entryday, from the SQL; again, because we are using DATETIME to store these values, we’ll save ourselves some effort later by simply having the database return the unique days of the articles, since we already know the month and year will be correct.

We are going to loop through this recordset, as we create the calendar, looking for matches between entryday and the day of the month we are outputting.

So, with the calendar ready to start outputting days and the records for the month on hand, we can go ahead and output the days, which we’ll do with a while() loop. Specifically, we’ll start at day 1, and run through to the last day of the month.

There’s one last trick. Remember how we held on to the weekday before the weekday on which the month begins? We did that because we need to know when to break for a new week in our table — that is, when to output a

or

tag.

We can figure that out using modulus. Any time the modulus of $x / 7 is equal to 0, we’ll echo a

or a

. By placing an incrementer for $x in the right place, we can place the tags in the right place.

$i = 1;
while($i <= $days_in_month) {
	if($x % 7 == 0) {
		echo "<tr>";
	}
	echo "<td>";
?>

Next, we set a boolean to a value of false. The boolean will tell us when we have found a record, from our journal entries recordset, that has the same day as the day we’re outputting.

So, we’ll iterate through all the records in the recordset, using a while() loop, looking for a match between entryday and the $i control variable.

If we find a record, we simply output a link back to this page that adds a day parameter to the query string. If we don’t find a match, we just output the day number.

We’ll keep recycling this resultset, so we don’t have to keep querying the database, using mysql_data_seek() to move back to record 0 (in a resultset, like an array, the index begins at 0).

If we find a match, we can stop looking through the recordset; we break out of the while loop once we’ve found a match. Finally, we output the closing cell tag.

	$found = false;
	if(mysql_num_rows($rs) > 0) {
		mysql_data_seek($rs, 0);
		while($row = mysql_fetch_array($rs)) {
			if($row['entryday'] == $i) {
				echo "<a href=\"index.php?m=$month&y=$year&d=$i\">$i</a>";
				$found = true;
				break;
			}
		}
	}

	if($found == false) {
		echo $i;
	}

	echo "</td>";

To finish up the loop that outputs the days on our calendar, we increment both the $i and $x variables by 1. We also check to see if we are at the end of the row; if so, we output a

tag.

	$x++;
	$i++;

	if($x % 7 == 0) {
		echo "</tr>\n";
	}
}

We’re awfully close to done with the calendar. The last two steps are to check if there are any blank cells that need to be output on the final row of the table (that is, if the week ends on any day other than Sunday, we need to output blank cells to keep the table cells aligned properly). If so, we output them. And last, but not least, we close the table.

if($x % 7 != 0) {
	for($i = $x; $i % 7 > 0; $i++) {
		echo "<td class=\"blank\">&nbsp;</td>";
	}
	echo "</tr>\n";
}

echo "</table>\n";

Now, on to the code that will display articles for the selected day.

As we did with the month and year, we’ll check for whether the day variable is set in the query string, and if it is, we’ll ensure it’s a 1- or 2-digit number via a regular expression.

If the day variable passes those tests, we try to make a specific date out of the arguments. If they don’t compute to a date, there’s no point behind proceeding; we’ll just kill the script outright. If there is, we’ll go ahead and output a headline for the articles section, and try to get the associated records for the requested day.

mktime() will manufacture a date even if the parameters are BS. For example, if you try this:

$mydate = mktime(0, 0, 0, 11, 31, 2006, 0);

$mydate will equal 1164949200, which is midnight on Dec. 1, 2006. So my method for checking dates isn’t ironclad, and you might want to massage it some.

OK, back to the point at hand: Here’s the code.

if(isset($_GET['d']) && eregi('^[0-9]{1,2}$', $_GET['d'])) {
	$day = $_GET['d'];
	$articledate = mktime(0, 0, 0, $month, $day, $year) or die('Cannot parse date from inputs');
	echo "<h3>Journal Entries For " . date('F j, Y', $articledate) . "</h3>\n";
	$sql = "SELECT mykey, mydate, journal FROM tbltest WHERE MONTH(mydate) = $month AND DAYOFMONTH(mydate) = $day AND YEAR(mydate) = $year ORDER BY mydate DESC";
	$rs = mysql_query($sql) or die('cannot parse entries');

Our next step is to check how many records are in our resultset. If there are none, we output a message stating there are no records for the selected day (this is mostly to deter those people who might change query string values by hand).

If we do have records, we loop through them, outputting each one with simple echo statements. To wrap the whole script up, we close out the if statement that started this section of code.

$count = mysql_num_rows($rs);
	if($count == 0) {
		echo "<p>No articles for this date.</p>\n";
	}
	else {
		$i = 1;
		while($row = mysql_fetch_array($rs)) {
			echo "<div id="articlebreak">Journal Entry No. $i Of $count Entries</div>\n";
			echo "<p>" . str_replace("n", "<br />", $row['journal']) . "</p>\n";
			$i++;
		}
	}
}

I know it seems like this took a lot of code and a lot of effort, but in the grand scheme of programming, it’s actually a very simple solution: With a little thinking ahead, we’ve used very few variables and two queries to create an efficient and elegant blog calendar.

6 Comments

  1. Greetings-

    Neat website you have, i like the functionality of it. I just wanted to ty again for your assistance with this. My txt thing that i had did work just for writing the calendar, but required external outputs., but it was def inefficient and didnt take advantage of all the php tools/sql tools out there.

    btw, ive been reading this script your wrote for hours now just trying to hammer in all the points.

    The modulus was a very neat way for inserting the s. I also like how you broke apart the whole tr td a /a /td /tr component.

    mktime(0, 0, 0, $month, 1, $year, 0); === 1162357200 when you wrote this, is this in epoch format? I take it this is a date in the rawest form. For example, when you type a date in MS Excel, and then click format cells and set it to General, it shows something that doesnt make sense at all. Then when you formate it, or set it to date, it puts it in a format that makes sense.

    I put a question in yahoo question/answers regarding setting the calendar to go to the current month/year when the page loads. Could one just modifty your code to read, if ‘m’ ‘y’ are set, then set the get variables equal to $month & &year assuming they are in proper format } else { $month = date(‘m’) $year = date(‘y’).

    I noticed on your page, that when the user clicks the > previous or next month button, the folder structure changes to that month. I wrote this code below, works pretty neat.

    $pmonth = $month - 1;
    $nmonth = $month + 1;
    if ($pmonth == 0) {
    	$pyear = $year - 1;
    	$pmonth = 12;
    } else {
    	$pyear = $year;
    }
    
    if ($nmonth == 13) {
    	$nyear = $year + 1;
    	$nmonth = 1;
    } else {
    	$nyear = $year;
    }
    

    well, im going back to do some more php reading, lol but there is one thing for sure that i agree with you about. I cant stand theisman, the man just doesnt shut up, while he is on the air.

    anywho, drop me a line, let me know what your looking for these days, i might have it, or know someone that does.

    peace

  2. if i want to add calendar and then display record by selecting date if i select date 1 Jan then its display record which is saved in 1 Jan provide me some code details and some guidelines how to do this.

  3. @Tanvir: This solution is more than 10 years old. While the underlying ideas are sound, the code is not. And emailing code is a bad idea, regardless.

    Take the ideas here, as well as the code snippets, and build your solution from them.

Leave a Reply

  • Check out the Commenting Guidelines before commenting, please!
  • Want to share code? Please put it into a GitHub Gist, CodePen or pastebin and link to that in your comment.
  • Just have a line or two of markup? Wrap them in an appropriate SyntaxHighlighter Evolved shortcode for your programming language, please!