Eric Clark CET4429 Group 18 & CET4584 Group 22 Project 

 

This project was a joint project with my CET4584 class:

 

 

CET4584 & CET 4429 Group Project Assignment 1

 

 

---

 

 

CET4584 & CET 4429 Group Project Assignment 2

 

 

Project Notes:

The client was pleased with the current progress. I have demonstrated the basic Functionality of our site through this DEMO site

The current progress is to revise the search forms and create reports based on the information contained in the database. 

Next Steps:

• Enter more records into the database for testing.
• Generate the database reports.
• Create the webpage layout

 

---

 

 

Project Documentation

 

As a student I have always wanted an easy way to search through the list of Majors and Courses. While most of this information is available through PDF files, there is no easy way to see the Major's information and the classes associated. The course names often lead one to wonder what the course description and prerequisite information are necessary. Searching through PDF files for related information can be time consuming. Why not have this information available on a web site and searchable. This  was the goal for my project. I chose to use the Information Systems Technology (B.S.) and the Psychology (B.S.) degrees to demonstrate this for my project.

 

      For this project I learned and developed with several technologies. I began with a simplistic PHP search form using MSSQL Full-Text Indexing. This was used to test the database and the tracking of information. The forms give the user a quick way to find the courses and/or Majors that the are interested in. I built a sketch of each Major/Course information page using PHP MSSQL generated pages. This was a very basic page with CSS formatting. I was not happy with the performance and the amount of pages that would be necessary to generate. I decided to use PHP and MSSQL to generate an XML file with all of the Major/Course information dynamically generated. This was a cleaner and faster method of  generating the data.

 

 

     To accomplish this task I used a MSSQL built-in function FOR XML AUTO. This function automatically generates an XML formatted SQL return. For example, the SQL statement:

SELECT * FROM Classes WHERE ClassName = 'Web Systems II'  FOR XML AUTO;

Returns:

 

 

<Classes ClassID="4" ClassName="Web Systems II" ClassNum="4584" ClassDept="CET" ClassCrHr="3" ClassDesc="Continuation of Web Systems I Advanced web design concentration on use of current technology (CGI, JAVA, XML, DHTML) to provide interactivity." ClassAvailiblity="Spring" />

 

Using SQL calls like the one above I was able to dynamically generate the XML data for each item that I needed.

The pages themselves are coded in PHP using the sqlsrv module. I would use database calls like the following:

 

$tsql = "select * from Classes where ClassID = " . $classID . " for xml auto"; 

$stmt = sqlsrv_query( $conn, $tsql); 

if( $stmt === false) 

{ 

    echo '<error message="Error in query preparation/execution getting Class information: ' . $tsql . '"> \n</error>'; 

     die(); 

}   

 

// Start XML data 

echo "<Courses>\n"; 

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) 

{ 

   echo $row['XML_F52E2B61-18A1-11d1-B105-00805F49916B']; 

} 

// End XML data 

echo "\n</Courses>\n";

 

To format this XML file in a human friendly format I used an XSLT style-sheet.  Since this project was based on a section of the UCF catalog I used the UCF home page as a template for the Header, Footer, Background, and text formatting. To display the XML content I used XPATH statements such as the following:

 

<xsl:for-each select="/Courses/Classes"> 

    <h1><xsl:value-of select="@ClassName" /></h1>

 <table>

     <tr> 

          <td> 

                <h3>Class Number:</h3><xsl:value-of select="@ClassDept" /><xsl:value-of select="@ClassNum" /> 

           </td> 

     </tr> 

<!-- Page content continues -->

 

This would display the XML attribute information based on the MSSQL created XML. Using this I was also able to dynamically address any error messages that may be generated (such as no class information available or SQL errors. To achieve this I coded PHP to output errors within an XML formatted line.  The XML would look like: 

 

<?xml version="1.0" encoding="utf-8"?> 

<?xml-stylesheet type="text/xsl" href="class.xsl"?> 

<error message="No Class Information Found for : 'CGS 2100C'">\n</error>

 

 To display this information to the user I used the following XSLT/XPath statement: 

 

<xsl:if test="/error" > 

        <xsl:for-each select="/error"> 

                <h2><xsl:value-of select="@message" /></h2> 

        </xsl:for-each> 

</xsl:if>

 

 

 

After these changes my Majors/Courses page design was reduced to four files, two XSLT style-sheets and two PHP pages. I used PHP POST and GET functions to pass information from the forms or links to the page itself. The URL contains the information about what the user requested and uses this information to build the SQL queries and the XML data. The links would look like the following:

http://ucf.orionblue.net/xml/class.php?classnum='CET 4429' 

 

Web browser/server would URL encode this information into the following string:

http://ucf.orionblue.net/xml/class.php?classnum=%27CET%204429%27

 

Even though both of these links are valid this  proved to be a problem for my pages. At first I was using the entire class name to identify the class and generate the page. Most courses would work however courses such as Systems Application in C/C++ contain invalid characters (+) . The plus (+) symbol is a reserved character in URL rendering it is used on pages like many of those in this PBWiki site to allow pages with spaces in the name to have qualified URL addresses.  To solve this issue I was able to use the course department and course number which I was tracking separately to generate the pages. The page generation logic used both of these columns to uniquely identify the requested information without the user needing to know the table primary-key identity.

 

Each degree has its own unique layout. Majors like Psychology have their electives broken down into subcategories (Applied, Experimental, etc.) to recreate this I used a few dynamic areas in the XSLT/XPath/PHP statements. Before I could start to work on the web pages this design issue needed to be resolved. Some courses belong to many Majors (College Algebra for example) and each course may be a different type in a given Major (Elective in one Required in another). To solve this issue my design includes a Requirements table.The Requirements table is used to link each course to a given Major and give the ClassType information such as Elective and the ElectiveType (if applicable) such as Experimental.And by using XSLT if statements <xsl:if test="/Degrees/Electives/ElectiveType7"> I could display the correct class in the correct heading (Experimental Electives).

 

 

I have my Demonstration video and Screenshots on the following page http://ucf.orionblue.net/