Lab 4 of 4: Database Construction Using MySQL and PHP

iLAB OVERVIEW

Scenario/Summary
In this assignment, you will learn to create and manipulate a MySQL database using the PHP. To complete this assignment, you will need to be able to run MySQL via Omnymbus. You should review Chapter 11's coverage on PHP, the tutorial titled "PHP and MySQL" listed in the Webliography and MySQL's own dialect of the SQL language via the file M10C_KROE8352_13_SE_WC10C.pdf in the Doc-Sharing folder titled

My SQL Documents.

Please ensure that you can connect to MySQL/Omnymbus via the account your Professor has emailed to you. Please consult with the document titled MySQLOmnymbusSupport.docx located in the Doc-Sharing folder titled Omnymbus Tutorial Files for instructions on how to get help for any issues that you are having with the MySQL/Omnymbus Environment.

Deliverables

Name your MySQL database Lab4_xxxx where xxxx is your userid. Create and save your MySQL database file. You will be required to submit a web-link to your index.php file, along with 6 php script files and a sql file that will be executed from the Omnymbus public Web page from your Omnymbus account. Please note that your php and sql files will be based upon the templates provided the zip file titled MIS582_Wk7_templates.zip located in the folder titled iLab Documents in Doc Sharing.

You will need to modify the following php scripts and sql file from the MIS582_Wk7_templates.zip file.
• index.php
• insert.php
• insert_data.php
• update.php
• update_data.php
• delete.php
• MyAddressBook_CreateTable.sql

When you are done, submit your index.php link and zip file to the Week 7 Course Dropbox.
Required Software
Omnymbus - MySQL
Access the software at https://devry.edupe.net:8300.

Steps 1-7
iLAB STEPS
STEP 1: Review the Data Dictionary Back to Top
Review the provided data dictionary below to understand the entity, attributes, and primary key that you will create in your MySQL database.
Table Name ATTRIBUTE NAME CONTENTS TYPE FORMAT RANGE REQUIRED PK or FK
MyAddressbook Id Contact ID Number Auto-Increment
Int(3) 999 000-999 Y PK
f_name Contact First Name VCHAR(25) Xxxxxx Y
L_name Contact Last Name VCHAR(25) Xxxxxx Y
ADDRESS Contact Address VCHAR(25) Xxxxxx Y
City Contact City VCHAR(25) Xxxxxx 0-9 Y
State Contact State CHAR(2) Xxxxxx Y
Zip Contact Zip Code NUMBER(5) 99999 Y

Phone Contact Phone NUMBER(10) 9999999999 Y

STEP 2: Create Table, Insert Statement
Back to Top
• Run MySQL via Omnymbus.
• Modify the table creation script that will be used to create the database with the outlined attributes from the Data Dictionary.
• Provide one Insert Statement that will add your contact information to the newly created table.
• Save the file with a name containing Lab7_, your first initial, and your last name (e.g., Lab7_JSmith.sql).
• Execute your script and ensure that the database is created.

STEP 3: Build Index PHP Page
Back to Top
Building Your index.php Web Page
In this step, you will complete your php script page to connect to the Lab7 database and display the MyAddressbook table on the Web page.
• Complete the MySQL connection for the index.php page. Be sure to fill in appropriate host (127.0.0.1), username, password, and database information in the mysqli field.
• Modify the provided php code to display each attribute of your MyAddressbook table. Note that initially, only 1 entry should be present in your table.
• Save your index.php file in your public_html folder in Omnymbus file manager.
• Please note that you will find your public_html folder down the path zdisk/USERID/public html
• Direct your Web browser to your site url (ex.http://devry.edupe.net/~XXXX/index.php).
• Note that you'll replace XXXX with your Omnymbus login ID.
• Ensure that your file executes and the database table displays on the screen.
• If you have a correct connect command but you get the error message "MySQL ERROR 1045 (28000): Access denied for user" you will have to create a new password with the box "Change password in other modules?" checked.

• The following image provides an example of how your index.php page should look with no entries provided in the database. Note: Feel free to add any additional formatting or images to your index.php page.

STEP 5: Build Update PHP Pages
Back to Top
Building Your update.php and update_data.php Pages
In this step, you will complete two php HTML pages. Your update.php page will provide a form for users to submit data to the database that will be updated. The update_data.php page will provide the actual queries in php to update the data in your database. When this process is complete, your page will redirect the user back to the index.php page.

• Review the update_data.php page template.
• You will need to modify the php code to add additional input forms for the user to enter all appropriate attributes that will need to be updated.
• In the update_data.php file, ensure that your
tag is set equal to update.php
• Character data should have a character data type.

• When the action is complete, your page should redirect back to index.php

STEP 6: Build Delete PHP Page
Back to Top
Building Your delete.php Page
In this step, you will complete the delete.php page. Your delete.php page will process the query that will remove the necessary data for the database. When this process is complete, your page will redirect the user back to the index.php page.
• Review the delete.php page template.
• You will need to modify the php code to delete the provided entry based on the address id.

• When the action is complete, your page should redirect back to index.php.

STEP 7: Test Links to Update, Delete, and Insert Pages. Submit Deliverables.
Back to Top
Finishing Your Web-Enabled Database
Before submitting, ensure that you have added at least 5 entries to your Addressbook database.
Ensure that all links for update, delete, and insert work properly.
When you are done, submit your web-link to your index.php (e.g. http://devry.edupe.net/~####/index.php), along with the zip file titled Lab4_FirstInitialLastName.zip which should contain your six php code pages and your database script.

Rubric
Back to Top
Points will be awarded according to the following rubrics.
• 10 points: Script and Table created for the MyAddressbook entity as described in the Data Dictionary.
• 10 points: index.php created that correctly displays the contents of the database.
• 10 points: insert.php and insert_data.php pages created that correctly add data to the database.
• 10 points: update.php and update_data.php pages created that correctly update the data in the database.
• 10 Points: delete.php page created that correctly removes an entry from the database.
For a more advanced optional lab experience you can add error checking and comments and better appearance to the code. Use Chapter 11 as a guide.