#4 Database-driven Websites: phpMyAdmin

#4Database-driven Websites:phpMyAdmin Jean Lacoste Department of Accounting and Information Systems FDI Summer lacoste@vt.edu

Dynamic Web Pages

phpMyAdmin • Database management system used to: • Setup tables* • Add, change and delete data • Query tables • Graphical user interface

Login to phpMyAdmin http://www.fdi.hosting.vt.edu/fdi/phpmyadmin phpMyAdmin Username phpMyAdmin Password

Exercise #8 Log into phpMyAdmin

Database Database name

Creating Tables

Defining Fields Specify which field is primary key VarChar requires # characters. Set & Enum require list of values. Unique name Value if User enters nothing Automatically Increment value For each new record Required field Kind of data to be entered into field

Datatypes • Numeric • Integer • Decimal • Boolean (0 = False) • String • Date & Time

Exercise #9 Create the table for our example application. Name the table phpuser. Name your fields as they appear in the solution to Exercise #7 which is included on the next slide.

username – primary key password name email affiliation = faculty, staff, student college = 3 character abbreviation note All of the following will have values of y or n: dreamweaver html db php programming Exercise #7 Solution

Exercise #9: Solution

mySQL Generated

Adding Data INSERT INTO `address` ( `name` , `street` , `zip` ) VALUES ('Jean', '3076 Pamplin Hall', '24060');

Finding Data Wildcard character

SQL – Select Statement SELECT `name` , `street` , `zip` FROM `address` WHERE 1 AND `street` LIKE '105 Main St%' AND `zip` = '24091' ORDER BY `name` ASC LIMIT 0 , 30

Modifying Data UPDATE `address` SET `street` = '3007 Pamplin Hall' WHERE `name` = 'Jean' LIMIT 1 ;

Deleting Data DELETE FROM `address` WHERE `name` = ‘Crey’ LIMIT 1

Exercise #10 Add, modify and delete data from the table using phpMyAdmin. Find specific data by creating several queries based on different search criteria.

Import • Used to populate tables. • Create a comma delimited file (in Excel).

Import Settings Check if imported data should overwrite existing data comma delimited file Change to a comma

Export Select Select • Used to copy all data from one table to another application such as Excel. • Highlight resulting text. • Paste into NotePad and save. • Open in another application.

Evaluations • https://www.fdi.vt.edu/Evals/ • Track P1 Evaluations • Database

#4 Database-driven Websites: phpMyAdmin