CIS 627 (Web Database Services Development) Week 3

Getting started with MySQL and a little more PHP

1. PHP Connecting to a MySQL using a PDO (PHP data Object)
   
   

   Possible resources:

   • Programming PHP (Tatroe, MacIntyre & Lerdorf) p. 205
   • PHP Data Objects (PDO): Connecting (Part 1/8) The presenter (Alex) uses the default root user without a password. Last week in PMA_intro.ppt I referenced a method for adding a root password (Slide 5). Later I showed how to add another user to the database (Slide 10). I am finding it easier to connect to the database if I change the "host" to LOCAL (localhost).
     
     
     
   • PHP Data Objects (PDO): Getting results (Part 2/8) shows that a record return from a database SELECT is both an associative array and a numerical array
   • PHP Tutorial - 21 - include function (We are going to put the credentials for connecting to a database in an include credentials file)
   • Beginner PHP Tutorial - 42 - include and require Functions
   • DatabaseConnectAndSimpleSelect.zip (contains a basic database connection and simple query with the database credentials in a separated "included" file -- the database used is the state-capital database used as an example last week)
   • PHP Data Objects (PDO): Fetch types (Part 3/8)
2. Presenting results of SELECT statements
   
   

   Possible resources:

   • MakeTable_StateCapital.zip Makes an HTML table from results of a database select statement (uses FETCH_NUM).
   • MakeStateCapitalTable_2.zip Same as above but uses FETCH_ASSOC and a DESCRIBE statement to make table headers.
   • MakeStateCapitalTable_3.zip Adds the data-table (jQuery plugin) functionality to the table. (https://www.datatables.net/).
   • MakeStateCapitalDropDownSelect.zip Make an HTML drop-down select element from the results of a database select statement.
   • MakeStateCapitalRadioButtons.zip Make an HTML set of radio buttons from the results of a database select statement.
   • MakeStateCapitalCheckboxes.zip Make an HTML set of checkboxes from the results of a database select statement.
   • MakeStateCapitalOrderedList.zip Make an HTML ordered list from the results of a database select statement.
3. Toward an API
   
   

   Possible resources:

   • StateCapitalFetchAllFETCH_OBJ_JSON_Encode.zip fetches the results of a query all at once instead of record-by-record, makes objects out of the records, converts those objects into the corresponding JSON (JavaScript Object Notation)
   • StateCapitalInfoFromAbbr.zip Shows a prepared, parametrized query. Uses the GET method to allow the user to supply the parameter value (a state abbreviation). Results displayed as JSON
4. Create Northwind database from script
   
   

   Possible resources:

   • BuildingNorthwind.ppt
   • Northwind_MySQL5_1.zip SQL script for northwind database -- creates tables and inserts data, adds relationships
5. Northwind exercises
   
   
   • Make a PHP page that has a drop-down select populated by a northwind query with the ProductName displaying to the user and the ProductID serving and the option's value. Put your credentials in an included file as shown in the examples; I should be able to swap my credential file for yours and run your code without needing other changes.
   • Make a page (possibly the previous page) with a form that allows the user to enter a ProductID then use the GET method and a "prepared, parametrized" query to output the data for that product in JSON format.
   • Make a PHP page that has a set of radio buttons populated by a northwind query with Shipper Company Name displaying in a label and the ShipperID serving as the value for the radio button
   • Make a PHP page that has a table (that has the jQuery plugin data-table functionality) populated by a northwind query of the suppliers table
6. Some questions
   
   
   • What are InnoDB and MyISAM? Compare them.
   • What is a compound primary key? Do any of the tables in the northwind database have a compund primary key?
   • The SQL script used to make the northwind database include the command TRUNCATE TABLE. What does that do?
   • What does that following statement from the SQL script do?

         ALTER TABLE `Orders` ADD CONSTRAINT `FK_Orders_Customers` 
         FOREIGN KEY (`CustomerID`) REFERENCES `Customers` (`CustomerID`);
         

   • How do you make a comment in a SQL script?
   • How do you use BETWEEN in a query? In last week's state-capital database, how would you find the states that were established in the first half of the 20th Century (1900-1950)?
   • What is the purpose of PDO's (PHP Data Object's) prepare method?
   • Compare the JSON and XML formats.