Database-driven Web Pages from Access Databases

1. Database-driven Web Pages from Access Databases Stephen Rondeau TINST312 18 May 2009

2. Agenda • Converting Access DB to SQL Server • Connecting to Database • Displaying a Table • Searching a Table • Adding/Changing/Deleting Table Contents

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

4. Prereqs • It will help a lot if you know: • HTML (br, tables, form, input) • SQL statements (select, insert, update, delete) • a programming language • variable assignments • function calls and returns • if-then-else • while loop • working with strings

5. Conversion of Access to SQL Server • Have system admin install and configure SQL Server and add a user id and password for you • Setup ODBC connection to SQL Server • via Control Panel/Administrative Tools/ODBC Administrator • Select table to export • Right-click on it and choose "Export…" • In "Save as type", choose "ODBC Databases" • Find ODBC definition • Save it there

6. Connecting to Database • Have system admin install and configure PHP ODBC • 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

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

8. Searching a Table • 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>

9. Searching a Table, Part 2 • Part 2: performing the search is PHP; 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 matching last name

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

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

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

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

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

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

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

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

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