Website Development & Management

1. CIT 3353 -- Fall 2006 www.clt.astate.edu/jseydel/mis3353 Website Development & Management Database Review & More(?) Instructor: John Seydel, Ph.D.

2. Recall About Working with MySQL on SuSE1 • Database server accounts • UserID = student last name (lowercase) • Password same as for SuSE1 • One database per student • Student last name plus DB • All permissions granted • Example: Ferd Berford, student ID 876-54-321 • SuSE login is berford-f • MySQL login is berford • Password for both is Ferd$321 • Has full privileges for berfordDB database

3. Exam #2 – FKAuto Intranet Application (Service Reminder) • First, turn in the take-home portion • Demonstration • Questions • Look at the code • In-class portion • Stylesheet • Form • Query processing • Take-home portion • Homework: deploy working application

4. The Final Exam – Complete the FKAuto Website • Two components • Internet site • Intranet site • Internet site • Primarily for prospective customers • Used car inventory and information • Shopping cart for accessories (not vehicles) • Access to employee intranet • Other . . . ? • Intranet • For FKAuto employees • View inventory, supplier, and HR info • Requires login • Database maintenance • Other . . . ? • Features • Your own stylesheets (research/employee design guidelines) • Cookies, Flash(?), JavaScript(?), other . . . ? • Team-based? • Starts now • Due on final exam date • Presentation to class curing final exam time period

5. A Couple of Utilities • dbase03.php: lists databases and tables available for a given user/admin • Involves creating an array to contain the recordset • Involves creating an additional array to contain another recordset • Note the while construct • Syntax • Loop termination • Written as an FKAuto application but is generic • dbTableCreate.php: generic set of files for creating database tables • Involves three files • Works only with class databases (e.g., berfelDB) • Both of these are available for FTP downloads through the cit3353 login

6. An INSERT Demonstration • Works only with the UsedCars table in any fkauto database to which you’re authorized • Needs customization for use with other tables • Refer to the handout • dbinsert01.php • dbinsert01_proc.php (incorrect title on handout) • Versions • Static, non-automated (using simple table) • Consider using MySQL directly • Create a non-formfed PHP application • Then consider form-fed PHP application

7. Appendix

8. Overview of Basic Database Processing • Reading data  SELECT • Adding data  INSERT • Changing data  UPDATE • Removing data  DELETE • Modifying the database structure • Creating a database  CREATE DATABASE • Creating tables  CREATE TABLE • Changing table fields  ALTER TABLE • Removing tables  DROP TABLE • Note reference material: Appendix D

9. SQL Examples • Adding data: INSERT INTO test_table VALUES (1,’This is a note’); • Changing data: UPDATE test_table SET test_note=‘New note’ WHERE test_id=1; • Removing data: DELETE FROM test_table WHERE test_id=2; • Creating a database: CREATE DATABASE testDB; • Creating a table: CREATE TABLE test_table; • Changing a table’s structure: ALTER TABLE test_table ADD test_cateogory TEXT; • Deleting a table: DROP TABLE test_table;