Lab 10

The purpose of this lab is to query a MySQL database with php mysqli and display the results. This lab will be similar to lab3's Shopping Catalog, but instead querying from a database.

Within MySQL environment, create a table called Products that has the following fields and types: 


CREATE TABLE Products (
    prod_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    prod_name varchar(127) DEFAULT NULL,
    prod_img varchar(127) DEFAULT NULL,
    prod_description varchar(511) DEFAULT NULL,
    prod_price decimal(10,2) DEFAULT NULL,
    prod_rating tinyint unsigned DEFAULT NULL,
    prod_sku char(32) DEFAULT NULL,
    prod_stock int(10) unsigned,
    PRIMARY KEY (prod_id)
);

Still within MySQL environment, insert at least six or more product items. Be sure to use various values for price, rating, and stock. Have at least one item with a stock value of 0. For the product img, the value will be the string value for the filename.jpg of the image; do not include the full path to file, just the file name.

Write a php script file called lab10.php that will connect to your database. Create an images directory to store your product images. Next, create a defined constant called IMG_DIR such that each image you output will be as follows: 
echo "<img src='".IMG_DIR.$img_filename."' >";
Use the following form to setup how to order and display the product items. 
<form method='GET' action='lab10.php'>
  <label for='sortby'>Sort By</label>
  <select name='sortby' id='sortby' onchange='this.form.submit()'>
    <option value=''> </option>
    <option value='rating'>Rating</option>
    <option value='priceHighToLow'>Price High to Low </option>
    <option value='priceLowToHigh'>Price Low to High </option>
  </select>
</form>
Setup your query to select all of your products ordered by the corresponding $_GET['sortby'] value. Use the SQL ORDER BY keyword to sort. An example approach: 
$sql='';
if(isset($_GET['sortby'])) {
  switch($_GET['sortby']) {

    // ... add cases ... 
    // rating 
    // price high to low
    // price low to high

    case '': // fall through to default case
    default:
        $sql = 
            "SELECT prod_name, prod_img, prod_description, 
                    prod_price, prod_rating, prod_sku, prod_stock
            FROM Products";
        break;
}
Query your Products table and display each product item similar to lab3. 
 ---------------------------------------
|    image      |Name: ITEM             |
|    goes       |Rating:* * * * -       |
|    here       |Price: $98.76          |
|    item.png   |SKU:123abc987zyx000    |
|               |In-Stock               |
|---------------------------------------|
|Description:                           |
|                                       |
|                                       |
 ---------------------------------------
For the product ratings value, display the corresponding amount of full and empty stars. For example: if the rating is 4, display 4 filled stars and 1 empty star. For the product stock quantity, display whether the product is In Stock or **Out of Stock**.

Have all of your php, css, js, images, images directory, etc in your ~/3680_S19/wk10/ depository directory, which I'll extract and copy at the time of the due date.