Data-Driven Web Pages

1. Stephen Rondeau 18 May 2009 Data-Driven Web Pages

2. 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

3. 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?

4. Sample Data In File

5. 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?

6. <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> Web Page with Data

7. 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?

8. Browsers and Web Servers Web Server Browser

9. Browsers and Web Servers Web Server Browser GET index.html

10. Browsers and Web Servers Web Server Browser index.html

11. Browsers and Web Servers Web Server GET form.html Browser form.html 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

12. Browsers and Web Servers Web Server Browser POST lu.php?ln=Smith&fn=Joe form.html lu.php HTML from lu.php form.html lu.php <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 lname=<?php echo $_POST["ln"]; ?><br /> fname=<?php echo $_POST["fn"]; ?

13. Quick Database Tutorial • Evolved from simple files • programs supporting searching files for criteria needed to be changed for each new set of data • updating and deleting was difficult and inefficient • Generalized, consistent way to structure data • 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.

14. Sample Data in Table

15. 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'

16. Quick Database Tutorial • DBMS: Database Management System • Common DBMSes: • Oracle, IBM DB2, Microsoft SQL Server • Robust, heavy-duty, complex, large, server-based, expensive • MySQL, Firebird • 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

17. Database Server Web Server Browser Web and Database Servers OR Web and Database Server Browser

18. 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

19. 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

20. 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()

21. 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

22. 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

23. Searching a Table • Search table for last name • Last name is supplied by user via browser • Two part process: • 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>

24. 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

25. 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

26. 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.

27. 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>

28. 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");

29. 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

30. 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); ?>

31. 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>

32. 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");

33. 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

34. 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/