Lab 9

To make progress on Project 3 and to understand...

1. How to generate forms from database tables
2. How to implement form processing and validation
3. How to use "get" and "post" to control the "state" of a web application

1. You should be using the project3 folder from the previous lab.
2. If you do not already have a project3 folder download project3.zip and unzip.

1. In your project3 folder, open drop_courses_table.php
2. Rename the file drop_table.php
3. This script always drops your courses table. You will modify the script, so that you can specify which table to drop as a URL parameter
4. Below the require function, add the following:
   $table_name = $_GET['table_name'];
5. Where ever you see the word "courses" hard coded in a string, replace it by properly slicing in the variable $table_name
6. Note that the SQL query uses double quotes, so you can slice it as follows:
   $sql = "DROP TABLE $table_name";
7. Save, upload and test your script on the server
8. Note that the script now allows the table name to be passed via the URL:
   drop_table.php?table_name=ebreimer_courses
9. Use your new script to drop the courses table you created last lab. Remember this is userid_courses where userid is your userid.
We will improve this table in the next part.

1. While dropping a table is a generic task that can work for any table, creating a table is usually very specific since the table has very specific fields. In the part, we improve the courses table by using a variable to avoid redundancy and using comments that we can use to generate the form labels.
2. Rather than hard code the labels into a script, we can extract them from the database table itself.
3. In your project3 folder, open create_courses_table.php
4. Define a variable called table_name and set it to userid_courses where userid is your userid.
5. Where ever you see the word "courses" or "userid_courses" hard coded in a string, replace it by properly slicing in the variable $table_name
6. Change the sql string to the following:
   

   "CREATE TABLE $table_name (
     cid   INT         NOT NULL AUTO_INCREMENT, 
     sub   VARCHAR(4)  NOT NULL COMMENT 'Subject Area', 
     num   VARCHAR(3)  NOT NULL COMMENT 'Course Number', 
     title VARCHAR(48) NULL     COMMENT 'Course Title', 
     descr TEXT        NULL     COMMENT 'Course Description', 
     year  INT(4)      NULL     COMMENT 'Year Taken', 
     sem   VARCHAR(6)  NULL     COMMENT 'Semester Taken', 
     uid   VARCHAR(10) NOT NULL COMMENT 'Userid', 
     PRIMARY KEY (cid)
   )";
   			

7. Notice that we added comments for each of the 7 fields that the user can enter
8. Also, we changed the year to INT(4) which will internally display this integer value using 4 digits.
9. We will use the datatypes (VARCHAR, TEXT AND INT) and their lengths (4, 3, 48, etc.) to generate the proper form elements for entering data for each field.
10. Save, upload and test your script on the server
11. Use your new script to re-generate your courses table with the comments for each field.
12. We will improve use these comments in the next part.

1. In your project3 folder, create a new file called show_table_columns.php
2. Add PHP tags
3. Require functions.php
4. Create a variable called table_name and set it equal to a URL parameter with the same name, i.e., $_GET['table_name']
5. Connect to the database, run the following query SHOW FULL COLUMNS FROM $table_name and store the results in a variable called cols
6. Be sure to use double quotes for SQL queries
7. Set a variable called out for storing the output and set it to '<h1>Columns of '.$table_name.'</h1>';
8. Write a while loop to fetch each column as an associative array:
   while($col = $cols->fetch_assoc()) { }
9. Inside the while loop, write a foreach loop to iterate over each associative array element:
   foreach ($col as $key=>$value) { }
10. Inside the foreach loop, concatenate a list item to the out variable:
    $out .= '<li><b>'.$key.':</b> '.$value.'</li>';
11. Outside the foreach loop (but inside the while loop), concatenate the proper HTML tags to the out variable to create an unordered:
    Before the foreach loop: $out .= '<ol>';
    After the foreach loop: $out .= '</ol>';
12. At the very end of your script (outside the while loop), call the function to make a basic page and pass $table_name as the page name and $out as the page content.
13. Save, upload and test your script on the server
14. Be sure to pass the proper URL parameter, i.e., show_table_columns.php?table_name=userid_courses
15. It should generate a list of 9 attributes for each column of the table

1. In your project3 folder, open insert_course.php
2. Simplify the script by re-writing it as follows:
   
3. Be sure to replace ebreimer_courses with your userid
4. Note that this script uses two functions make_table and make_form that you can copy.
5. Save, upload and test your script on the server
6. You should be able to insert courses, but with only the subject, number and userid
7. Note that this script now has two states:
   1. The form is shown if any of the three variables are null. Remember null strings are interpreted as false
   2. The insert query is executed and the table is shown if all three of the variables are not null, i.e., if ($sub && $num && $uid)

1. The insert form is annoying because if the user forget to enter one of the three values, the form is regenerated without any previously entered values.
2. We can fix this by passing previously submitted values to the make_form function and then slicing them as the values of the input elements.
3. Input text elements have an optional value attribute that will display a value inside of the text box. For example, the following input element would have the value CSIS inserted:
   <input type="text" value="CSIS">
4. Modify the make_form function so that it take 3 parameters ($sub, $num, $uid) and sliced each of them value attribute of the appropriate input elements, i.e.,
   value="'.$sub.'"
5. Change the function call in the main program to pass the three parameters, i.e., $output = make_form($sub, $num, $uid);
6. Save, upload and test your script on the server

1. To see the most recently inserted course, we can sort the table in descending order based on course id (cid)
2. In the make_table function, change the select query to "SELECT * FROM $table_name ORDER BY cid DESC"
3. The form can be generated automatically from the courses table, but first we need to implement a helper function for create each form group.
4. Above the make_form function, define the following helper function:

   /* -----------------------------------------------------------------------
     Makes a form group from a given id, label and value
   ----------------------------------------------------------------------- */	
   function make_form_group($id, $label, $value) {
     return '
       <div class="form-group">
         <label for="'.$id.'">'.$label.'</label>
         <input type="text" class="form-control" id="'.$id.'" name="'.$id.'" value="'.$value.'">
       </div>		
     ';
   }	
   

5. Modify the make_form function as follows:
   
6. Read each of the following key points:
   • SHOW FULL COLUMNS returns all of the meta information about the columns including the field name (used as id) and the comment (used as label)
   • The column meta data is passed as an associative array where $col['Field'] is the field name and $col['Comment'] is the custom comment we added when we created the table.
   • This function can now create a basic user input form for any table where the comment indicates the descriptive label of the field.
   • The form uses method="post" so that the submitted data is not in the URL. Instead it is in the message body of the HTTP request.
   • The submitted value can be accessed by passing the $_POST array to this function.
   • The $_POST array is an associative array where the array indices are the names of the form elements. By using the field names as both the id and name of the form elements, we can retrieve the submitted values using the field names
7. Rather than hard code the field names and values into the query (i.e., INSERT INTO $table_name (sub, num, uid) VALUES ('$sub', '$num', '$uid')), we will generate the field names and values and simplify the query as follows: INSERT INTO $table_name ($fields) VALUES ($values)
8. Modify your script as as follows:
   
9. Read each of the following key points as you will be quizzed in lecture about this code:
   • $_POST is an associative array that contains the values of any named form element
   • The index/key of each $_POST element is the name of the form element, i.e, the value of the name attribute
   • Since the name of each form element matches the field names of the database table, we can use them to generate the list of field names.
   • We loop over the $_POST array and if a value is not null we add the key to the list of fields and the value to the list of values.
   • If a value is ever null, we set valid to false and break out of the loop.
   • We have to trim off the last comma of the field and value lists so that the query syntax will be correct
   • $fields equals the string sub,num,title,descr,year,sem,uid and $value would be of the form 'CSIS', '120', ..., 'user1'
   • If a table had 50 fields, this code would generate a query for handling 50 fields.
10. Save, upload and test your script on the server