Project Summary
You will be writing two programs for this project.
A utility that will create a new database with tables and populate it with sample data.
A program that demonstrates the use of the SQL keyword: JOIN.
This is an individual assignment and the work must be your own.
Module Objectives
Design a database with multiple tables
Create database and tables with a primary key using PHP and SQL.
Populate a table with data using SQL.
Refactor programming code using the DRY (Don’t Repeat Yourself) principle.
Extract data from multiple tables using SQL JOINs.
File Management
Inside the www folder of your AMPPS application, make a new folder named prjCRUD. It will contain the following files when the project is complete:
A file named dbfCreate.php
A file named dbfJoin.php
A file named prjCRUD[yourFirstLastName].sql created by exporting your database using phpMyAdmin. In the filename replace [yourName] with your firstLast name.
A sub-folder named graphic to hold all any images you use.
An external CSS file named style.css with your CSS code.
Specifications (70 points total possible)
40 points - Create and Populate a Database using PHP
Write a program named dbfCreate.php. This program will use PHP and SQL statements, creating a new database with tables. Populate the tables with at least three records of sample data. Encapsulate the code by organizing it into functions and calling the functions appropriately.
Grading Criteria:
createDatabase( ) - Will create a new database and tables. If a database with the same name already exists it should be deleted using the SQL keyword DROP. Create new tables based on the database design you created last week or a new database/table of your own design that uses key fields and foreign fields.
populateTable( ) - Will populate the table using sample data. Make certain that at least two records that do not relate to other tables. For example, using the sample data from last week, you could add a Product that has "null" for the Manufacturer or a Department that does not have a related Product. (You will need these records to demonstrate the different JOINs in the next section.)
displayTable( ) - Will display the data using an HTML table.
Demonstrate JOINs
Write a program named dbfJoin.php using the database you created in Part I.
This program should demonstrate a JOIN, LEFT OUTER JOIN, and a RIGHT OUTER JOIN by displaying a set of HTML tables based on the following specs:
Each table should have a <h2> heading describing what type of JOIN was used.
Underneath each heading, using the <pre> element, display the SQL String that was used to generate the data for that particular table.
Each table should include <th> elements with labels for each column extracted from the MySQL table
One table will include data from all three tables in the database.
One table should demonstrate the use of a LEFT OUTER JOIN.
One table should demonstrate the use of a RIGHT OUTER JOIN.
Grading Criteria:
3 points - All Data HTML Table showing results of a JOIN statement displaying data from all three tables.
3 points - LEFT OUTER JOIN HTML Table showing results of a LEFT OUTER JOIN statement with at least two null values displaying.
3 points - RIGHT OUTER JOIN HTML Table showing results of a RIGHT OUTER JOIN statement with at least two null values displaying.
6 points - Describe the three JOINs Write a paragraph explaining the difference between the three joins. Give an example on how each one would be useful in a program.
Publish to the Server
Publish your working code and database to the hosting service.
Include the .sql file that you created to export from your localhost out to the remote server as one of the files you submit to the instructor. Make certain this files has the extension of .sql.
Submitting your project
Zip up the prjCRUD folder from your localhost and submit it to Blackboard.
In the comment section include the URL to your site out on the server. (Please double check to make sure the URL opens the correct page before submitting your work.)
This URL is for the instructor's review. You may also be posting the URL as part of your discussion posting which will allow the other students to view your web site.
Note: Publishing your work out on the server is not enough. You must submit your code and a valid URL to your remote server when you submit your zip file to Blackboard in order to have your project graded.
Other Notes
Points will be deducted for the following:
Not following the naming conventions
Using filenames other than those specified.
Spelling or grammar errors. Proof-read each other's work. Use Grammarly.com
Inadequate commenting.
Missing a comment block at the top of each page of code.
Poorly formatted code. Use tools such as Free Formatter or Dan's Tools - HTML formatter
Commenting
Comments are required for each block of code. Add comments as notations to the instructor showing your knowledge and understanding of the code in your own words. Just having the code is not acceptable.
Every file created by you must have a comment header block at the top of the page including the following:
Name of the file and its purpose.
Course name and project name
Your name and email.
The date the file was written/created.
A place to document future revisions. (date, person, what was done)
Naming Conventions
The following naming conventions must be followed when naming all folder, filename, id, and class names:
Always start with a lower case letter.
Do not use spaces or underscores "_"
useCamelCaseForReadability. Acronyms can remain all caps, using a hyphen "-" only when absolutely necessary.
Use singular names. For example, graphics not graphics.
Keep all HTML elements and filename extensions lower case (Watch out for photo editing software that tries to save the filename with the extension set to all.