Handout #5 - CSCI 2442

01 - Create a new directory called handout5 under the htdocs directory. This directory will contain seven files called index.htm, songs.csv, add.php, display1.php, display2.php, page2.php and remove.php. The index.htm file should have a heading containing the text "Handout 5 by YOUR NAME" followed by two radio button sections and a submit button. The first radio button section should have a heading of "Database Tasks" and contain three entries: Display Database, Add Database Entry and Delete Database Entry. The name for all three buttons should be "task" while the value and id statements should be "show", "add" and "delete". The show option should be checked by default. The second radio button section should have a heading of "Display Options" followed by six entries: Songs - Ascending, Songs - Descending, Artist - Ascending, Artist - Descending, Album - Ascending and Album - Descending. The name of all six buttons should be display while the id and value options should be "songa", "songd", "artista", "artistd", "albuma" and "albumd". The songa option should be checked by default. The entire form should direct the results to page2.php using the post method.

02 - Create the songs.csv file using the same format as demonstrated in class. This file will have five fields in the following order: id, song, artist, album and released. Populate the fields with five different entries containing music you enjoy. Enter 1 through 5 for the id field and just the year the song was released in the released field. then save the file. Start up the phpmyadmin tool and create a new database called mymusic. Inside this database, create a table called music which contains the five fields mentioned above. Set the id to type int with a size of 5, plus set it as the primary key and turn on the autoincrement option. Set the song, artist and album fields to type varchar with a size of 40 and set the released field to type int with a size of 5. Use the import option as demonstrated in class to import the songs.csv file and verify it was imported correctly. (Note; The database should be password protected using the value of qwerty as the password.)

03 - Inside the page2.php file, create two variables called task and display and set them equal to the values passed from index.htm. Set up nested if statements to test the status of the mysql_connect() and mysql_select_db() functions and give informative die() statements if either statement returns a false value. If both values evaluate as true, create an if/else if structure testing if the variable task is set to show, add or delete. Add comments to clearly identify each section. If the show option was selected, create a switch statement testing the six possible values of the display variable. Each case statement should contain four lines of code. The first line should set the variable query to a string which will be passed to the mysql_query function. The string should use the select function to perform the desired task. For the songa option, the string should select all records and order them by song in ascending order. See the classroom examples. The second line should be a print statement which state how the records were sorted. The third line should use an include statement to read in the code from display1.php while the fourth line should be a break statement. Outside of the switch statement, place a link back to the index.htm file. The add and delete options will be described in steps 5 and 6 below.

04 - Create the file called display1.php. After the opening php tag, create a variable called result which is equal to mysql_query($query). This should be followed by print statements which create a tabel and define the first row. Using th tags in the first row, label the columns of the table "Song Title", "Artist", "Album" and "Release Year". Next, use a while statement containing the mysql_fetch_array() function to print out each record using the method demonstated in class. Outside of the while structure, insert a closing tabel tag, then close the php script.

05 - Back in the page2.php file, go to the section where the variable task is set to "add". Copy the entire switch statement used in the step 3 to this section. Outside of the switch statement, create a heading with the label "Enter Title, Artist, Album and Release Year". This should be followed bya form tag which uses the post method and redirects to the add.php file. Create 4 text input entries with the names title, artist, album and year followed by a submit input button. Close off the form tag to end this section.

06 - Inside the section where the variable task is set to delete, add a heading with the text "Select one record to delete". This should be followed by a form tag which uses the post method and sends the results to a file called remove.php. Next, copy the switch syntax used in show and add section but replace all occurances of display1.php with display2.php. Outside of the switch statement, place a submit button and close the form tag. Also make sure that all three options will execute a mysql_close() function to cleanly close the database.

07 - The file display2.php will be very similar to the display1.php file, except it will contain five columns instead of four. Columns one through four will be the same as in display1.php. The fifth column should be labeled "Delete ?" in the th section and will include a radio button in the body of the table. Inside the while statement, create a variable called recordid and set it equal to the current value of id for that record. Inside the fifth td tag, create a radio button with the name "delete". Set the id and value options equal to the recordid variable.

08 - Open the add.php file and create four variables for the values passed to it from the page2.php file. Create a nested if structure similar to the one in page2.php to test the fuctions mysql_connect() and mysql_selct_db(). Create a variable query containing a string to insert the 4 values into the table called music within the mymusic database. Create an if statement to test if the mysql_query function ran successfully. If it ran successfully, print out the message "Successfully added the entry for:" followed by a listing of the title, artist, album and release year. If the mysql_query function was not sucessfuly, print out the message "Unable to add an entry for:" followed by a listing of the title, artist, album and release year. Run the mysql_close() function, then include a link back to index.htm.

09 - Open the remove.php file and create a variable called deleterecord containing the record passed to it from page2.php. After the standard nested if structure, create a query using the delete option to remove the record specified. Make sure to include the "LIMIT 1" option. If the mysql_query command is successful, print out the message "Successfully deleted entry", otherwise print the message "Unable to delete entry". Run the mysql_close() function, then include a link back to the index.htm file.

10 - Add any CSS positioning, color and font options as desired to make each page easier to read.

11 - Test out your code by first displaying the 5 entries from the songs.csv file. Take a screen shot of the results. Next, add an entry to the database and get a screen capture of the page with the "Successfully added the entry for" message. Next, use the delete option to remove the record you just entered. Get a screen capture of the "Successfully deleted entry" message. urn in the local source code of page2.php, add.php and remove.php along with the screenshots. Be prepared to demonstrate the results in class.

A working example of this code can be found HERE










		Handout #5 - CSCI 2442 01 - Create a new directory called handout5 under the htdocs directory. This directory will contain seven files called index.htm, songs.csv, add.php, display1.php, display2.php, page2.php and remove.php. The index.htm file should have a heading containing the text "Handout 5 by YOUR NAME" followed by two radio button sections and a submit button. The first radio button section should have a heading of "Database Tasks" and contain three entries: Display Database, Add Database Entry and Delete Database Entry. The name for all three buttons should be "task" while the value and id statements should be "show", "add" and "delete". The show option should be checked by default. The second radio button section should have a heading of "Display Options" followed by six entries: Songs - Ascending, Songs - Descending, Artist - Ascending, Artist - Descending, Album - Ascending and Album - Descending. The name of all six buttons should be display while the id and value options should be "songa", "songd", "artista", "artistd", "albuma" and "albumd". The songa option should be checked by default. The entire form should direct the results to page2.php using the post method. 02 - Create the songs.csv file using the same format as demonstrated in class. This file will have five fields in the following order: id, song, artist, album and released. Populate the fields with five different entries containing music you enjoy. Enter 1 through 5 for the id field and just the year the song was released in the released field. then save the file. Start up the phpmyadmin tool and create a new database called mymusic. Inside this database, create a table called music which contains the five fields mentioned above. Set the id to type int with a size of 5, plus set it as the primary key and turn on the autoincrement option. Set the song, artist and album fields to type varchar with a size of 40 and set the released field to type int with a size of 5. Use the import option as demonstrated in class to import the songs.csv file and verify it was imported correctly. (Note; The database should be password protected using the value of qwerty as the password.) 03 - Inside the page2.php file, create two variables called task and display and set them equal to the values passed from index.htm. Set up nested if statements to test the status of the mysql_connect() and mysql_select_db() functions and give informative die() statements if either statement returns a false value. If both values evaluate as true, create an if/else if structure testing if the variable task is set to show, add or delete. Add comments to clearly identify each section. If the show option was selected, create a switch statement testing the six possible values of the display variable. Each case statement should contain four lines of code. The first line should set the variable query to a string which will be passed to the mysql_query function. The string should use the select function to perform the desired task. For the songa option, the string should select all records and order them by song in ascending order. See the classroom examples. The second line should be a print statement which state how the records were sorted. The third line should use an include statement to read in the code from display1.php while the fourth line should be a break statement. Outside of the switch statement, place a link back to the index.htm file. The add and delete options will be described in steps 5 and 6 below. 04 - Create the file called display1.php. After the opening php tag, create a variable called result which is equal to mysql_query($query). This should be followed by print statements which create a tabel and define the first row. Using th tags in the first row, label the columns of the table "Song Title", "Artist", "Album" and "Release Year". Next, use a while statement containing the mysql_fetch_array() function to print out each record using the method demonstated in class. Outside of the while structure, insert a closing tabel tag, then close the php script. 05 - Back in the page2.php file, go to the section where the variable task is set to "add". Copy the entire switch statement used in the step 3 to this section. Outside of the switch statement, create a heading with the label "Enter Title, Artist, Album and Release Year". This should be followed bya form tag which uses the post method and redirects to the add.php file. Create 4 text input entries with the names title, artist, album and year followed by a submit input button. Close off the form tag to end this section. 06 - Inside the section where the variable task is set to delete, add a heading with the text "Select one record to delete". This should be followed by a form tag which uses the post method and sends the results to a file called remove.php. Next, copy the switch syntax used in show and add section but replace all occurances of display1.php with display2.php. Outside of the switch statement, place a submit button and close the form tag. Also make sure that all three options will execute a mysql_close() function to cleanly close the database. 07 - The file display2.php will be very similar to the display1.php file, except it will contain five columns instead of four. Columns one through four will be the same as in display1.php. The fifth column should be labeled "Delete ?" in the th section and will include a radio button in the body of the table. Inside the while statement, create a variable called recordid and set it equal to the current value of id for that record. Inside the fifth td tag, create a radio button with the name "delete". Set the id and value options equal to the recordid variable. 08 - Open the add.php file and create four variables for the values passed to it from the page2.php file. Create a nested if structure similar to the one in page2.php to test the fuctions mysql_connect() and mysql_selct_db(). Create a variable query containing a string to insert the 4 values into the table called music within the mymusic database. Create an if statement to test if the mysql_query function ran successfully. If it ran successfully, print out the message "Successfully added the entry for:" followed by a listing of the title, artist, album and release year. If the mysql_query function was not sucessfuly, print out the message "Unable to add an entry for:" followed by a listing of the title, artist, album and release year. Run the mysql_close() function, then include a link back to index.htm. 09 - Open the remove.php file and create a variable called deleterecord containing the record passed to it from page2.php. After the standard nested if structure, create a query using the delete option to remove the record specified. Make sure to include the "LIMIT 1" option. If the mysql_query command is successful, print out the message "Successfully deleted entry", otherwise print the message "Unable to delete entry". Run the mysql_close() function, then include a link back to the index.htm file. 10 - Add any CSS positioning, color and font options as desired to make each page easier to read. 11 - Test out your code by first displaying the 5 entries from the songs.csv file. Take a screen shot of the results. Next, add an entry to the database and get a screen capture of the page with the "Successfully added the entry for" message. Next, use the delete option to remove the record you just entered. Get a screen capture of the "Successfully deleted entry" message. urn in the local source code of page2.php, add.php and remove.php along with the screenshots. Be prepared to demonstrate the results in class. A working example of this code can be found HERE BACK


	Home \| Portfolio \| Blog \| Podcast \| Misc \| Contact \| Sitemap

Handout #5 - CSCI 2442

BACK