Transcript Data-Driven Web Pages - University of Washington

Data-Driven Web Pages
Stephen Rondeau
18 May 2009
Agenda

Problem Statement

Static vs. Dynamic Information

Browsers and Web Servers

Quick Database Tutorial

Web and Database Servers

How to Access a Database from Web Page
Problem Statement

“Put x on the web”


x could be employee data, songs, order info, etc.
Questions to ask:

Why?

What information – all or partial?

What can be done?

Who can do what?
Sample Data In File
101 John
102 Jane
103 Mary
104 Tim
Smith
Doe
Adams
O'Brian
Static vs. Dynamic Information

Could put information directly in web page

Can be formatted to look better

Someone who knows about web pages can update



Okay solution if data doesn't change more than once per
day and entire information is desired
Could put information into a file that page references

Link to a text file

Anyone could update – no knowledge of web

Easier to maintain, but doesn't look great
Information is static

cannot be changed except by updating entire file

What if person who updates can't do it?
Web Page with Data
<table>
<tr>
<td>101 <td>John <td>Smith
</tr>
<tr>
<td>102 <td>Jane <td>Doe
</tr>
<tr>
<td>103 <td>Mary <td>Adams
</tr>
<tr>
<td>104 <td>Tim <td>O'Brian
</tr>
</table>
Static vs. Dynamic Information

Dynamic information: data can change often

Web page displays a snapshot of current data


Someone is adding, changing or deleting data
If new or changed data comes from the web,
how does it get there from browser?
Browsers and Web Servers
Browser
Web Server
Browsers and Web Servers
GET index.html
Browser
Web Server
Browsers and Web Servers
Browser
index.html
Web Server
Browsers and Web Servers
GET form.html
Browser
form.html
Web Server
form.html
lu.php
form.html
<form method="post" action="lu.php">
Last Name:<input type="text" name="ln"> <br />
First Name:<input type="text" name="fn"> <br />
<input type="submit" name="submit"
value="Submit">
</form>
See form
Browsers and Web Servers
Browser
Web Server
POST lu.php?ln=Smith&fn=Joe
form.html
lu.php
HTML from lu.php
form.html
<form method="post" action="lu.php">
Last Name:<input type="text" name="ln"> <br />
First Name:<input type="text" name="fn"> <br />
<input type="submit" name="submit"
value="Submit">
</form>
See form
lu.php
lname=<?php echo $_POST["ln"]; ?><br />
fname=<?php echo $_POST["fn"]; ?
Quick Database Tutorial

Evolved from simple files



Generalized, consistent way to structure data


programs supporting searching files for criteria needed to
be changed for each new set of data
updating and deleting was difficult and inefficient
Data is efficiently stored and can be searched
Database is collection of tables and other info

table consists of rows of related columns of data


each column is named and typed
other info includes user accounts and passwords,
views, indices, procedures, etc.
Sample Data in Table
employees
data type:
col name:
Row/Rec 1
Row/Rec 2
Row/Rec 3
Row/Rec 4
Column1
integer
id
Column2
char(20)
firstname
101John
102Jane
103Mary
104Tim
Column3
char(20)
lastname
Smith
Doe
Adams
O'Brian
Quick Database Tutorial

Data usually accessed via SQL

SQL is Structured Query Language





INSERT puts data in table
SELECT retrieves data that matches criteria
UPDATE changes data in table
DELETE removes data from table
Examples


insert into employees(id, firstname, lastname)
values(105,'Lee','O''Leary')
select * from employees where lastname='Smith'
Quick Database Tutorial

DBMS: Database Management System

Common DBMSes:

Oracle, IBM DB2, Microsoft SQL Server


MySQL, Firebird


Robust, heavy-duty, complex, large, server-based, expensive
Robust, medium-duty, understandable, small, server-based, free
Microsoft Access



Adequate, light-duty, very understandable, medium, local,
affordable
De-emphasizes SQL, but it's there
Simple export to SQL Server
Web and Database Servers
Database
Server
Browser
Web Server
OR
Browser
Web and
Database
Server
How to Access DB from Web

Use PHP and ODBC

PHP: Perl Hypertext Processor


Scripting language for dynamic web pages
Embedded in HTML:



<?php php_statements ?>
Must be installed and configured on web server
ODBC: Open DataBase Connectivity



Standardized way to access DBMSes
Independent of programming language, DBMS, OS
Can switch to different DBMSes without changing code
How to Access DB from Web

PHP ODBC functions: must be installed





odbc_connect(): connect to DB
odbc_exec(): execute SQL statement
odbc_fetch_row(): get a returned row from odbc_exec()
odbc_result(): pick out column data from row
odbc_close(): cleanup and close DB connection
How to Access DB from Web

Outline of tasks:

Connect to DB and save connection “handle”


Use odbc_connect()
Do something with DB via connection handle


Pass a SQL statement to odbc_exec()
Usually loop over returned rows with odbc_fetch_row()


Extract data from rows using odbc_result()
Close DB connection

Use odbc_close()
Connecting to Database

Using “housing” database
 Access database exported to SQL Server


Will use tblEmployees
Save as file "connect_db.php":
<?php
$db = odbc_connect("dsn", "user", "pw") or die "not connected";
print "connected <br />";
?>
– http://cssgate.insttech.washington.edu/~css_test/housing/connect
_test.php

dsn is reference to database or "data set name"

user is database user id; pw is password for that user id
Displaying a Table


Copy "connect_db.php" to "show.php"
In place of print line:
$stmt = "select * from tblEmployees";
$result = odbc_exec($db, $stmt);
if ($result == FALSE) die("Problem with $stmt");
while (odbc_fetch_row($result))
{
print odbc_result($result, "LastName") . ", " .
odbc_result($result, "FirstName");
print "<br />";
}
• http://cssgate.insttech.washington.edu/~css_test/housing/show.php
Searching a Table

Search table for last name


Two part process:



Last name is supplied by user via browser
Part 1: accept search "key" from the user
Part 2: perform the search using the key
Part 1: Asking the user is HTML

save as "get_key.php"
<form method="post" action="search.php">
Last Name: <input type="text" name="key">
<input type="submit" name="search">
</form>
Searching a Table, Part 2


For example, let's say the key value is 'Smith'
Part 2: performing the search is PHP

Any user information in $_POST[ ]



e.g., $_POST[“key”]
Value is Smith
Copy "show.php" to "search.php" and modify as
follows
$key = str_replace("'", "''", $_POST["key"]);
$stmt = "select * from tblEmployees where LastName =
'$key'";


First line gets key passed from "get_key.php", replacing
each single quote with two single quotes
Second line uses SQL to search table for match
Adding/Changing/Deleting
Table Contents
• Standard form (table display):
Add a record
Change/Delete record 1…
Change/Delete record 2…
Change/Delete record 3…
etc.
• "Add a record" is a link to add.php, which asks
user for field values of record
• "Change" is a link to change.php with the record id
supplied
• "Delete" is a link to del.php with the record id
supplied
HTML for Add/Change/Delete

HTML:
<a href="add.php">Add a record</a>
<a href="change.php?id=24">Change</a>/
<a href="del.php?id=24">Delete</a>…


add.php doesn't require an id: new record
change.php and del.php pass the id value in
the query string; here the id is 24, e.g.
Add Process

Like searching, two parts:



Part 1: get values from user for fields of record
Part 2: insert the record in the table with id
add.php:
<form method="post" action="add_rec.php">
Last Name: <input type="text" name="lastname">
First Name: <input type="text" name="firstname">
<input type="submit" name="Add">
</form>
Add Process, Part 2

add_rec.php (just the important pieces):
$lastname = str_replace("'","''",$_POST["lastname"]);
$firstname = str_replace("'","''",$_POST["firstname"]);
$stmt = "select max(employeenumber)+1 from tblEmployees";
$result = odbc_exec($db, $stmt);
$new_id = odbc_result($result, 1);
$stmt = "insert into tblEmployees(employeenumber, lastname,
firstname) values($new_id, '$lastname', '$firstname')";
$result = odbc_exec($db, $stmt);
if ($result == FALSE) die("Could not insert $stmt");
Change Process

Three parts:



Part 1: get values from table
Part 2: let user change field values of record
Part 3: update the record using id and other
values
Change Process, Part 1

change.php (first part):
<?php
include 'connect_db.php';
$id = $_GET["id"];
$stmt = "select * from tblEmployees where employeenumber=$id";
$result = odbc_exec($db, $stmt);
if ($result == FALSE) die("Could not find $id: $stmt");
$lastname = odbc_result($result, "LastName");
$firstname = odbc_result($result, "FirstName");
odbc_close($db);
?>
Change Process, Part 2

change.php (second part):
<form method="post" action="change_rec.php">
<input type="hidden" name="id"
value="<?php echo $id; ?>">
Last Name: <input type="text" name="lastname"
value="<?php echo $lastname; ?>">
First Name: <input type="text" name="firstname"
value="<?php echo $firstname; ?>">
<input type="submit" name="Change">
</form>
Change Process, Part 3

change_rec.php (just the important pieces):
$id = $_POST["id"];
$lastname = str_replace("'", "''", $_POST["lastname"]);
$firstname = str_replace("'", "''", $_POST["firstname"]);
$stmt = "update tblEmployees set lastname='$lastname',
firstname='$firstname' where employeenumber=$id";
$result = odbc_exec($db, $stmt);
if ($result == FALSE) die("Could not update: $stmt");
Delete Process

Could be simple – delete immediately via del.php
(just the important pieces):
$id = $_GET["id"];
$stmt = "delete from tblEmployees where employeenumber=$id";
$result = odbc_exec($db, $stmt);

But deleting immediately doesn't allow for user
mistake – should confirm with the user



search for info associated with id (e.g., lastname and
firstname)
display info to user and ask for permission to delete, then
delete as above if okay
how to do the confirmation is left as an exercise for the reader
References

Documentation (includes PHP source)
• http://css.tacoma.washington.edu/~lab/Support/HowtoUse/PHP/

Web Page Examples
• http://cssgate.insttech.washington.edu/~css_test/housing/