Project 2

Website Implemented in PHP & MySQL

---

See course schedule for due date.

Getting Started

You will store all your files in a folder called project2 and upload your working project to our development servers, either breimer.net or sienacs.com. When naming folders and files, never use spaces. Use underscores instead, i.e., site_core.php

View the following sample solution to under stand the big picture:

Project 2 Solution
PHP Code is naturally hidden on the server.

While you must follow the requirements below, you can apply your own CSS and reuse styles from project1. You are encourage to use content from project1. Your website will look different and unique.

---

Resources

The following references are helpful. Please use them.

zyBook Chapter 12 and 13.5 - 13.9

---

1. Understanding the core framework

You PHP scripts should heavily utilize re-usable functions for creating typical content. To get started, we will create two core functions for generating an HTML document that uses Bootstrap's CSS and JavaScript. Bootstrap is the most widely used framework for making "mobile first" website, which are websites designed first for mobile devices.

Task: Create site_core.php

1. In your project2 folder, create a script called site_core.php that we will use to store core HTML functions.
2. Copy and paste the following code into site_core.php:
   Core Functions
3. Study the core functions and notice that there are two versions of each function. One version echos the HTML code and the other version returns the HTML codes as a string.
4. Create a script called site_test.php and add the following code

   <?
    require_once('site_core.php');
    echo_head('Site Test');
    echo '
     <div class="container">
   	
     <div>';
    echo_foot();
   ?>

5. Echo a Bootstrap Alert and a Bootstrap Button inside the container div. You can cut and paste the sample code from the Bootstrap documentation pages.
6. Create a script called style.css and add the following code:
   

   .container {
     border: 1px solid gray;
   }

7. Make sure your scripts are saved and then upload your project2 folder to the server.
8. Open a web browser and go to your URL (either username.breimer.net or username.sienacs.com). Then, click on your project2 folder and finally click on site_test.php.
9. You should see the sample alerts and buttons you created styled with standard Bootstrap CSS.
10. Resize the page to see how the container sizing works. Rather than use a fluid layout, Bootstrap uses 4 preset widths that are based on the most widely used screen resolutions.

---

2. Creating your own tables (10 points)

You will use PHP scripts to create initially empty tables for storing content for your web pages and asides. You will also create a table called has_aside for storing the relationship between pages and asides.

A. Task: Create your pages table

1. In your project2 folder, create a script called site_db.php that we will use to store database functions.
2. Add the following code, but replace rootname with either breimern or sienasel depending on whether you connect to breimer.net or sienasellbacks.com. Also, be sure to ask the instructor for the new password.
   

   <?
   	
   /* -----------------------------------------------------------------------------
   Connects to server, executes SQL statement, closes connection
   and returns MySQLi result object  	
   
   Input: SQL Statement (string)
   Output: MySQLi result object (iterator) 	
   ----------------------------------------------------------------------------- */
   function run_query($sql) {
     $mysqli = new mysqli('localhost', 'rootname_p_admin', 'ask instructor', 'rootname_pages');
     if ($mysqli->connect_errno) {
       die('Failed to connect to MySQL: '.$mysqli->connect_error);
     }	
     $result = $mysqli->query($sql);
     if (!$result) {
       die('Error executing query: '.$mysqli->error.'<br> SQL: '.$sql);
     }
     $mysqli->close();
     return $result;
   }	
   		
   ?>

3. Next, we are going to use the function above to create a table. To ensure that your table names are unique, you will append your username. For example, I would name my pages table to the following unique name: ea30brei_pages. In the instructions below, be sure to replace my username with your username.
4. In your project2 folder, create a script called site_create_tables.php and add the following code:
   

   <?
   	
   require_once('site_db.php');
   
   $sql = "CREATE TABLE IF NOT EXISTS `ea30brei_pages` (
     `pageid` varchar(32) NOT NULL,
     `title` varchar(64) NOT NULL,
     `parent` varchar(32) DEFAULT NULL,
     `content` text,
     PRIMARY KEY (`pageid`)
   )";
   
   run_query($sql);
   
   echo 'SUCCESS: The following query executed: <pre>'.$sql.'</pre>';
   	
   ?>

5. Make sure your scripts are saved and then upload your project2 folder to the server.
6. In a web browser, go to your URL and your project2 folder. Click on site_create_tables.php.
7. You should see the SUCCESS message.

B. Task: Verify that table is created

Since you do not have root access to the database server, we will create a script to return all the table information.

1. In your project2 folder, create a script called show_columns.php and copy & paste the following code:
   show_columns
2. Make sure your scripts are saved and then upload your project2 folder to the server.
3. In a web browser, go to your URL and your project2 folder. Click on show_columns.php.
4. Add the table name to the URL, i.e., show_columns.php?table=pages
5. You should see all the column information.

C. Task: Create your asides and has_asides tables

1. Modify your site_create_tables.php to also create your asides table and has_asides table.
2. Below the code to create the pages table, add code to define a query string and call the run_query funtiontion to create the asides table. Note: The asides table is almost identical to the pages table but it has two differences:
   • Instead of pageid, it has asideid, which should also be set as the primary key
   • Instead of parent, it has color, which should also be type varchar(32)
3. Below the code to create the asides table, add code to define a query string and call the run_query funtiontion to create the asides table. The query string will look exactly like this:
   
   

   CREATE TABLE IF NOT EXISTS `ea30brei_has_aside` (
     `pageid` varchar(32) NOT NULL,
     `asideid` varchar(32) NOT NULL,
     `ord` int(11) DEFAULT NULL,
     PRIMARY KEY (`pageid`,`asideid`)
   )

4. Notice that the primary key is pagid, asideid. This table implements a many-to-many relationship between the pages and asides. It allows a page to have two or more asides where ord specifies the display order. It also allows an aside to be used in two or more pages.
   
   Example: csdept page with three asides:
   

   pageid	asideid		ord
   csdept	depthead	1
   csdept	location	2
   csdept	programs	3

   
   Example: depthead being used as the first aside in two different pages:
   

   pageid	asideid		ord
   people	depthead	1
   latest	depthead	1

5. Echo a SUCCESS message after each query to be sure there were no errors.
6. Make sure your scripts are saved and then upload your project2 folder to the server.
7. In a web browser, go to your URL and your project2 folder. Click on site_create_tables.php.
8. You should see 3 SUCCESS messages.
9. Use show_columns.php?table=asides and show_columns.php?table=has_aside to see if the structure is correct.
10. You should see all the column information.

---

3. Inserting content (10 points)

You will use PHP scripts to create simple forms for inserting data into you three tables: pages, asides and has_aside. The tasks below will help you create a basic script for inserting a new row into the pages table. Afterwards, you can modify this script to create working forms for inserting new asides and for inserting new relationships between pages and asides.

A. Task: Create your pages form

1. In your project2 folder, create a script called insert_page.php and copy & paste the following code:
   insert_page
2. In your project2 folder, create a script called site_forms.php and copy & paste the following code:
   site_forms
3. In site_forms.php be sure to change the table name to replace my username with your username.
4. Using the functions return_input_text and echo_input_text, write a functions for returning and echoing a textarea. See Bootstrap Form Controls to understand the structure and classes used for a textarea.
5. In your project2 folder, create a script called show_table.php and copy & paste the following code:
   show_table
6. Make sure your scripts are saved and then upload your project2 folder to the server.
7. In a web browser, go to your URL and your project2 folder. Click on insert_page.php and try to insert some pages.
8. Use show_columns.php?table=pages to see if the row was added.

B. Task: Create your insert aside form

1. Open insert_page.php and save it as insert_aside.php
2. Modify it by changing the three function calls so that it calls insert_aside and echo_aside_form
3. Open site_forms.php and use return_page_form, echo_page_form and insert_page as models for creating three new functions called:
   • return_aside_form
   • echo_aside_form
   • insert_aside
   Note: That the only difference is that the aside table has an asideid (instead of pageid) and has color (instead of parent).
4. In site_forms.php be sure to change all table names to replace my username with your username.
5. Make sure your scripts are saved and then upload your project2 folder to the server.
6. In a web browser, go to your URL and your project2 folder. Click on insert_aside.php and try to insert some asides.
7. Use show_columns.php?table=asides to see if the row was added.

C. Task: Create your insert has_aside form

1. Open insert_page.php and save it as insert__has_aside.php
2. Modify it by changing the three function calls so that it calls insert_has_aside and echo_has_aside_form
3. Open site_forms.php and use return_page_form, echo_page_form and insert_page as models for creating three new functions called:
   • return_has_aside_form
   • echo_has_aside_form
   • insert_has_aside
   Note: That that has_aside table does not use a textarea, it only has three input text elements for the asideid, pageid and ord
4. Make sure your scripts are saved and then upload your project2 folder to the server.
5. In a web browser, go to your URL and your project2 folder. Click on insert_has_aside.php and try to insert some asides.
6. Use show_columns.php?table=has_asides to see if the row was added.

---

4. Building web pages from pages and asides (15 points)

Here we will create a PHP script to generate a page by passing the pageid in the URL and then fetching the page content and any aside content associated with that page.

A. Task: Create your index script

1. In your project2 folder, create a script called index.php and copy & paste the following code:
   

   <?
   require_once('site_db.php');
   require_once('site_core.php');
   	
   /* -----------------------------------------------------------------------------
   Get the pageid from the URL and generate a page
   ----------------------------------------------------------------------------- */
   
   // If the page is null, use the default pageid
   if ($_GET['pageid'] == null) 
   	$idpage = 'home';
   else 
   	$idpage = $_GET['pageid'];	 
   
   // Echo the major parts of the page from head to foot
   echo_head('Countries of the World');
   echo_content($idpage);
   echo_foot();
   ?>

3. In your site_core.php and copy & paste the following functions:
   echo_content
4. Make sure your scripts are saved and then upload your project2 folder to the server.
5. In a web browser, go to your URL and your project2 folder.
6. Add the pageid to the URL, i.e., project2/?pageid=home
7. Use a pageid of a page you actually created

B. Task: Adding your navigation menu (10 points)

The previous lab included many functions to generate a Bootstrap navigation menu. Add these functions to a file called site_nav.php.

Modify the nav menu generation query so that it reads your pages table.

In your index script, require the site nav functions and call the proper function to generate your navigtion between between the head and the content.

Help will be given in class if you are completely lost.

---

5. Building the back-end content management system

We will create PHP scripts to delete and edit both pages and asides.

A. Delete content (10 points)

Design and implement a form that allows a user to delete pages and asides.

Basic Delete (code)

B. Edit content (20 points)

Design and implement a form that allows a user to edit existing pages and asides.

Basic Update (code)

---

6. Improvements & Usability

Make the following improvements to maximize your grade

A. Drop-down Menus whenever possible (5 points)

The user should never have to type a pageid or asideid. Instead, provide a drop-down menu so the user can select existing ids.

Drop-down menu (code)

C. Adding aside content (10 points)

This will be the focus of our next lab, but if you wish to try this on your own, you can modify your index script to call a function called echo_side_content that will fetch all the asides assoicated with the pageid and insert this content into a Bootstrap column after the main content.

B. Website Presentation & Aside Customization (10 points)

Add custom CSS to make your web page look unique. It is OK to use styles from Project 1. Customize your aside content. For example, you can create a submenu if a page has "children" or you can use the aside color to decorate the border or text instead of the background color.

C. Integration (10 bonus points)

Design and implement a page that integrates all the content management functions: create, delete and edit pages and asides. In lab 10, we will learn how to secure these scripts using a session variable that is set during login.