INFM 603 - Information Technology and Organizational Context

Fall 2016 – Assignment H7

The following tasks for Assignment H7 use the database that you have already constructed (for employees and projects) for Assignment 5 and also build on Assignment H6

 

What you’re going to do with H7 is to create a web form that takes the name of an employee from a drop-down list (a “select” input) and provides information from your database tables about that employee, including, for example, title, department, project(s) and supervisor(s).   I am not too worried about the details of what you provide but it should be grouped together even though it’s from multiple 3NF tables and it should be names rather than just ID numbers.   

   The other key to Assignment H7 is that you’re going to use JQuery and AJAX in order to create an interactive HTML page that makes it look like all this is being done without ever taking you away from your form, just by interacting with the page itself.    It is going to look at lot like the form to get information about pets shown in http://testuser2.psjconsulting.com/jquery-petform.html

 

 

1)      Copy the file from http://testuser2.psjconsulting.com/jquery-petform.html onto your computer.    You are going to edit this to create your own form that works with your own php scripts and database

2)      Note that this HTML file defines two JavaScript functions and each JavaScript function uses a PHP script.   JQuery JavaScript functions are loaded so these functions use the JQuery syntax.    You don’t have to understand all of this but it’s a shorthand way of using HTTP (and PHP) from JavaScript, so that JavaScript interacts with the Document Object Model (DOM) in the browser and you create the illusion that everything is happening right in front of you on your browser even though it’s constantly running code and getting data from the server.    This is how a lot of Web application features today really work. 

3)      The notation looks ugly, but you will notice things like $(‘#petname’) and $(‘#results’).   $ in JavaScript (not to be confused with $ in PHP!!!) is a shorthand way of saying something is handled by JQuery, which is a set of special JavaScript functions that you’ve loaded from Google.   JQuery uses some of the same notation as CSS, so “#” means the element with a certain id, “.” means the element with a certain class, etc.   So things like $(‘#petname’) just address places in your document (your page) where there is going to be input or output.    You are going to change the name from “petname” to something else because “petname” doesn’t make sense for what you are doing.   Then you are going to change the name of the two scripts, “getpetnames.php” (which just gets a list of pet names from a database) and “getpetinfo.php” (which gets all the info about a pet from the database) to something that makes sense for what you are doing.   While you’re at it, also change the name of petchoice to something that makes sense, since your user is choosing the name of an employee, not a pet.

4)      Go through the rest of the form and just keep changing the names of ids and functions so they make sense, including showpetinfo() and the message.    You can add additional beauty to the page if you like.   Make sure you leave the main elements like the <select> intact and have a <div> for your results – that is where the output from the database is going.     Normally I would have another <div id = error> in case something goes wrong so that an error can be displayed on the page, but we are keeping it simple.

5)      Once you are happy with your page, and double check that all your names are consistent, now you can write your two scripts.   The .load method in JQuery is actually using HTTP to transfer information to the web server via HTTP, and get back data from the web server via HTTP, kind of behind the scenes, so that it magically appears in the browser.    Theoretically this should be possible even if the HTML file (and the Javascript) is somewhere else but there are serious security reasons why it can only be allowed if the JavaScript is actually on the same server as the PHP script.   Nevertheless, as a user, you don’t really know this because it looks like it’s all happening in your browser.   So now you know that your HTML file is going to have to get uploaded to your virtual server, which you know how to do.

6)      The first script is going to go to your database and just get the names of the employees for your drop-down menu.   This PHP script opens the database, runs a query to get all the rows that have employee names, and “prints” the HTML with the <option> tags for the drop down.    I’m going to give you the whole code and all you have to do is change it so that it uses your database and tables:

<?php

          // Create connection

          $database = new mysqli("localhost", "testuser1", "*******", "testuser1_pets");

         // Check connection

       if ($database->connect_error) {

        die("Connection failed: " . $database->connect_error);

      }

          if ( !( $result = $database->query ( "SELECT Name FROM Pets WHERE 1") ) )

              print ("Warning!   could not execute query <br />" );

         while($row = $result->fetch_assoc()) {

              foreach ($row as $value) {

                   print ("<option>$value</option>");        

              }

          }

?>

 

You don’t necessarily have to understand everything, but remember that the result of every query is an array of arrays, like a matrix (or table!).    The variable $result is keeping that matrix and the method fetch_assoc() just says get the next row (until there are no more, because of the “while”).    Then the “foreach” is a special kind of for loop that assigns each value in the row to the variable $value.     You will also see “as $key => $value” which assigns the field name to $key and the value of that field in the record (row) to $value.     In this case, it’s a little confusing because each row in the answer has only one entry, the pet name, but I still used the same loop.  

7)                  Your second script runs after the user has selected an employee name and just takes the name of the employee and gets all the database info for that employee.     I can’t write this one for you because you have different database tables.   Basically go to all the tables you have and set up variables to hold the information you want to display from the database, then output a table with the results.     You can do a big join and just do one query, or you can do several queries and keep track of the ID’s (foreign keys) as you get them so that you can use them to do lookups in a different table.     Here’s what it looks like for the pet database:

<table width="639" height="66" border = "0" cellpadding = "0" cellspacing = "10">

   <tr>

     <td width="73" bgcolor = "#ffffaa">Name </td>

     <td width="72" bgcolor = "#ffffbb">Owner</td>

     <td width="160" bgcolor = "#ffffbb">Telephone</td>

     <td width="103" bgcolor = "#ffffbb">E-mail</td>

     <td width="68" bgcolor = "#ffffbb">Food </td>

     <td width="93" bgcolor = "#ffffcc">Breed</td>

   </tr>

 

   <tr>

 

<?php

 

           // Now get all the data

          // Create connection

     $database = new mysqli("localhost", "testuser1", "*******", "testuser1_pets");

         // Check connection

     if ($database->connect_error) {

              die("Connection failed: " . $database->connect_error);

          }

         $petname = $_REQUEST['petname'];

          // first query - get the data, ownerID and FoodID from the Pets table

          if ( !( $result = $database->query ( "SELECT Breed,Eats,OwnerID FROM Pets WHERE Name = '$petname'") ) )

              print ("Warning!   could not execute query <br />" );

         $row = $result->fetch_assoc();

          $foodid = $row["Eats"];

          $petbreed = $row["Breed"];

          $ownerid = $row["OwnerID"];

 

          // Now get the owner info (name, tel. and e-mail)

          if ( !( $result = $database->query ( "SELECT Name,TelNo,EMail FROM PetOwners WHERE OwnerID = '$ownerid'") ) )

              print ("Warning!   could not execute query <br />" );

         $row = $result->fetch_assoc();

          $ownername = $row["Name"];

          $ownertelno = $row["TelNo"];

          $owneremail = $row["EMail"];

 

          // Finally get the name of the food

          if ( !( $result = $database->query ( "SELECT Name FROM Petfoods WHERE ID = '$foodid'") ) )

              print ("Warning!   could not execute query <br />" );

         $row = $result->fetch_assoc();

          $petfood = $row["Name"];

          //

        // Now just print each form field’s value

          print( "<td>$petname</td>

                     <td>$ownername</td>

                     <td>$ownertelno</td>

                     <td>$owneremail</td>

                  <td>$petfood</td>

                  <td>$petbreed</td>" );

                    

           // and wrap up the table

           print("</tr></table><br><br>")

 

?>

 

.     Note that I didn’t even bother here to use the “foreach” but I took advantage of the associative array to grab each field using commands like $ownername= $row[“Name”] .   Look carefully also at the use of the variable $ownerid.   This is what’s done instead of the join.   We get the $ownerid as a foreign key from one table, then we use it to query against another table and get the rest of the information about that owner.

8)      At this point, you have three files, your simple HTML/JavaScript file, and your two PHP scripts, and you upload them all to the server and debug them.   Submit your three scripts and the link to your HTML file on your virtual server so that I can test it.

 

 

 


Last Updated: October 25, 2016