Miles To Go Before I Sleep... - Bringing In The Database

(Page 4 of 6 )

Since "day.view.php" is going to read the appointment list from a database, this is a good time to set up the table which will hold calendar data. Here's the structure I came up with - feel free to modify it to your requirements, but remember to alter the SQL queries as well.

# --------------------------------------------------------
#
# Table structure for table 'calendar'
#
CREATE TABLE calendar (
id int(10) unsigned NOT NULL auto_increment,
date date DEFAULT '0000-00-00' NOT NULL,
time time DEFAULT '00:00:00' NOT NULL,
comment text NOT NULL,
PRIMARY KEY (id)
);
# Column descriptions:
#
# id - unique identifier for each entry
# date - appointment date
# time - appointment time
# comment - appointment description
#

You might be wondering why I've split the date and time fields into two columns, rather than a single field. Keep reading - you'll see the reason soon enough.

Since I'll be connecting to the database quite frequently, and since I'm pretty lazy and dislike typing in more code than I have to, I've also created a single file, "config.php", which holds the mySQL user name, password and database name. This file is include()d whenever required to open a database connection.

<?
// config.php
$server = "localhost";
$user = "us564";
$pass = "he3423k4j";
$db = "calendar";
?>

Let's now move on to the "day.view.php" script. As you saw on the previous page, "day.view.php" receives the date, month and year via the URL GET method; it will then use these three variables within a SELECT query to find out if there are any previously scheduled appointments for that day.

<?
// format date for entry into database
$this_date = $currYear . "-" . sprintf("%02d", $currMonth) . "-" .
sprintf("%02d", $currDay);
?>
<table border="0" cellpadding="2" cellspacing="5">
<tr>
<td colspan=2 align=center>
<font face=Arial size=-1>
<b><? echo date("D M d Y", mktime(0,0,0,$currMonth,$currDay,$currYear));
?></b>
</font>
</td>
</tr>
<?
include("config.php");
// open a connection to the database
$connection = mysql_connect($server, $user, $pass);
// formulate the SQL query - same as above
$query = "SELECT * from calendar WHERE date='$this_date' ORDER BY time";
// run the query on the database
$result = mysql_db_query($db,$query,$connection);
?>

If you take a look at the table structure above, you'll see that the date and time fields require entry in a specific format - so the first order of business is to take the three variables passed to "day.view.php" and format them to match that format with sprintf(). So

<?
$currYear = 2001;
$currMonth = 1;
$currDay = 25;
?>

becomes

2001-01-25

Next, I've opened up a database connection and executed a query to find out if any appointments have been scheduled for that date. Depending on the result, I'll either display a list of appointments, or a message with the words "Nothing scheduled".

<?
// run the query on the database
$result = mysql_db_query($db,$query,$connection);
// if result
if(mysql_num_rows($result) > 0)
{
?>
<tr>
<td align=center>
<font face=Arial size=-1>
<i>Time</i>
</font>
</td>
<td align=left>
<font face=Arial size=-1>
<i>Description</i>
</font>
</td>
</tr>
<?
// get the list of appointments
while($row =  mysql_fetch_array($result))
{
$this_time = $row["time"];
$comment = $row["comment"];
$id = $row["id"];
?>
<tr>
<td align=center valign=top><font face=Arial size=-1><? echo
substr($this_time,0,5); ?></font> </td>
<td align=left valign=top width=200><font face=Arial size=-1><? echo
$comment; ?></font>  <font face=Arial size=-2><a
href="edit.php?id=<? echo $id; ?>&currYear=<? echo $currYear;
?>&currMonth=<? echo $currMonth; ?>&currDay=<? echo $currDay;
?>">edit</a></font>  <font face=Arial size=-2><a
href="delete.php?id=<? echo $id; ?>&currYear=<? echo $currYear;
?>&currMonth=<? echo $currMonth; ?>&currDay=<? echo $currDay;
?>">delete</a></font></td>
</tr>
<?
}
// close connection
mysql_close($connection);
}
else
{
?>
<tr>
<td align=center colspan=2>
<font face=Arial size=-1>
<i>Nothing scheduled</i>
</font>
</td>
</tr>
<?
}
?>
<tr>
<td align=left>
<a href="month.view.php?currYear=<? echo $currYear; ?>&currMonth=<? echo
$currMonth; ?>&currDay=<? echo $currDay; ?>"><font face=Arial size=-2>month
view</font></a>
</td>
<td align=right>
<a href="add.php?currYear=<? echo $currYear; ?>&currMonth=<? echo
$currMonth; ?>&currDay=<? echo $currDay; ?>"><font face=Arial
size=-2>add</font></a>
</td>
</tr>
</table>
?>

Each entry (if there is one) is displayed with an "edit" and "delete" link next to it - these point to the "edit.php" and "delete.php" files respectively. Once the appointment list has been displayed, I've added two links at the bottom - one takes you back to "month view", while the other allows you to add a new appointment.

Here's what the result looks like.




Adding a new appointment is accomplished with "add.php", which again receives the date, month and year as GET parameters. Let's take a closer look at it next.

This article copyright Melonfire 2001. All rights reserved.

Next: Adding, Editing, Deleting... >>
 

More PHP Articles
More By Vikram Vaswani, (c) Melonfire

Comments On: Miles To Go Before I Sleep...

>>> Be the FIRST to comment on this article!