Campus Travel WordPress Assignment

Each student has their own WordPress website to submit this assignment. A playlist of 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.

In this assignment, you will import the spreadsheet into an online database using this SQL script.

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.

  1. Campus Travel - Introduction menu and home page.
  2. Database Tables - displays the agents and sales so you can confirm your database import and changes.
  3. Add Sale - form for adding a new sale by an agent, to a destination, for an amount.
  4. Search Sales - form for searching for sales by agent, destination or amount range.
  5. Search Results - page for displaying the results of a sales search.
  6. Update Sale - form for updating or deleting a particular sale.
  7. Add Agent - form for adding a new agent.
  8. 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.

  1. In the Dashboard, under Appearance, click Customize.
  2. Under Static Front Page set the Front page displays to A static page, and select Campus Travel as the Front page.
  3. 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.

  • Campus Travel
    • Add Agent
    • Agent Bookings
    • Add Sale
    • Search Sales
    • Database Tables
  • About Me

To accomplish this,

  1. In the Dashboard, under Appearance, click Menus.
  2. 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.
  3. 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.
  4. With your mouse, click and drag the pages so they appear in order and indented as shown above.
  5. Click the button to Save (or Create) the Menu.
  6. 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,

  1. In the Dashboard, under Settings, click Permalinks.
  2. On the Permalinks Settings form, set Common Settings to Post name.
  3. 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,

  1. In the Dashboard, click Pages on the left.
  2. Click the check box to the left of Title above the pages, to select all the pages.
  3. Click down Bulk Actions above and select Edit. Click Apply.
  4. For the Template drop-down menu, select Full-width Page if available.
  5. For Comments, select Do not allow. Click the Update button.

You can confirm this operation by examining your pages.

PART 2 - Creating a New MySQL Database


Now we will create a MySQL database to import the tables into. Watch the video(3/7).

Step 6. Create the database.

Before importing the CSV files we must create the database and give the WordPress user access rights. To do so,

  1. Log into cPanel.
  2. Find the MySQL Databases icon in the Databases section and click on it.
  3. 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.
  4. 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.
  5. 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 3 - phpMyAdmin: Importing SQL Database into MySQL


Now we will import the SQL database into MySQL.

Step 7. Import database.

We will use phpMyAdmin to import the database.

  1. Log into cPanel.
  2. Find the phpMyAdmin icon in the Databases section and click on it.
  3. 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.
  4. Click the SQL tab at the top.
  5. Copy this SQL script into the text box and click Go.
  6. Confirm the four tables have been added.
  7. Close phpMyAdmin. Return to cPanel
  8. In cPanel, double-click on the phpMyAdmin icon to re-open it.
  9. Click the plus (+) sign next to your username on the left to see the list of databases.
  10. Click on the _CAMPUS database. Now you should see the list of tables properly named.

 

PART 4 - 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 8. Install ABASE plugin

The ABASE plugin is used to interface your MySQL database with your WordPress pages. To install the plugin,

  1. Download the abase plugin here.
  2. In the Dashboard, on the left, click Plugins.
  3. Click Add New at the top next to the Plugins heading.
  4. Click the Upload Plugin button at the top. Browse to find the abase.zip file you just downloaded.
  5. Click to Install ABASE.
  6. Once installed, click Activate.
  7. Click to return to the Plugins page.
  8. To Setup ABASE, click ABASE Settings on the Plugins page, or Click Settings on the left, and click ABASE for MySQL.
  9. For Texturization, make sure the Disable wptexturize checkbox is checked.
  10. Under Database Settings, enter the name of your database, in this case, CAMPUS.
  11. Click Save Changes at the bottom.

ABASE should now be installed and configured.

Step 9. 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

  1. [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.
  2. [abase table="AGENT" echo="red"]
  3. [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.

Add Agent page

  • [abase form="insert,1" table="AGENT" elements="AgentName, OfficeKey" columns="Name^AgentName, Office^OfficeKey$INSERT" ack="green" echo="red"]

This shortcode will create a form to add a new agent to the AGENT table.

Add Sale page

  • [abase form="insert,1" table="SALE" elements="SaleDate, AgentKey, Amount, DestinationKey" columns="Date^SaleDate, Agent^AgentKey|AgentName, Amount, Destination^DestinationKey$INSERT" ack="green" echo="red"]

This shortcode will create a form to add a new sale to the SALE table.

Search Sales page

  • [abase form="search,1,/search-results/" table="SALE" elements="AgentKey, DestinationKey,Amount" columns="AgentKey, DestinationKey, Amounts Above^Amount>=LowAmount, Amounts Below^Amount<=HighAmount$SEARCH" echo="red"]

This shortcode will add a form to search the SALE table. You can search by Agent, Destination or a Sale Amount range.

This shortcode might not work unless you have setup all your pages and tables using the exact right names.
Also, newer versions of WordPress do not allow angle brackets within shortcodes (">" and "<") so they must be replaced with their equivalent HTML character codes. Use "&gt;" for ">" and use "&lt;" for "<"


An explanation of the attributes is a follows:

Attribute Explanation
form="search,1,/search-results/" HTML form for performing a database search. 1 shortcode specifies the whole form. The relative address of the search results page is "/search-results/". Confirm that the relative web address of your search results page matches what is specified in your shortcode. If they are different, change the shortcode, not the web link address of the page.
table="SALE" The form will search the table named "SALE". Note that the case must be exactly correct. If your table name is different, change the shortcodes.
elements="AgentKey, DestinationKey, Amount" Three fields from the SALE table (AgentKey, DestinationKey and Amount) will be used to specify the search. These fields must also appear in the column specification.
columns="AgentKey, DestinationKey, Amounts Above^Amount&gt;=LowAmount, Amounts Below^Amount&lt;=HighAmount$SEARCH" Four elements will appear in the form: AgentKey, DestinationKey and two for Amount. Since all three fields are also listed in the elements attribute, they will all appear as search form elements (rather than values from a search record).

Both AgentKey and DestinationKey appear as drop-down menus because they are foreign keys into tables specifying a corresponding name field.

The first search Amount field is titled "Amounts Above" and will be named LowAmount, and generate a search criteria term to search for
Amount >= LowAmount (entered).
Notice "&gt=" is used to represent the ">=" operator.

The second search Amount field is titled "Amounts Below" and will be named HighAmount, and generate a search criteria term to search for
Amount <= HighAmount (entered).
Notice "&lt=" is used to represent the "<=" operator.

A form search button titled "SEARCH" immediately follows the HighAmount text box.
echo="red" Display the shortcode above the form in red. This is for debug purposes. When your shortcode works properly you can remove this attribute completely.


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.


Search Results page

  • [abase table="SALE" columns="Date^SaleDate!'n/j/y', Amount, Destination^DestinationName, Agent^AgentName, Office^OfficeLocation" order="SaleDate DESC" style="width:700px;" rlink="SaleDate, /update-sale/" ack="green" echo="red"]

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.


Update Sale page

  • [abase form="4" table="SALE" columns="Date^SaleDate, Agent^AgentName, Amount, Destination^DestinationName$DELETE" echo="red"]
  • [abase form="update,1" table="SALE" elements="SaleDate, AgentKey,Amount, DestinationKey" fields="Date^SaleDate, Agent^AgentKey, Amount, Destination^DestinationKey$UPDATE" ack="green" echo="red"]

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 10. Test your forms

To test your forms, do the following:

  1. Add yourself as an agent.
  2. Add a $2,500 sale for yourself to Paris. See that it is added to the SALE table on the Database Tables page.
  3. 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.
  4. 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 11. 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 5 - 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 12. 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:

     
<a href="/">Home</a>

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:

     
<a href="/wordpress/add-agent/">Add Agent</a>


     
<a href="/wordpress/search-sales/">Search Sales</a>


     
<a href="/wordpress/">Home</a>

So, to begin, create a single-line menu on the Campus Travel home page with every menu item linked.

<a href="/">Home</a> |
<a href="/add-agent/">Add Agent</a> |
<a href="/agent-bookings/">Agent Bookings</a> |
<a href="/add-sale/">Add Sale</a> |
<a href="/search-sales/">Search Sales</a>

The menu should look similar to:

      Home | Add Agent | Agent Bookings | Add Sale | Search Sales

Save the page. View the page and TEST EVERY LINK.

Once you have tested every link, copy this code to the bottom or top of every page, including Search Results and Update Sale.

Step 13. 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 14. 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 12 should only appear as is on the Search Results and Update Sale pages, because those pages are not included in the menu.

The following menu would be on the Add Sale page:

      Home | Add Agent | Agent Bookings | Add Sale | Search Sales

Where "Add Sale" is not a link.

To accomplish this:

  1. Go back to each page (except Search Results and Update Sale) and remove the link around that page in the menu. For example, for the Campus Travel home page, remove the link "a" tags around "Home":

    Home |
    <a href="/add-agent/">Add Agent</a> |
    <a href="/agent-bookings/">Agent Bookings</a> |
    <a href="/add-sale/">Add Sale</a> |
    <a href="/search-sales/">Search Sales</a>
  2. The menu on the Add Agent page would be:

    <a href="/">Home</a> |
    Add Agent |
    <a href="/agent-bookings/">Agent Bookings</a> |
    <a href="/add-sale/">Add Sale</a> |
    <a href="/search-sales/">Search Sales</a>
  3. Finish removing the menu links for the Agent Bookings, Add Sale and Search Sales pages.

Make sure you confirm all your links function properly.

That completes our Campus Travel assignment.