Project Layout 1. You will model your files and site for this project after what you did in the previous assignment. Use the index. php includes file attached to this assignment to create a main page where a navigation link exists for each of the queries below. Show the question, the SQL query itself and the query results when the link is clicked. Structure your code so that you use an index. php file, a header. php file, a content. php file, a nav. html file and a footer. php file as in the previous PHP includes template assignment. As before, there should be a main folder (called p1 where is your initials … i. e., mine would be LLCp1) with all of these files in it. There should also be an includes folder that has six (6) php files in it as named below for the SQL Queries section (one for each of the queries below) as well as a database php file (called db. php).
2. Make sure you also have a db. php file in the includes folder as well and set the password to an empty string "" in it (see file attached here for sample). This php file should be included in the index. php file.
SQL Queries (see Chapters 2, 3 and Appendix B)
1. Write a query to display the names (last_name, first_name) from the employees table using an alias for the name "Last Name", "First Name". Order the results by last name in ascending order, then first name in ascending order (see Appendix B for alias and order by keywords). [called display_names. php]
2. Write a query to get unique department IDs from the employees table using an alias name "Department ID". Order the results in descending order by Department ID. [called employee_department. php]
3. Write a query to get all employee details from the employees table for employees whose last names start with an S. Order by last name, descending then by first name, descending. [called employee_last. php]
4. Write a query to get the names (last_name, first_name), salary, PF of all the employees (PF is calculated as 12% of salary) from the employees table. Order the results by last name in ascending order, then by first name in ascending order. [called employee_pf. php]
5. Write a query to get the employee ID, names (first_name, last_name), salary from the employees table in ascending order of salary. Limit the output to only those employees with a salary greater than or equal to $10,000. [called total_salaries. php]
6. Write a query to get the total salaries payable to employees from the employees table. This query should result in only one number being output. Label the output as tTotal Salries and make sure the value is in currency format. [called unique_department. php]
Adding A Personalized Welcome Message
Add a personalized welcome message to the main page of your web site (in the header portion of it) using an html form and php code that prompts the user for the first name and last name and then outputs "Welcome to my site, Professor!" if the first name entered is your professor’s first name and the last name entered is your professor’s last name. Otherwise, output "Welcome to my site, first name last name!". You will need to create a name. html file and a name. php file in the includes folder for this part of the project to add this code.
Adding and Deleting a New User
1. Add a new link on the main page that will open an html form to prompt the user for information needed to add a new user to the employees table. Take the information entered and add the new user to the employees table. Do a SQL select query on this newly added user to verify it worked (using employee_ID) and output the results for this user only. You should obtain the next available primary key value yourself and not prompt the user to enter this in the form. Add the appropriate files to the includes folder for this part of the project.
2. Add a new link on the main page that will open an html form to prompt the user for information needed that will allow them to delete a user from the employees table. The delete should be done using the Primary Key of the user. Once the user enters this key, output the other field values in the employees table for that user and ask them to verify it is the correct user, then check response and either delete the user or don’t, based on their response. Do a SQL select query before and after the delete to verify it worked (last query should show no results). Add the appropriate files to the includes folder for this part of the project.