Back to start

Lab 1 instructions

This assignment will be your introduction to creating a web application using the Php and MySQL. You are expected to create a functional todo list application that will be used to keep track of tasks. The application should be able to store tasks in a database, and be able to create, edit, and delete tasks.

You will use HTML, CSS, and JavaScript to create the layout and functionality of the application. The application should be designed to be responsive and work on both desktop and mobile devices.

You will use PHP and MySQL to store and retrieve data from the database. The database should be able to store all tasks, including information about each tasks status (completed=1, not completed=0).

Use Git and GitHub to store the code for the application and keep track of all changes.

By the end of this assignment, you should have a fully functioning todo list application. You should be familiar with the technologies used to create the application, and be able to use them to create more complex applications in the future.

Introduction

The application built with these instructions will contain the following files:

Page vs Script

A PHP file is always "just" a PHP file but some are referred to as Scripts and others as Pages. A Page is a web page that contains PHP code that is parsed by the web server and the output is then sent to the web browser. The PHP code can generate HTML, JavaScript, and other content that is sent to the web browser. A Script is any other file that are not directly displayed in the browser. Scripts might include files for functions, classes, or just code that interracts with the database and the redirects the user to a Page.

Linking to one task

In MySQL, if you follow the instructions below, all your tasks that you create will have a unique id in the database. In your index.php you will have links to each task in a table. Each task will be displayed using the same PHP file (a Page in this case). To link to a specific task you need to create a link with that tasks id in the url as a query string. For example, if you want to link to a task with id 2 and you have created your project files (including edit-task.php) inside a folder named todo-application, then the link for showing your task in your web application should point to http://localhost/todo-application/edit-task.php?id=2 (dont forget the port number 8888 if you're using MAMP.

Inside edit-task.php you can get the id (2 in our example) by using the PHP Superglobal $_GET and use that id to fetch the correct task from the database.

Sending POST data from pages to scripts

To create a new task you will need to create a PHP file with a form containing all the required fields (Title, Description, and Status). This form should use the attributes method="POST" and action="create-task.php" to send the form data to to the PHP script create-task.php. This data can then be fetched using the PHP Superglobal $_POST. For deleting or editing tasks you do the same thing as for creating the task, just use a form with all the required fields that sends a POST-request from edit-task.php to either update-task.php or delete-task.php.

Step 1: Start page (index.php)

  1. Create index.php-file for the start page of the application. Include a header, navigation, and main content area.

  2. Add styling to the page using CSS.

  3. Add JavaScript to the HTML page to prepare handling interactive functionality and validating form fields.

Step 2: Set up a database and table

  1. Open phpMyAdmin by navigating to it via http://localhost/phpmyadmin or http://localhost:8888/phpmyadmin for MAMP

  2. Log in using username root and no password (use root as the password for MAMP). If you don't see a login box on phpMyAdmin you are already logged in and can move on to the next step.

  3. Click on New in the left pane

  4. Select the database name todo_application and click on Create

  5. Click on the SQL tab

  6. Enter the following query:

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  1. Click Go

  2. The table tasks has been successfully created in the database todo_application

  3. Add a few tasks manually by navigating to your new table to the left and using the Insert tab at the top

Step 3: View all tasks

Before moving on, read up on connecting to the database and running SQL queries in PHP the Object Oriented method here and here

Inside your index.php file add the following

  1. Connect to the database using MySQLi.

  2. Prepare and execute a SELECT statement to retrieve all tasks from the database.

  3. Create a table for displaying the tasks, with a link for editing or deleting each task.

  4. Loop over each task from the database to display them in your table.

  5. Close the database connection.

  6. Test the page to make sure it is working by manually adding or editing your tasks in phpMyAdmin and updating the page to see if the changes are displayed correctly.

Step 4: Create a form in HTML for creating a new task

Before moving on, read up on how to handle form data here. create-task.php should retrieve data from the $_POST variable like the examples in the link.

  1. Create a PHP script create-task.php for creating your tasks in MySQL. Include code for connecting to the database, preparing and executing an INSERT statement, and closing the connection.

  2. Create PHP page new-task.php containing a form for creating a task

  3. Set the action attribute of the form to point to your create-task.php script and set the method attribute to POST.

  4. Add fields for the title, description, and status.

  5. Add JavaScript to the HTML page to handle functionality for validating form fields.

Step 5: Create a page for editing or deleting a task

  1. Create a PHP page edit-task.php for editing or deleting a specific task.

  2. Create a PHP script update-task.php for editing tasks. Include code for connecting to the database, preparing and executing an UPDATE statement, and closing the connection.

  3. Create a PHP script delete-task.php for deleting tasks. Include code for connecting to the database, preparing and executing a DELETE statement, and closing the connection.

  4. Create a form in edit-task.php for editing the task, as well as a button for deleting the task.

  5. Test the page to make sure it is working correctly.

Step 6: Testing and verification

  1. Test all the steps of the application by creating a task, editing it, and deleting it while visiting the start page in between to make sure that it updates

  2. Add CSS to improve the user experience if you haven't already

  3. Refactor and comment your code to improve the readability if you haven't already

Presentation

  1. Double check that your app fulfills all requirements by rereading the instructions.

  2. Present your work to a teacher at one of the lab sessions. You should be able to explain how all code you have written works, and the teacher might ask you some questions about it to verify that this is the case.

  3. If the teacher is satisfied with your presentation you will be approved on the Canvas assignment Laboratory Work Presentation.

  4. After the presentation, upload your project to the Canvas assignment Laboratory Work Code.

Bonus

  1. If you're done with the lab: try and make sure that you're only using prepared statements (no variables in your SQL-string)

  2. If you're done with the lab: try and use require_once to move repeated code to the same file.