Home arrow PHP arrow Page 4 - Miles To Go Before I Sleep...

Bringing In The Database - PHP

Ever wished you had an appointment calendar you could accessthrough your Web browser? Well, it's not too late. This week, learn how tobuild a simple PHP-based appointment calendar which allows you to view,add, edit and delete appointments and meetings. And if you're just gettinginto PHP, this article will show you how to apply the theory you've spentso much time learning to a real-world application.

  1. Miles To Go Before I Sleep...
  2. Building The Foundation
  3. Seven Days, Seven Nights
  4. Bringing In The Database
  5. Adding, Editing, Deleting...
  6. The Final Touch
By: Vikram Vaswani, (c) Melonfire
Rating: starstarstarstarstar / 6
February 05, 2001

print this article


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; ?>



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.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: