php

jenny2000000

I308Lab12extended.pptx

Home >Information Systems homework help >php

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.

<html>

<body>

Band Title: <input type="text" name= “form_bandtitle” required><br>

Year Formed: <input type= "number" name= “form_yearformed"><br>

</form>

</body>

</html>

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

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.

<?php

$con = mysqli_connect("SERVER NAME","USER NAME","PASSWORD","DATABASE NAME");

// Check connection

if (mysqli_connect_errno())

{echo "Failed to connect to MySQL: " . mysqli_connect_error() . ”<br>"; }

else

{ echo "Established Database Connection <br>";}

//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)) . "<br>"; }

echo ”Band Added <br>";

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.pdf 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.

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.)

<form> to call albuminsert.php

<form> to call albumselect.php

MATERIAL

Dropdown Example: Lab13.PHP

Download lab13.php from Canvas files/resources. This file contains the week13.php that you created in lecture in Big Data.

This file is an example of how you incorporate the PHP into the page to create a dropdown list.

Important: The file type must be .php This will make sure that the PHP coding in the file is not displayed to the browser. If you do wish to see the PHP code, you can rename the file as .html and choose View Source.

Screen with data:

http://cgi.sice.indiana.edu/~drichert/phplabs/lab13.php

EXERCISE

Lab13.php

<!doctype html>

<html>

<body>

<h2>Select from Works_shift</h2>

<h3>Calls same week13.php as in Lecture</h3>

Order Date: <input type="date" name=“form-date" min="2003-01-02" required><br>

Role: <select name=‘form-role'>

<?php

$conn = mysqli_connect("db.soic.indiana.edu","i308_data","my+sql=i308_data","i308_dataset");

// Check connection

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

$result = mysqli_query($conn,"SELECT distinct role FROM emp_shift");

while ($row = mysqli_fetch_assoc($result)) {

unset($id, $name);

$id = $row[‘role'];

$name = $row['role'];

echo '<option value="'.$id.'">'.$name.'</option>';

}

</select>

</br>

Time of Order: <input type="time" name=“form-time" required><br>

</form>

</body>

</html>

<form> starts

<form> ends

Dropdown <select> starts

Dropdown <select> ends

Select where dropdown data comes from

Setting the variables from the select. Note the role is used both for what is displayed as name and passed to the PHP for ID.

This is where you build the dropdown values that are on the screen. The html coding needs to be in an echo since this completes the html tag for <select>

Notice that the PHP is in the middle of the html coding for creating the screen and can be done in multiple places.

EXERCISE

Confirm It Works

Connect to your php page from a web browser.

http://cgi.sice.indiana.edu/~USERNAME/lab13.php

Enter the fields for a selecting the emp_shift like in lecture.

Example day=2011-06-06 time=6:00pm role=busser

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 php file and PHP file are the same.

Refer to Canvas files/resources burrow.pdf for help in errors

EXERCISE