INFO I308
Week 12 Extended Lab
Important Note
Because of the constraints of going on line, we have combined labs 12 and 13 into a single lab 12.
Slides 3 through 12 are the original lab 12, and you can earn all 50 points for this assignment by completing just this part.
The remaining slides allow you to:
Practice validating and sanitizing input data as shown in the Week 13 lecture.
Learn how to use a dropdown to limit the choices for input data.
You can receive up to 10 points of extra credit for completing each of these exercises. (Total of 20 points extra credit)
ADMINISTRATIVE
Before You Start
This lab would normally build on the tables you created in lab 10. However, so that all students use the same data sets, you should start by running the scripts in the file new_artist_band_etc in the Files/Resources folder on Canvas.
You may run this script in whatever way you feel comfortable with. You can go through PHPAdmin as you learned in lab 10, or you can simply copy and paste the code into MySQL. If you do the latter, you must do it in steps rather than try to dump all the code at once. There is a limit to the amount of data in cut/paste.
Run the scripts in your Individual database only!
Note:
In this script we have named the tables Artist12, Band12, etc. because you should already have the original Artist, Band, etc. from Lab 10, and we do not want you to overwrite them.
This will be very useful practice to help with creating and populating your database in the Team Project!
ADMINISTRATIVE
PHP Resources
PHP manual for issues:
http://php.net/manual/en/
Inserting Data into a Database Table:
Use the coding provided in lecture and lab
http://www.w3schools.com/php/php_mysql_insert.asp
Selecting Data from a Database
http://www.w3schools.com/php/php_mysql_select.asp
Note: Make sure you follow the procedural examples, not the object-oriented!
ADMINISTRATIVE
Submitting Your Basic Assignment
Note: In slides 7 to 12 of this lab we will show you how to do the basic exercises for the Band12 table. Your assignment is to do the same thing for the Artist12 table. Do not submit the Band forms that are in this lab example!
Upload the following to Canvas:
A text file (.txt) or Comment containing the url to the webpage for your artist.html.
Example URL: http://cgi.soic.indiana.edu/~USERNAME/artist.html
The artist.html file you wrote to create the user interface for data entry.
Your PHP file(s). You can either put these all in a single file called insertartist.php or you can create and upload separate files insertartist.php and selectartist.php.
Once you have submitted, check that the link to your page opens correctly from Canvas. If they cannot open it from the computer they are using, the graders will not be able to grade it and you will receive a grade of 0.
Submission
Completing Your Assignment
For this lab you will create a web-based front end for INSERT and SELECT queries for the Artist12 table. Use the Band example in slides 7-12 to help create the files.
INSERT (20 points)
Create a webpage that includes a form for data entry for the Artist INSERT query. To do so:
Create an HTML file to create the user interface for data entry and to call the PHP file.
Create a PHP file that includes commands to connect to the database and run the MySQL queries
SELECT (30 points)
Create a form to do data retrieval for the SELECT query. You can do the SELECT part on the same page as the INSERT, or you can create a separate button and .php file. See slide 12 for example outputs.
Output must be formatted: Full Name, DOB like “Jan 1st, 2000”, and Hometown. Order by last name. Hint: use aliases for the field names in your SELECT to have good column names.
Do not cut and paste from the PowerPoint slides! You should know by now that PowerPoint does not use the same characters and you will have errors.
Upload the HTML and PHP files to your cgi-pub directory.
Submission
Example Using the Band12 Table
The following slides are here to show you how to create files for the Band12 table. This is to help you understand how it works before you create similar files for the Artist12 table for submission.
While the Band files are not to be submitted, you need to create them by working through the example so that you can enter data easily for this and the next labs.
EXERCISE
Step 1: Create the HTML Page “band.html”
Create the HTML file exactly as shown. Output will look like the lower right screen.
EXERCISE
PHP Database Connection
How to connect to your individual database in PHP:
$con = mysqli_connect(“SERVER NAME”,”USER NAME”,”PASSWORD”,”DATABASE NAME”);
($con is the variable used in php to store the information. It does not have to be named $con.)
SERVER NAME: dbHost
USER NAME: dbUsername
PASSWORD: dbPassword
DATABASE NAME: dbDatabase
The db values can be found on the Canvas page since they are the same. Pick the correct database (Individual) to use for the Lab.
Example:
$con = mysqli_connect(“db.sice.indiana.edu”,”i308f19_drichert”,”my+sql=i308f19_drichert”,”i308f19_drichert”)
EXERCISE
9
Step 2: Create the “insertband.php” File
Create the file as shown to insert data into band table. Do not copy and paste from the slide. Replace the connection info.
“; }
else
{ echo “Established Database Connection
“;}
//escape variables for security sql injection
$sanbtitle = mysqli_real_escape_string($con, $_POST[‘form_bandtitle’]);
$sanyearf = mysqli_real_escape_string($con, $_POST[‘form_yearformed’]);
//Insert query to insert form data into the band table
$sql = “INSERT INTO band12 (title, year_formed) VALUES (‘$sanbtitle’,’$sanyearf’)”;
//check for error on insert
if (!mysqli_query($con,$sql))
{ die(‘Error: ‘ . mysqli_error($con)) . “
“; }
echo ”Band Added
“;
mysqli_close($con);
?>
EXERCISE
Confirm It Works
Connect to your html page from a web browser.
http://cgi.sice.indiana.edu/~USERNAME/band.html
Enter the fields for a new band.
Use MySQL to verify that the record was inserted.
Debugging Hints:
If you have issues, debug by putting in one piece of coding and use “echo” statements in to see if it works.
Verify that the MySQL database table name and columns are the same as in the PHP file.
Also make sure that the variable names between the html file and PHP file are the same.
Refer to burrow on Canvas files/resources for a complete list of ways to debug your coding on the CGI Server.
EXERCISE
Step 3: Add the Option to Select the Data
Select all records from the Band12 table. Show title and year formed. Order by year formed.
This can be done as a separate button and PHP file (selectband.php), or you can add the code to insertband.php so that it simply displays the data after adding the new band.
Refer to the link in the PHP manual slide (2) for how to create. Use the Example (MySQLi Procedural) for the creation but refer to the other examples for table and ordered list output.
http://www.w3schools.com/php/php_mysql_select.asp
Click on this link to see both options:
http://cgi.soic.indiana.edu/~drichert/phplabs/band12.html
EXERCISE
Submitting the Extra Credit
If you are attempting the extra credit (total 20 points), you should upload the following to Canvas in addition to the files listed on slide 14 that are required for the basic assignment.
The url to the webpage for your album.php should be included in your .txt file or Comment.
Example URL: http://cgi.soic.indiana.edu/~USERNAME/album.php
The album.php file you wrote to create the user interface for data entry
The PHP files insertalbum.php and selectalbum.php
The example on these slides is for you to practice with before doing your Album files for submission.
Once you have submitted, check that the link to your page opens correctly from Canvas. If they cannot open it from the computer they are using, the graders will not be able to grade it and you will receive no extra credit.
Submission
Completing the Extra Credit
To earn the extra credit, you will create a PHP web-based front end for INSERT and SELECT queries for the Album12 table. Use the example lab13.php to help create the files.
album.php
Create a php file that includes a form for data entry for the album table. To do so:
Create a php file to create the user interface for data entry and to call the PHP files.
The PHP files should include the validation and sanitization listed in the requirements.
insertalbum.php, and selectalbum.php
Create the PHP files to do data insert and select. Make sure you create separate forms for submission.
Do not cut and paste from the PowerPoint slides! You should know by now that PowerPoint does not use the same characters and you will have errors.
Submission
Create the Webpage for Album
Create the PHP file for the Album12 table in your database. See slide 17 for an example of how the page should look.
Required validation and sanitization of the fields (10 Points):
All fields are required.
Title should be text and sanitized. Max value should be set to the size of the column in the database, and the display size should be 50 characters.
Published year should be a number field that only allows values from 1900-2025
Publisher should be text and sanitized. Max value should be set to your database size and the display size should be 50 characters.
MATERIAL
Create the Webpage for Album
Add dropdowns to your page as follows (10 Points):
Band should be a dropdown list that is populated from the Band12 table. The list should only show the band name, and it should be ordered by name. Only the band id is passed to the Insert PHP.
Media should be a dropdown list. Here you can hardcode the possible values.
MATERIAL
Assignment: The form will look like this
Example: http://cgi.sice.indiana.edu/~drichert/phplabs/album12.php
(Note: Buttons are not intended to work in this example.)