Each student has their own WordPress website to submit this assignment. A playlist of 8 videos on how to complete this assignment is available here.
Campus Travel is a fictious travel agency in Hawaii. They have offices located around the state and agents assigned to those offices. They book travel to destinations like Rome, Paris and Bangkok.
They have been using a spreadsheet (CAMPUS.xlsx) to keep track of everything. The spreadsheet has a worksheet for the agents, destinations, agency offices and sales.
In this assignment, you will import the spreadsheet into an online database. Then you will use WordPress and the ABASE plugin to create some forms and reports. Click for a finished version of the assignment.
PART 1 - WordPress
Before we begin with the database part, let's make sure we have our WordPress website set up with all the pages. We will also make sure the WordPress menu is set up.
If you have not yet installed WordPress, watch this video(0/7) first. Then watch the video(1/7) to set up blank pages and WordPress menu for the assignment.
Step 1. WordPress pages for Campus Travel.
Make sure you have created all your WordPress pages for your Campus Travel assignment. When you are adding new pages, make sure you are adding PAGES and not POSTS.
Campus Travel - Introduction menu and home page.
Database Tables - displays the agents and sales so you can confirm your database import and changes.
Add Sale - form for adding a new sale by an agent, to a destination, for an amount.
Search Sales - form for searching for sales by agent, destination or amount range.
Search Results - page for displaying the results of a sales search.
Update Sale - form for updating or deleting a particular sale.
Add Agent - form for adding a new agent.
Agent Bookings - display summary of total bookings by agent.
Step 2. WordPress Static Home Page Setup
Make sure you have set the home page of your WordPress site to your Campus Travel page.
In the Dashboard, under Appearance, click Customize.
Under Static Front Page set the Front page displays to A static page, and select Campus Travel as the Front page.
Click Save at the top and click Close to return to the Dashboard.
Step 3. WordPress Menu Setup
Make sure the WordPress menu for your Campus Travel assignment has the Add Agent, Agent Bookings, Add Sale, Search Sales Database Tables pages in its sub-menu.
To accomplish this,
In the Dashboard, under Appearance, click Menus.
Make sure the seven pages listed above are in the Menu Structure on the right. If any are missing, click the check boxes of the missing pages on the left in the Pages box and click Add to menu.
Delete any unwanted pages by clicking down on the Page arrow and clicking the Remove link. This only removes the page from the Menu Structure. It does not delete the page.
With your mouse, click and drag the pages so they appear in order and indented as shown above.
Click the button to Save (or Create) the Menu.
To make sure the menu is activated, click the Menu Locations tab at the top. Select your menu as the Primary Menu and click Save Changes.
Notice that your menu does not contain the Search Results or Update Sale page. The user reaches the Search Results page only from doing a search on the Search Sales page. The user reaches the Update Sale page only by clicking on the date link of a sale listed on the Search Results page.
Step 4. Static Name Page Addresses
Setting "Permalinks" to "Post name" allows your pages to be given human-friendly URLs. This makes it much easier to make your own menu links. It is necessary for your search sales form because you need to specify the address of the page for the results of the search. To set Post name Permalinks,
In the Dashboard, under Settings, click Permalinks.
On the Permalinks Settings form, set Common Settings to Post name.
Scroll to the bottom and click the Save Changes button.
Notice now the page URLs contain the page title.
Step 5. Remove Comments
As you add pages you often forget to remove the Comments section at the bottom. If your theme has a full-width page option, you may want that set for all the pages. You can perform these settings once for all the pages in your site. To do so,
In the Dashboard, click Pages on the left.
Click the check box to the left of Title above the pages, to select all the pages.
Click down Bulk Actions above and select Edit. Click Apply.
For the Template drop-down menu, select Full-width Page if available.
For Comments, select Do not allow. Click the Update button.
You can confirm this operation by examining your pages.
PART 2 - Excel and CSV files
To import a spreadsheet of worksheets into an online MySQL database, we must convert the four worksheets into for separate CSV files. Watch the video(2/7).
Step 6. Create CSV file for each table for import.
Open the CAMPUS.xlsx spreadsheet. Notice there are 4 worksheets: DESTINATION, AGENT, OFFICE, and SALE.
Save each sheet individually as comma-separated values files (.csv) on the desktop. Start by saving the DESTINATION worksheet as DESTINATION.csv. Make sure the word "DESTINATION" is all upper case. MAKE SURE you save it in plain .csv file format. Save the AGENT worksheet as AGENT.csv. Save the OFFICE worksheet as OFFICE.csv. Pay attention to case.
IMPORTANT - Before saving the SALE worksheet, change the SaleDate format to yyyy-mm-dd, which is a format that the database will recognize as a date. For example,
12/1/17 converts to 2017-12-01 (December 1, 2017).
To change the date format in Excel,
Select the SaleDate column by clicking on the B above the column.
On the HOME tab, in the Number section, click the drop-down and select "More Number Formats..." The Format Cells dialog box should open.
In the Format Cells dialog box, on the Number tab, click Custom at the bottom.
In the Type: box, clear and type yyyy-mm-dd and click OK.
The dates should now appear in year-month-day numerical format.
Now you can save the SALE worksheet as SALE.csv.
PART 3 - Creating a New MySQL Database
Now we will create a MySQL database to import the tables into. Watch the video(3/7).
Step 7. Create the database.
Before importing the CSV files we must create the database and give the WordPress user access rights. To do so,
Log into cPanel.
Find the MySQL Databases icon in the Databases section and click on it.
In the Create a New Database box, enter CAMPUS as the name and click Create Database. Click to go back to the MySQL Databases screen.
Scroll down and you should see the new database you created in the list of database. Scroll down further, past Add a New User, to Add a User to a Database. Select the WordPress User. The WordPress user's name will end with either "_wrdp1" or an underscore followed by 7 random characters (e.g., "_V7N0Vwo"). For the Database, select the _CAMPUS database you just created. Click the Add button.
Click the checkbox at the top to give the user ALL PRIVILEGES. Then click Make Changes.
You have now created the database and given WordPress access to it. Next you will import the CSV files as tables in your database.
PART 4 - phpMyAdmin: Importing CSV Files into MySQL
Now we will import the four CSV files into MySQL tables. Next we will adjust the field types and set the field constraints. Then we will relate the tables by setting up the foreign keys. Watch the video(4/7).
Step 8. Import the CSV files into tables.
We will use phpMyAdmin to import the CSV files into tables, and to specify the field types to meet our database constraints.
Log into cPanel.
Find the phpMyAdmin icon in the Databases section and click on it.
On the left, click the plus (+) sign next to your username to open the list of databases. Click on the name of the database you just created.
Import AGENT.csv. We will import the tables in alphabetical order to help us keep track of them.
Click the Import tab at the top to import the first table.
Click Browse... and find and select the AGENT.csv file you created.
Scroll down. phpMyAdmin should recognize the CSV format. (If it does not, you might not have saved the worksheets in .csv format.)
IMPORTANT - At the bottom under Format-Specific Options, make sure you check the checkbox indicating the first line of the file contains the table column names. The first line of the file contains the table column names.
Then click Go. phpMyAdmin will import the table as TABLE 1.
Repeat the process DESTINATION.csv, OFFICE.csv and SALE.csv.
When all files have been imported, the four tables TABLE 1, TABLE 2, TABLE 3 and TABLE 4 should be listed on the left column in your CAMPUS database. (It doesn't matter what the table numbers are - just that they are the tables we imported!) Next we will name the tables.
Step 9. Naming the Tables
Rename TABLE 1 to AGENT
Click on TABLE 1 on the left side bar. The table of agents should appear.
At the top, click the Operations tab. It might be under the More tab if you can't find it.
There are four forms on the Operations page. Find the Table options form. Rename the table to AGENT. Then click the Go button for the Table options form. Make sure you use the right form and you click the right Go button.
It may appear like nothing happens. This is OK. You can click on the name of your database at the very top to return to see the up-to-date list of your database tables. The first table should be renamed to AGENT.
Repeat this process three more times to rename TABLE 2 to DESTINATION, TABLE 3 to OFFICE, and TABLE 4 to SALE.
Close phpMyAdmin. Return to cPanel
In cPanel, double-click on the phpMyAdmin icon to re-open it.
Click the plus (+) sign next to your username on the left to see the list of databases.
Click on the _CAMPUS database. Now you should see the list of tables properly named.
PART 5 - phpMyAdmin: Specifying MySQL Field Types
Now we will import the four CSV files into MySQL tables. Next we will adjust the field types and set the field constraints. Then we will relate the tables by setting up the foreign keys. Watch the video(5/7).
Step 10. Set Field Types.
Log into cPanel, then into phpMyAdmin. On the left, click to the CAMPUS database. Then, for each table,
Click the Structure tab at the top to display the list of fields.
On the Structure page click to set the Primary Key for the table. Also, click Index for any foreign key fields. (If you can't find the Index button for a field, roll your mouse over More.)
Click Change to remove the size limit on Integers, to set the other field data types and AUTO_INCREMENT.
Setup the following specifications for the four tables:
Click Change ...
Click Change ...
Click Change ...
Click Change ...
Step 11. Set up Relationships between tables.
Set up the foreign key relationships connecting AGENT to OFFICE and connecting SALE to AGENT and DESTINATION. In the Structure view, click the Relation view link under the table of field names and set:
ON DELETE RESTRICT
ON UPDATE CASCADE
ON DELETE RESTRICT
ON UPDATE CASCADE
ON DELETE RESTRICT
ON UPDATE CASCADE
PART 6 - WordPress: MySQL Interface
Finally we will connect the MySQL database to our WordPress web pages using the ABASE for MySQL plugin. Watch the video(6/7).
Step 12. Install ABASE plugin
The ABASE plugin is used to interface your MySQL database with your WordPress pages. To install the plugin,
In the Dashboard, on the left, click Plugins.
If ABASE is already installed, make sure you have the latest version installed. Click update now if not. If ABASE is not present, then
Click Add New at the top next to the Plugins heading.
In the Search Plugins box on the right, type ABASE and press the Enter key.
Click to Install ABASE.
Once installed, click Activate.
Click to return to the Plugins page.
To Setup ABASE, click ABASE Settings on the Plugins page, or Click Settings on the left, and click ABASE for MySQL.
For Texturization, make sure the Disable wptexturize checkbox is checked.
Under Database Settings, enter the name of your database, in this case, CAMPUS.
Click Save Changes at the bottom.
ABASE should now be installed and configured.
Step 13. Add ABASE short codes to your pages.
For each of the pages, add the listed ABASE short codes. IMPORTANT - Make sure on the Edit Page you click the Text tab above the text box to make sure you are in entering in Text mode not Visual mode.
Database Tables page
[abase] To confirm ABASE is configured properly, start by adding just this single [abase] shortcode to the Database Tables page. Then View the page. ABASE should display its version number, some other information, and your four tables. If you get an error, ABASE is not configured properly.
[abase table="AGENT" echo="red"]
[abase table="SALE" echo="red"]
The second shortcode will display the agents and their offices. The third shortcode displays the SALE table along with data from the other tables it is connected to. You will refer back to these tables when you test your pages at the end.
This shortcode will add a form to search the SALE table. You can search by Agent, Destination or a Sale Amount range.
The form="" attribute as shown assumes the relative page URL of the Search Results page end be /search-results/.
If your page has a parent or your WordPress was installed in a subdirectory you may have to add a path in front. Go to your Search Results page and copy the relative portion of the URL (to the right of your domain name) from the address bar of your browser and replace /search-results/ in the form attribute.
This shortcode will display the results of a search from the form on the Search Sales page. Note that when you view this page by itself, you should see all the sales. Note also that the date link is a link to the Update Sale page for that particular sale.
The rlink="" attribute specifies the SalesDate as the link and that the URL of the Update Sale page will be named /update-sale/. If your page has a parent or your WordPress was installed in a subdirectory you may have to add a path in front. Go to your Update Sale page and copy the relative portion of the URL (to the right of your domain name) from the address bar of your browser and replace /update-sale/ in the rlink attribute.
The first shortcode will create a form for deleting the selected SALE record. The second shortcode produces a form for updating the selected SALE record. Note that when you just view this page, you will only see the short codes because no SALE record will have been selected. To test the page, you need to reach the page from your Search Results page by clicking on one of the dates, selecting a SALE record.
Agent Bookings page
[abase table="SALE" select="AgentName, OfficeLocation, SUM(1) AS NumSales, SUM(Amount) AS Total" group="AgentName" order="Total DESC" columns="Agent^AgentName, Office^OfficeLocation, Number of Sales^NumSales, Total" center="NumSales,Total" echo="red"]
This shortcode will group records from the SALE table counting and summing the total sales for each agent and displaying the results ordered by the Totals in descending order.
Step 14. Test your forms
To test your forms, do the following:
Add yourself as an agent.
Add a $2,500 sale for yourself to Paris. See that it is added to the SALE table on the Database Tables page.
Modify the first sale (which should be $200 to Auckland) so that YOU are the agent. Also, change the amount to $2,200 (enter the digits only). Confirm the update.
Search for all the sales by Tom Brower. Delete the $320 trip to Moscow booked by Tom Brower. Then search Tom Brower's sales again and verify the record is gone.
Step 15. Remove the echo attribute on each short code
Do not do this last step until you are sure all your pages and forms work properly. The echo="red" attribute in each shortcode forces it to display. Remove all echo="red" attributes from the shortcodes do they are hidden. (You can leave them in on the Database Tables page.)
PART 7 - HTML Navigation
In this part we will add some content to the Campus Travel home page. We will also add a single-line navigation menu to each of our Campus Travel pages. We will code this single-line menu in HTML. Watch the video(7/7).
Step 16. Add a single-line menu to all of your pages using HTML code.
For each page, add a single-line navigation menu containing the main Campus Travel page, Add Agent, Agent Bookings, Add Sale and Search Sales. You do not need to include Database Tables. Make sure Database Tables is accessible from the WordPress menu under Campus Travel.
To make a link, you surround the text (that you want to become a link) with "<a href=...>...</a>" tags. The part before the text has the URL of the page you want the link to take you to.
For example, to create a link for the Add Agent page, you would type:
<a href="/add-agent/">Add Agent</a>
This assumes that the URL of the Add Agent page ends with "...http://richardh.bus311.org/wordpress//add-agent/".
Similarly, assuming the URL of the Search Sales page ends with "...http://richardh.bus311.org/wordpress//search-sales/", create a link by typing:
<a href="/search-sales/">Search Sales</a>
Since the URL of the Campus Travel home page ends with just a slash "...http://richardh.bus311.org/wordpress//", create the home link by typing:
IMPORTANT: If your WordPress is installed in a subdirectory, then you need to add the subdirectory path to the href="" attribute in the <a...> tag. If WordPress is installed in a directory named /wordpress/ then the above three examples become:
Once you have tested every link, copy this code to the bottom or top of every page, including Search Results and Update Sale.
Step 17. Add an introduction menu to the Campus Travel home page.
Using the single line menu you created and pasted on each of the pages, create a more descriptive menu for the Campus Travel introduction page that could be constructed something like:
<b>Welcome to the Campus Travel Intranet Website</b>
To add a new agent to the database, click <a href="/add-agent/">Add Agent</a>
To display a sales summary for each agent, click <a href="/agent-bookings/">Agent Bookings</a>
To add a new sale, click <a href="/add-sale/">Add Sale</a>
To search sales by agent, destination or amount, click <a href="/search-sales/">Search Sales</a>
You do not need to include Database Tables. Make sure Database Tables is accessible from the WordPress menu under Campus Travel.
Step 18. Remove same page links on menus.
Now, to finish the single-line menus, on any particular page the menu should not contain a link to that same page. For example, the menu you created in Step 16 should only appear as is on the Search Results and Update Sale pages, because those pages are not included in the menu.