Demonstrate your ability to retrieve information from a database and display it. This will require a variety of SQL queries. The results when finished will look similar to that shown in Figure 14.37.

Figure 14.37 Completed Project 1

1. You have been provided with a PHP page (ch14-proj1.php) along with various include files.

2. You will need to retrieve information from three tables: continents, countries, and imagedetails.

3. Display every image (the URL is supplied in the starting file) in the imagedetails table. The Path field contains the filename of the image. Each image should be a link to detail.php with the ImageID field passed as a query string. The supplied detail.php page contains sample markup for a single photo. You will need to construct a SQL query that joins data from the imagedetails table, the country table, and the cities table. The camera and color information shown in Figure 14.37 are from the Exif and Colors fields and contain json data. You can use the json_decode() function to convert this json data into a PHP object.

4. The filter section near the top of the page will be used to filter/reduce the number of images displayed in the image list. The user will be able to display only those images from a specific continent, country, or images whose Title field contains a search word after the user clicks the Filter button.

5. You will need to display every record from the continents tables within the <select> list that appears in the filter section near the top of the page. Each <option> element should display the ContinentName field; the ContinentCode field should be used for the option value.

6. For the Countries <select> list, you will display only those countries that have a matching record in the imagedetails table. This will require an INNER JOIN along with a GROUP BY.

7. When the user clicks the Filter button, the page should display only those images whose CountryCodeISO or ContinentCode or Title fields match the specified valued in the filter area. For the Title field, match any records whose Title field contains whatever was entered into the search box (hint: use SQL Like along with the wildcards character).

1. Break this down into smaller steps. A good starting point would be to get your PHP page to read and display data from the continents table. Then do the same for the countries and imagedetails tables.

2. The styling has been already provided for you. Examine the sample markup within the supplied <template> elements.

3. Get the list of images to display correctly, then implement the details page.

4. Finally, add in the filter functionality.

Demonstrate your ability to use MongoDB in conjunction with Node.js.

1. Create a new MongoDB database named adoptions filled with the data in the adoptions.json file by entering the following command in the terminal window (you will have to first start the mongod server process in a separate terminal):

   
   mongoimport -db project3 --collection adoptions --file adoptions.json –jsonArray

2. Try running a few sample queries within the MongoDB shell. For instance, retrieve the adoption with AdoptionID = 14. Retrieve all adoptions whose UniversityID = 100724.

3. Create a Node API with the route [domain]/api/adoptions. This will return a JSON object containing all the adoptions sorted by adoption date.

4. Add the additional route [domain]/api/adoptions/:id. This will return a JSON object containing a single adoption whose AdoptionID matches the passed:id parameter.

5. Add the additional route ([domain]/api/adoptions/university/:id) that returns a JSON object containing multiple adoptions whose UniversityID matches the passed:id parameter.

1. Create a simple JavaScript page that tests each of these routes using fetch. You may need to add the appropriate Access-Control-Allow-Headers to your API if it is on a different domain than your test page.

Demonstrate your ability to both select and modify data using either a PHP and SQL or Node and NoSQL. The result will look like Figure 14.38. You have been provided with an SQL import script if using MySQL, a sqlite file if using SQLite, and a JSON file if using MongoDB.

Figure 14.38 Completed Project 3

1. Unlike the earlier end of chapter projects in this book, in this project you have only been provided with a wireframe sketch (such sketches are often all that is provided to a development team). You are free to implement whatever design you wish, as the focus here is on the functionality. Your page must display a list of paintings; when the user selects a painting, it will display a form that allows the user to edit the data.

2. If using PHP, display a list of painting images and links styled as buttons in the left-side area. This will require querying the painting table. For each link button, add a link back to the same page but with the painting id as a querystring. When a request is received with a querystring, then display a data-entry form in the right area each field in the form should be populated with the appropriate record data.

3. If using Node, then create a API that returns an array of all the paintings. Add JavaScript to the HTML file that fetches the API and then populates the left-side list with an image and a link styled as a button for each painting. Add click event handler for each link button, that displays the form in the right side each field in the form should be populated with the appropriate record data.

4. This table has many fields; you should break up the data-entry form into different sections to make it easier for the user. The form should have a button for saving the current form values and a button for resetting the form. Be sure to set the action attribute of the <form> so that it runs a PHP or Node script that you will create. This script will use form data passed to it, and construct and execute an UPDATE query (if using SQL) or use the findOneAndUpdate() method of the Mongoose model object.

1. Break this problem down into smaller steps. Focus initially on step 2 (if using PHP) or step 3 (if using Node).

2. Verify the form has updated the painting data in the underlying data source.