Web-Database Integration
Week 8
LBSC 690
Information Technology
Agenda
•	Questions

•	PHP

•	SQL

•	Midterm review
Ways of Generating Web Pages
•	Static: Written in a markup language
–	HTML, XML

•	Dynamic: Generated using a program
–	Common Gateway Interface (.cgi)
–	Java servlets

•	Dynamic: Generated from a database
–	Cold Fusion (.cfm)
–	PHP (.php)
Why Database-Generated Pages?
•	Remote access to a database 
–	Client does not need the database software

•	Serve rapidly changing information
–	e.g., Airline reservation systems

•	Provide multiple “access points”
–	By subject, by date, by author, …

•	Record user responses in the database
Three Ways to Serve Data
Data Access Pages
PHP Examples
•	Demo 1: a “Hello World” program

•	Demo 2: Reading input from a form

•	Demo 3: Printing output from a table 
Structured Query Language
DESCRIBE Flight;
Structured Query Language
SELECT * FROM Flight;
Structured Query Language
SELECT Company.CompanyName, Company.CompanyPhone, Flight.Origin, Flight.DepartureTime 
FROM Flight,Company
WHERE Flight.CompanyName=Company.CompanyName
   AND Flight.AvailableSeats>3;
The mysql DBMS
mysql –u dbname –h host –p password
show databases;
use RentAPlane;
show tables;
describe Flight;
select * from Flight;
insert into …
Cold Fusion
<HTML> 
<HEAD> 
<TITLE>Show All Students in Our School</TITLE> 
<CFQUERY NAME=“allStudents” DATASOURCE=“student20”> 
		SELECT * FROM Students 
	</CFQUERY> 
	</HEAD> 
	<BODY BGCOLOR="LIGHTBLUE">
	 	<H2> <B>All Students in Our School</B> </H2>
	 	<BR> <hr> <pre> studentID FirstName MiddleName LastName<br></pre>
		 <CFOUTPUT QUERY="allStudents"> 
		<PRE> 
		#studentID# #FirstName# #MiddleName# #LastName# 	
		</PRE> 
		</CFOUTPUT> 
		<FORM ACTION="mainMenu.cfm" Method="post"> 
		<input type="submit" value="Main Menu"> 
		</FORM> 
	</BODY> </HTML> 

SQL in ColdFusion
•	Show who borrowed which book

<CFQUERY name="borrowedBook" dataSource="student20"> 
    SELECT Books.Title, Students.FirstName, Students.MiddleName,     	 	   Students.LastName, Checkout.DueDate 
    FROM Books, Students, Checkout 
    WHERE Books.BookID=Checkout.BookID AND Students.StudentID=Checkout.StudentID;
</CFQUERY> 
The Grand Plan
The Midterm
•	Quiz/homework should be good preparation
–	A variety of question types
–	Some questions will require computer use

•	Lots of prior exams are available
–	Many have solutions available

•	Open book/notes/Internet/mind/…
–	Just don’t get help from another person
Computer Systems
•	Hardware 
–	Types of hardware
–	Storage hierarchy
–	Moore’s law
•	Software
–	Types of software
–	Types of interfaces
Networks
•	Types of Networks
–	LAN, WAN, Internet, Wireless
•	Packet Switching
–	Ethernet, routers, routing tables
•	Layered Architecture and protocols
–	TCP/UDP
–	IP address/domain name
•	Encryption

Structured Documents
Multimedia
Human-Computer Interaction
•	Input and output devices

•	Mental models

•	Interaction styles
–	Direct manipulation, menus, command line
Programming
•	Programming languages
–	Machines require low-level specific instructions
–	Humans require high-level abstraction

•	Can create any behavior from 3 control structures
–	Sequential execution
–	Conditional
–	Iteration

•	Javascript interpreters are in Web browsers
Databases
•	Structured information
–	Field->record->table->database
–	Primary key
•	Normalized tables (relations)
–	Remove redundancy, inconsistency, error
–	Easy update, search
•	Join links tables together
–	Through foreign key
•	Access provides visual operations
Web-Database Integration
•	Access “Data Access Pages”

•	PHP

•	SQL