Activity: PHP and database

(no submission)

Purpose: Hands-on experience with database programming; get ready to work on your assignment (course project)

This exercise focuses on using web application and database, with an assumption that you are familiar with the database concepts. For the fundamental concepts of database (such as database design, SQL commands, query optimization) please refer to CS 4750 (Database systems), Intro to SQL commands (by Professor Nada Basit), Basic SQL, aggregates, joins, subqueries, and quantifiers.

You may work alone or with another student in this course. There are three options. Please do at least one.


Option 1: Library Notification

(PHP, mail service, database programming, retrieve data)

Imagine you are implementing a notification system for webPL Library (Alternatively, you may use any library's name of your choice). You will write a PHP program (or programs) to access the database, retrieve information about the books that have been borrowed, and then determine whether an overdue notification must be sent.

  1. Set up a sample library database: You may do one of the following options.
    Option 1: copy and paste
    1. Download library.sql
    2. Log into your database server
    3. Click on your database name on the left-hand side or select the SQL tab and then type use your-database-name;
    4. Select the SQL tab, copy all of the code in this file, paste it into the SQL tab. Then, click Go
    5. Click back onto your database name and verify that the table and content were added
    Option 2: use the Import feature
    1. Download library.sql
    2. Log into your database server
    3. Click on your database name on the left-hand side or select the SQL tab and then type use your-database-name;
    4. Select the Import tab
    5. Select the SQL file (library.sql) you want to run. Then, click Go
    6. Click back onto your database name and verify that the table and content were added
    Option 3: type your own SQL scripts
    1. Log into your database server
    2. Click on your database name on the left-hand side or select the SQL tab and then type use your-database-name;
    3. Go to the SQL, write SQL scripts to set up your database. For simplicity, let's create a table containing information about books that have been borrowed from webPL Library (or your library). The table may be similar to borrow_records.json.
    Feel free to modify the database include additional records, update the borrowers' email addresses.
  2. Write a PHP program to do the following tasks. You may use your solution from the Mail Service activity as a starting point.
    1. Establish a database connection.
    2. Retrieve information about books that have been borrowed from the database.
    3. Determine if the book is overdue. You may assume that each book can be borrowed for 14 days. If the book is overdue, construct an email notification. You decide on the email content and format. The content should be customized and included at least the following information:
      • Borrower's name
      • Book title(s)
      • Due date
      Your program then uses PHPMailer to construct and send an email notification to the borrower's email address. Be sure to include a proper email's subject.

Optional:

In addition to the above functionality, you may consider and handle how many times the notification has been sent. You may consider and handle a renewal option. You may compute and include overdue fine in the email notification. You may create a form allowing a user (presumably, a librarian) to view existing list of books that have been borrowed and/or sort the list. You may create a form allowing a user (presumably, a librarian) to update the list. Then, your program should be modified such that it retrieves the form data entry and subsequently reflects the database.

Feel free to modify the notification the way you like, or add additional content you feel should be included. Get creative and have fun!


Option 2: My ToDo

(PHP, form handling, database programming, MVC pattern, basic SQL operations)

You may use the provided templates or create PHP programs from scratch.

You will start by accessing your database server, creating a table named todo (which consists of four columns: task_id, task_desc, due_date, and priority). Alternatively, you may create a table you plan to use for your CS 4640 project. Then, you will implement a PHP program using MVC pattern to:

Example screen is shown below

Optional:

In addition to the above functionality, you may create a form allowing a user to enter data to be inserted into the table. Then, your program should be modified such that it retrieves the form data entry and subsequently reflects the database.

You may modify your interface the way you like. Get creative. Feel free to add additional elements you feel should be included and have fun!


Option 3: Basic form and database

(PHP, form handling, database programming, basic SQL operations)

You may use connect-db.php (text version) and db-exercise.php (text version) as a starting point or create PHP program(s) from scratch. You will implement PHP program(s) to:

Optional:

In addition to the above functionality, you may create a form allowing a user to enter data to be inserted into the table. Then, your program should be modified such that it retrieves the form data entry and subsequently reflects the database.

You may modify your interface the way you like. Get creative. Feel free to add additional elements you feel should be included and have fun!


To deploy and test your program use one of the following options:

Refer to Connecting PHP and DB based on where you host your database.