MySQL database within PHP
Submission Instruction
Put all resources used by your assignment in a folder named a4_XXXX#### where XXXX#### is your Laurier's user_name (e.g., a4_mrudafshani). Zip the folder. Submit the a4_XXXX####.zip file through MyLearningSpace under the appropriate dropbox (named Assignment 4). Your a4_XXXX### folder must have the following structure:
Include a readme.txt file in the zip file. The readme file should have the following format. Replace the urls with the URLs to your assignment.
Name: ID:
Email:
Assignment_ID: CP476A2
Homework statement: I claim that the enclosed submission is my individual work.
In addition to submitting to the dropbox, make your assignment available on hopper. Put a link to your assignment on hopper in the browse-paintings.php file (explained later).
A start.zip file is provided to you which includes all the required files for you to get started on the assignment. Keep the same structure.
Non-Functional Requirements (25 points)
(5 points) You must have a folder named include. This folder includes a file named config.php. The config.php file must define constants that will be used in the rest of your php code for connecting to the database. You can refer to the examples on the course web page to see how config.php is used (you need to include it in any php file that needs to connect to the database). This file is included provided in the start.zip file provided.
(5 points) You must use binding parameters when you put user’s input in a query. The examples in the course page are done using this method.
(5 points) Organize your code by developing functions. In the start.zip file provided few hints are given.
(5 points) Error checking must be done when connecting to the database and in any other interaction with database.
(5 points) You need to call isset and empty functions whenever needed to prevent errors.
Database
You will be working with a database named artDB. The artDB.sql file is provided and includes all the SQL statements for creating the artDB database and the tables and data within the database. The tables are shown in the figure below. Note that if you are putting this database on hopper you do not have enough permission to create or delete a databases. In that case, you need to modify the parts of artDB.sql file that CREATE/DROP a database, so that it adds the tables and data to the the database scheme available for you on hopper. It is named according to your user-name on hopper.
Functional Requirements (75 points)
We continue working on the Art Store project. Start working on a file named
browse-paintings.php which is provided to you. Once you are done with this assignment the browser-paintings.php file should look like the following.
The right side of the page, under the Paintings header, 20 images are shown. The header and footer of the page are the same as what you developed for pageOne.html in Assignment 1 and is not included in the browse-paintings.php provided. The html for the middle part is provided in the browse-paintings.php. To help you in doing the assignment, the locations in the code that you need to put your php code in the browse-paintings.php file is specified.
(20 points) Question 1. Once the user clicks on the “Select Artist” in the drop-down list beside Artist in the above figure, the names of all the artists in the Artists table should be displayed (as shown in the following figure). The names of all the artists must be retrieved from the database using php and listed there. You must NOT hardcode the names in the
browse-paintings.php file. The same is true for the list of museums and shapes. You must retrieve the content of each list from the database and use the result to generate each list
(in the museum goes the GalleryName field from the Galleries table and in the shape goes the content of ShapeName field from Shapes table.
Note: Use utf8_encode function to make sure the data retrieved from the database is displayed appropriately.
(20 points) Question 2. On the right side of the page, under the Paintings header, you must display the first 20 paintings, not all the paintings. You can use limit 20 at the end of a query to limit the number of records to 20. For each painting you must display the title of the image (Title field in the paintings table), the image itself ( the name of the image file is in ImageFileName field in the Paintings table and the image is located under images/square-medium folder), the artist name (FirstName and LastName fields in the Artist table) , a description of the painting (Excerpt field in the Paintings table) and the price (MSRP field in the Paintings table). You need to have a while loop to display the items for each image (the template is provided in browse-paintings.php file).
Note: Both the image itself and the image title have a link to another php page named
single-painting.php and pass the painting id as a query string parameter.
(20 points) Question 3.
The single-painting.php is shown in the next figure. This file is actually the pageOne.html page you created in Assignment 1, but this time you modify it so that the content of the page are generated dynamically.
In the single-painting.php file you need to display the title of the image, the artist’s name, the image file (Note: you must use the larger version of the image available under images/medium folder. You must not enlarge the smaller photo used is
browser-paintings.php).
You also need to display the description of the image (the writing on the right side which is in the Excerpt field of Paintings table) followed by the price of the image, and the product details.
To display product detail you need to access the following fields:
The Date (YearOfWork in Paintings table), Medium (Medium field in Paintings table), Dimensions (Width and Height fields in Paintings table), Home (GalleryName, GalleryCity and GalleryCountry fields in the Gallery table), Genres (GenreName in the Genres table), and subject (subjectName in the Subjects table)
(15 points) Question 4. In this question you work on filtering data. If the user selects an artist, then the page must only display all the paintings by that specific artist on the right side (as shown in the example below). The user may select a museum, a shape, or a combination of them (e.g, all paintings by a specific artist in a specific museum OR all the square paintings by a specific artist in a specific museum).