Assignment 6 -- SQL: Queries & Updating

You may find it convenient to have your "SQL in 10 minutes" book available while working on this assignment.

This first part of this assignment uses three tables: geekProducts, geekCategories and geekProductCategories. The tables have the fields shown below.

To add these tables to your mySQL database, login into phpMyAdmin, click the "SQL" tab, paste this phpMyAdmin SQL dump into the query field and click the Go button.

 

All of your php pages should initially display neatly (no partial  tables or incorrect query results), respond gracefully when no records are found, and handle incorrect user entries. You may add additional features, but your pages should look like the samples and have the same or more functionality.

Debugging Tip: If you have problems debugging a SQL statement in your PHP page, echo the SQL to the browser and debug it in phpMyAdmin (under the SQL tab). Once you get it working inside phpMyAdmin then return it to the PHP script. This method separates SQL problems from PHP problems, simplifying the debugging process.

1. ProductsByPrice.php - List all products under a specific price, sorted by price (primary) and name (secondary). You may wish to use mySQL.php (source) as a template. Look in the sample's source code to see the images URLs.

2. ProductSearch.php - This page searches the Name field and returns all records that contain any part of the query in the product name. For instance, a search for "ff" would return "coffee" and "caffeine." The SQL statement uses wildcards with the syntax:

   "WHERE Name LIKE '%$query%' "

   where $query is the string entered by the user.

3. RandomProduct.php - Display a random selected product from the database. Use the long product description and the medium size images. The following SQL statement randomly orders the records in the database and returns the first record:

   select * from geekproducts order by rand() limit 1;

4. ListCategories.php - List the product categories as hyperlinks and display the name of the selected category. The SQL query uses the "DISTINCT" keyword to sort the items and remove any duplicate category names.

• Optional: Improve the menu by using "group by" to list the number of items in each category (sample)

5. CategoryItems.php - Display the items in each product category.  Copy the previous exercise and add a second query to retrieve the items in the selected category. The SQL statement joins the tables geekProducts and geekProductCategories on the field ItemID. Display the Name, price, thumbnail image and short product description.
   
   
6. CRUD.php - Updating database records is a two step process. The first step retrieves the existing information and populates the textboxs. The second step writes the updated information back to the database.
   
   

   Step 1: Populating the textboxes:

   1. Add an update link (similar to the delete link) that passes a parameter named "updateID."
   2. The customer data must be retrieved from the database before the textboxes are rendered. Move the code for connecting and selecting the database to a location above the form.
   3. Write code to retrive the customer data and assign the data to local variables named $strFName2 and $strLName2.
   4. Populate the textboxes using the syntax:
      <input type="text" name="strFName" value="<? echo $strFName2; ?>">
   5. Test your code to make sure it is populating the textboxes.

   Step 2: Updating the database.

   1. We need a flag to tell us that we are in the update step of the process. We also need to save the updateID. We can do both by storing the updateID in a hidden form field. Add a hidden field named updateID2 that passes the $updateID.
   2. Write an if statement that checks for updateID2 in the querystirng and executes an UPDATE SQL statement.
   3. Fix: the Insert code will be triggered by the update because the textboxes contain data. Modify the if statement that triggers the insert to check that $updateID2 is empty.