Transcript Document

Database-driven Web Pages from
Access Databases
Stephen Rondeau
TINST312
18 May 2009
Agenda
•
•
•
•
•
Converting Access DB to SQL Server
Connecting to Database
Displaying a Table
Searching a Table
Adding/Changing/Deleting Table Contents
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/
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
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
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
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
• 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>
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
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