MySQL/Web Project

This project integrates several capabilities of PHP, MySQL, and the Web within a project.

Table groceries contains records with items found in local grocery stores. More specifically, the following fields are reported by the MySQL command, describe groceries.

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30)      | NO   |     | NULL    |                |
| category | varchar(30)      | YES  |     | NULL    |                |
| price    | decimal(10,2)    | YES  |     | NULL    |                |
| untanned | varchar(30)      | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

The first 5 rows of this table are shown below:

+----+--------------------------------+-----------+-------+----------+
| id | name                           | category  | price | unitName |
+----+--------------------------------+-----------+-------+----------+
|  1 | Pacific Rose Apples            | fruit     |  0.99 | pound    |
|  2 | Navel Oranges                  | fruit     |  1.69 | pound    |
|  3 | Honeycrips Apples              | fruit     |  2.99 | pound    |
|  4 | Anjou Pears                    | fruit     |  1.49 | pound    |
|  5 | Green Seedless Grapes          | fruit     |  1.79 | pound    |

Part 1: Display Grocery Items on the Web in a Table

1. In working with the grocery table, a SELECT statement allows the retrieval of database records, one at a time. Within a PHP program, obtain grocery items from the MySQL database and construct a Web page, in which the fields of the database are displayed in separate fields within an HTML table. Of course, the columns of the table should be labeled appropriately.

   The start of the resulting table might have the following form:

   id	name	category	price	unitName
   1	Pacific Rose Apples	fruit	0.99	pound
   2	Navel Oranges	fruit	1.69	pound
   3	Honeycrips Apples	fruit	2.99	pound
   4	Anjou Pears	fruit	1.49	pound
   5	Green Seedless Grapes	fruit	1.79	pound

   
   
   

2. Expand the table by adding a column that will allow a user to order a number of units for each grocery item. (Of course, the user can leave a quantity blank for unwanted items.)

   Details of this new column follow:

   • The entire table must be placed in a form.
   • The title of the column should be "units wanted".
   • Each row should contain an HTML text box, so the user can type a quantity (number of unites), if desired.
   • The "name" on the textbox should be the id number. Thus, the first textbox might have the form:

               <input type="text" name="1">
             

     Hint: In printing this part of the HTML table, one would retrieve the id as a variable from the database, and then print that variable twice: once as the contents of the first cell in the row, and a second time within the input box tag.

Part 2: Creating an groceryOrder Table, Based on User-selected Items

The grocery table contains 52 grocery items, and the id numbers run from 1 to 52. Thus, when the user completes the form in Part 1 and submits a form, the names of the text boxes will be "1", "2", ..., "52". Accessing the quantities for each id can be accomplished with a simple loop:

  for ($id = 1; $id <= 52; $id++)
  {
      $quantity = _GET[$id];

      ...   
  
  }

3. On the response page for the order form for Part 1, print the id numbers and quantities for each item ordered. That is, print the id numbers and quantities for which the $quantity is greater than 0.
   (Do not print items that the user has left blank.)
   The results might be displayed in a table:

         id     quantity
          3        5
         10        2.5
       

4. Expand the processing in step 3, so that the program

   • creates a new table, called groceryOrder, with two fields:
     • id: an int
     • quantity: a decimal (10, 2) (i.e., a number with up to 10 digits, including 2 decimal places)
   • inserts the id's and quantities printed in step 3.
     that is, each item printed in part 3 is printed and inserted into the new groceryOrder table.

created 19 February 2019 revised 20 February 2019