Exercise: PHP with MySQL See the "Programming Examples" for help in answering questions 1 through 6. 1. Use SQL to define a table(file) named Item with the create command. The column names, data types and lengths are described below: Table Name Column Name Data Type Length Comment ---------- ----------- --------- ------ ---------- Item: id int 5 primary key, name varchar 15 cost decimal 5 2 dec. places quantity int 1 2. Invoke a load utility to copy item.csv into Item. For example: load data local infile 'e:/item.csv' into table item fields terminated by ','; Query Item to verify the data was loaded correctly. 3. Prepare and invoke a php program to insert an additional row into item. a. Assign to the variable $db the value returned from instantiating a new mysqli database object with parameters for host (e.g. "localhost"), userName, password, and database(schema) name. b. Define a variable named $sql that is assigned a string for a SQL insert statement with values 70000, 'Cookie Sheet', and 7.99. c. To execute, have $db invoke the function query with parameter $sql. d. Have $db invoke close(); e. Query MySQL from the client to verify this insert occurred correctly. 4. Prepare another php program to update an item row. a. Instantiate a new database object as in 3a. b. Assign to a php variable named $id a value such as 70000 and to $cost 8.89. Define $sql for an update string variable set equal to a string composed of an SQL update statement for item table that is to modify cost where id = $id. Reference the variables instead of literals. c. To execute, have $db invoke the function query with parameter $sql. d. Have $db invoke close(); e. Use the client to query item for verifying the result. 5. Prepare an html form to collect an item id. This form should have an action to invoke a php program to delete one item row. Use the post method. Include input tags for a type text field for name = 'id' and for type submit. 6. Code a php program referenced in the action option of the input form. a. Instantiate a new database object as in 3a or 4a. b. Assign to $id, a local variable, the $_POST[] parameter value for 'id'. c. Define an delete string variable named $sql. d. Have $db invoke query() with parameter $sql, then invoke close(). e. Query Item to determine whether the delete occurred. item.csv 04505,Noodle Cutter,18.95,6 12016,Garlic Press,13.95,9 15303,Coffee Maker,16.95,6 16344,Nut Cracker,69.00,4 20625,Potato Stomper,2.95,8 22760,Butter Melter,4.95,6 23316,Cheese Press,209.00,1 31102,Copper Kettle,69.00,3 36103,Pepper Grinder,27.50,4 69206,IceCream Maker,199.00,6