Home arrow MySQL arrow Page 2 - Setup Your Personal Reminder System Using PHP
MYSQL

Setup Your Personal Reminder System Using PHP


Here is an article that I can personally relate to. How many times have you forgotten to do something that was truly important? Read how one can use PHP to help you send reminders to yourself to prevent this from happening again.

Author Info:
By: Mayank Gandhi
Rating: 4 stars4 stars4 stars4 stars4 stars / 150
August 05, 2003
TABLE OF CONTENTS:
  1. · Setup Your Personal Reminder System Using PHP
  2. · Article
  3. · Final Destination

print this article
SEARCH DEVARTICLES

Setup Your Personal Reminder System Using PHP - Article
(Page 2 of 3 )

What Do We Need?

Not much actually, we just need the following which are available on most servers:
  • A server which can host and execute PHP scripts
  • A MySql Database
  • A server with ability to send out mails via PHP
  • Optional: Access to setup CRON jobs (*nix) or Scheduler Events (windows)
System Design

An effective reminder system should have two major capabilities,
  • Browser Based Reminder Management
  • and, A system to send out reminders on its trigger date
The following is a simple three step procedure that will help us build this system effectively:
  • Designing the Database Schema (with the same structure you can use Flat Files too)
  • Browser-Based Reminder Management
    - Adding Reminders
    - Listing Reminders
    - Deleting Reminders
  • Finally, sending out reminders

Designing the Database Schema

Throughout this article, I will be using examples of MySql based databases, however if you wish to use flat files you can use similar structures to achieve the same output. We will be assigning each reminder a unique Id which will be used to identify it. For our requirements we need only one table to list all reminders and their details. I will refer to this table as the Events List table. Let’s design our Events List table.

We will use this table to list all the upcoming events with their reminder dates. This table will hold very few and basic fields which can easily be emulated in another DBMS, if you choose not to use MySql.

Reminder Events table: let’s name it reminder_events

Field NameField Type 
reminder_idIntegerHolds a unique Id for each reminder
reminder_nameTextUsed for storing the name for a reminder
reminder_descTextOptional description field of the reminder
reminder_dateChar(8)Holds the date of the reminder, e.g. 15082003

Following is the MySql query for the above table:

CREATE TABLE reminder_events (
reminder_id bigint(20) unsigned NOT NULL auto_increment,
reminder_name varchar(255) NOT NULL default '',
reminder_desc text,
reminder_date varchar(8) unsigned NOT NULL default '0',
PRIMARY KEY (reminder_id),
KEY reminder_id (reminder_id)
) TYPE=MyISAM;


Setting up Connection with MySql

Let’s build our first script; this script will serve as our database connection file. We will include this file in all our scripts as it will setup up a connection with our MySql database.

Let’s name this script, “database.inc.php”

<?php
$mysql_link = mysql_connect("localhost", "root", "root");
mysql_select_db("devarticles") or die("Could not select database");
?>


On execution of the code above it will establish a connection with the MySql Database named “devarticles” using the username, password as root. Therefore, before implementing this code, you should use your own username, password and database name values.

We will build 3 scripts in the following sections which will take care of the required functioning of this system. Within all these scripts we will be using the services of MySql to fetch, insert and update data from the database. Therefore we will include our database connectivity file, database.inc.php in each of these scripts to achieve a pre-connected link with our database on each execution.

Browser-Based Reminder Management 

The first and foremost necessity of the reminder system is to have browser based management system via which we can add, delete and list all existing reminders with their details. To achieve that, we will build 2 scripts to take care of these functionalities:

1. Setting up a Reminder Event
2. Listing set Reminder Events
3. Deleting of a Reminder Event

Setting up a Reminder Event

To setup a Reminder Event we require the following fields,

  • Reminder Name
  • Reminder Description
  • Trigger Date
I am referring the date when the reminder has to be sent out as its trigger date. To represent any date we will use a simple format, e.g. the date 15th August 2003 will be represented as 15082003 (ddmmyyyy). This format will allow us to easily compare dates, e.g to find out if a given date is an earlier or future one in comparison with the current date can now be done mathematically.

Firstly, let’s build a simply form that will allow us to add a new reminder event. We will name is script reminder_setup.php

<?php
include('database.inc.php'); // Our database connectivity file
if($_POST['step'] != '1')
{
?>
<html>
<head><title>Add Reminders</title></head>
<body>
<form name="setup_reminder" action="reminder_setup.php" method="post">
<table border='0' align='center'>
<tr>
<td>Event:</td>
<td>
<input name="reminder_name" type="text" maxlength="255" />
</td>
</tr>
<tr>
<td>Description</td>
<td>
<textarea name="reminder_desc" rows="5" /></textarea>
</td>
</tr>
<tr>
<td>Trigger Date</td>
<td>
<select name="reminder_year">
<?php
$current_year = date("Y");
for($counter=$current_year;$counter<=$current_year+2;$counter++)
{
echo("\n<option>$counter</option>");
}
?>
</select>
<select name="reminder_month">
<?php
for($counter=1;$counter<=12;$counter++)
{
if($counter < 10)
$prefix = "0";
else
$prefix = "";
echo("\n<option>$prefix$counter</option>");
}
?>
</select>
<select name="reminder_date">
<?php
for($counter=1;$counter<=31;$counter++)
{
if($counter < 10)
$prefix = "0";
echo("\n<option>$prefix$counter</option>");
}
?>
</select>
</td>
</tr>
<tr>
<td> </td>
<td>
<input name="step" type="hidden" value="1" />
<input name="submit" type="submit" value="add" />
</td>
</tr>
</table>
</form>
</body>
</html>
<?php
}


The above code will produce an output something like this:

Simple isn’t it? The above code produces an html form that includes the required fields of a reminder addition. It automatically lists the options for the Date fields using a for-loop to help us ease the effort while building these input fields. For the Year field it lists the current year and the next two year’s. E.g. for Year 2003 the options will appear as, 2003, 2004 and 2005, allowing us to build reminders for the next 2 years too.

In the second part of this code we need to write a system to check the validity of the fields sent by the form before we can add an entry for it in the database. The required fields are: the name of the Reminder Event and the date. The most important aspect and a little tricky validation is to check the format of the date entered. The Date should not be acceptable if either of the following two occur:

a. If the entered Date is from the Past

To check if the date is from past or not we will convert the sent date values into our data format (ddmmyyy) and then compare its value with the value of the current date which would also be in the same format. If the value of the date sent is less than or equal to the current date then in that case we will know that the event has passed. As our reminder events are not time specific but date specific we will won’t accept a date even if it is not from past but matches with the current date.

b. or If the entered date is an unreachable one

An unreachable date represents a certain combination of the Day, Month and Year values which can never be reached, e.g. 30th February. Therefore the date entered requires validating before it is indexed in the database. For this validation we will use PHP’s checkdate function; it requires 3 arguments, Month, Date and the Year value to find out if the combination date can ever be reached.

Now let’s add some code to the code we just wrote, which will allows us to validate the fields and thereafter allow us to index the submitted values to the database.

else
{
$error_list = "";
$todays_date = date( "Ymd" );
$reminder_date = $_POST['reminder_year'].$_POST['reminder_month'].$_POST['reminder_date'];
if( empty($_POST['reminder_name']) )
$error_list .= "No Reminder Name<br />";
if( !checkdate( $_POST['reminder_month'], $_POST['reminder_date'], $_POST['reminder_year'] ))
$error_list .= "Reminder Date is invalid<br />";
else if( $reminder_date <= $todays_date )
$error_list .= "Reminder Date is not a future date<br />";
if( empty( $error_list ) )
{
// No error let's add the entry
mysql_query( "INSERT INTO reminder_events(`reminder_name`, `reminder_desc`, `reminder_date`) VALUES('".addslashes($_POST['reminder_name'])."', '".addslashes($_POST['reminder_desc'])."', '".addslashes($reminder_date)."')" );
// Let's go to the Reminder List page
Header("Refresh: 1;url=reminder_list.php");
echo <<< _HTML_END_
Reminder Added, redirecting ...
_HTML_END_;
}
else
{
// Error occurred let's notify it
echo( $error_list );
}
}
?>


The code above validates all required fields. Initially the entered date is tried by the checkdate function after which we use value produced for the current date by the date function (which yields the value in our desired format),

$todays_date = date( "Ymd" );

With this value we can compare the entered date values to validate if the date is from the past or the future. If the date is from the future, we index the reminder event in our database. On a successful entry in the database the script will automatically redirect us to Reminder Listing page which we will be building in our next section.

This completes our code for reminder_setup.php, you can download this file from last page of this article.

Listing set Reminder Events

The main purpose of this script is to list all the existing reminders, along with to produce links via which we would be able to delete any reminder. The functionality for deleting reminders will also be embedded within this script. The code for this script is very basic in nature as it performs the following functions:

  • Lists all existing reminders with their event names and their trigger dates
  • Produces links for deletion of any reminders
  • Functionality to delete the requested reminder
We will build this script in two parts,

1. List all Reminders

2. Delete any requested reminder

An appropriate name for this script would be reminder_list.php.

List All Reminders

The first part of this script doesn’t require any complex coding. It will produce an output with the list of all existing reminders with their respective links to delete any of them.

<?php
include('database.inc.php'); // Our database connectivity file
if( empty($_GET['reminder_id']) )
{
?>
<html>
<head><title>List of Reminders</title></head>
<body>
<table width="90%" border="0" align="center">
<tr>
<td colspan='3'><a href='reminder_setup.php'>Add new Reminder</a></td>
</tr>
<?php
$result = mysql_query( "SELECT * FROM reminder_events" );
$nr = mysql_num_rows( $result );
if(empty($nr))
{
echo("
<tr>
<td colspan='3'>No Reminders setup</td>
</tr>
");
}
while( $row = mysql_fetch_array( $result ))
{
$reminder_name = $row["reminder_name"];
$reminder_date = $row["reminder_year"]."-".$row["reminder_month"]."-".$row["reminder_date"];
$reminder_id = $row["reminder_id"];
echo("
<tr>
<td width='60%'>$reminder_name</td>
<td width='30%'>$reminder_date</td>
<td width='10%'><a href='reminder_list.php?reminder_id=$reminder_id'>delete</a></td>
</tr>
");
}
mysql_free_result( $result );
?>
</table>
</body>
</html>
<?php
}


The above code will produce an output something like this:

Delete Any Requested Reminder

To delete any existing reminder we need to know its unique reminder Id. This id is generated whenever we add a new entry for a reminder. For example, to delete reminder Id 3, we will need to generate link along side its listing representing a URL something like this, reminder_list.php?reminder_id=3, which would evoke the second part of this script, hence deleting the reminder.

else
{
mysql_query( "DELETE FROM reminder_events WHERE reminder_id='".addslashes($_GET['reminder_id'])."'" );
// Let's go back to the Reminder List page
Header("Refresh: 1;url=reminder_list.php");
echo <<< _HTML_END_
Reminder Deleted, redirecting...
_HTML_END_;
}
?>


This completes our code for reminder_add.php, you can download this file from last page of this article.

Sending Out Reminders

While sending out reminders we will need to query the Database to fetch a list of all those reminders which have their trigger dates earlier or equivalent to the current date.

To achieve that we need to set two preference settings,

1. Number of Days before the reminder mail is to be sent, 

$number_of_days_before = 1;

e.g. if the value set to 1, it will enable the reminder to be sent 1 day before the event occurs. Similarly the value set to 0 will send the reminders on the same day of the event.

2. Destination Email address where the reminder mail has to be sent

$email = "youremail@yourserver.com";

The following code will send out all reminders bulked in one mail to avoid unnecessary multiple copies for each reminder. After sending out the mail with all reminder details for a given day, those very reminder entries will have to be deleted from the database to protect from duplicate processing the next day.

<?php
include('database.inc.php'); // Our database connectivity file
// Values you need set
$number_of_days_before = 1;
$email = "youremail@yourserver.com";
$reminder_details = "";
$todays_date = date( "Ymd" );
$year = substr($todays_date, 0, 4);
$month = substr($todays_date, 4, 2);
$date = substr($todays_date, 6, 2);
$trigger_date = date("Ymd", mktime (0,0,0,$month,$date-$number_of_days_before,$year));
$result = mysql_query( "SELECT * FROM reminder_events WHERE reminder_date <= $trigger_date ORDER BY reminder_date ASC" );
$nr = mysql_num_rows( $result );
while( $row = mysql_fetch_array( $result ) )
{
$year = substr($row["reminder_date"], 0, 4);
$month = substr($row["reminder_date"], 4, 2);
$date = substr($row["reminder_date"], 6, 2);
$reminder_date = date("M j, Y", mktime (0,0,0,$month,$date,$year));
$reminder_details .= "Event: ".$row["reminder_name"]."\n";
$reminder_details .= "Date: ".$reminder_date."\n";
$reminder_details .= $row["reminder_desc"]."\n\n";
}
mysql_free_result( $result );
if( !empty( $nr ) )
{
// Send out Reminder mail
$mailheader = "From: Reminder System <$email>\nX-Mailer: Reminder\nContent-Type: text/plain";
mail("$email","Reminder","$reminder_details","$mailheader");
// Delete the sent reminders
mysql_query("DELETE FROM reminder_events WHERE reminder_date <= $trigger_date" );
}
?>


Following is the sample of the content of the mail that will be sent out:

Event: My Best friends Birthday
Date: Jul 15, 2003
Be sure to buy some Red Roses for her
Event: Mom Dad’s Anniversary
Date: Jul 15, 2003
For God sake remember to wish them this time

This completes our code for reminder_send.php, you can download this file from last page of this article.
Note: For versions older than 4.1.0, equivalent of _GET, _POST, _FILES are arrays HTTP_GET_VARS, HTTP_POST_VARS, HTTP_POST_FILES.


blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials