Chapter Seven – Database Processing Applications
!DOCTYPE>
<html>
<?php
// Get connection
$Conn = odbc_connect(‘HSD’, ‘HSD-User’,‘HSD-User+password’);
// Test connection
if (!$Conn)
{
exit (“ODBC Connection Failed: “ . $Conn);
}
// Create SQL statement
$SQL = “SELECT * FROM CUSTOMER”;
Chapter Seven – Database Processing Applications
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “<tr>”;
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
<br />
7.53 Create a Web page for Heather Sweeney Designs to display the EmailAddress,
LastName, FirstName, and Phone of customers in the CUSTOMER table. Add a
hyperlink to the HSD home page to access the page.
Chapter Seven – Database Processing Applications
<!DOCTYPE html>
<html>
<head>
}
?>
<!– Page Headers –>
<h1>
The Heather Sweeney Designs CUSTOMER Table
<br />
(EmailAddress, LastName, FirstName, Phone)
</h1>
<hr />
<h2>
CUSTOMER
</h2>
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 24 of 100
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “<tr>”;
echo “<td>” . $RecordSetRow[‘EmailAddress’] . “</td>”;
echo “<td>” . $RecordSetRow[‘LastName’] . “</td>”;
echo “<td>” . $RecordSetRow[‘FirstName’] . “</td>”;
echo “<td>” . $RecordSetRow[‘Phone’] . “</td>”;
echo “</tr>”;
}
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
<br />
<hr />
<p class=“footer”>
7.54 Create a Web page for Heather Sweeney Designs to display the data in the
SEMINAR_CUSTOMER table. Add a hyperlink to the HSD home page to access the
page.
Chapter Seven – Database Processing Applications
<!DOCTYPE html>
<html>
// Create SQL statement
$SQL = “SELECT * FROM SEMINAR_CUSTOMER”;
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “<tr>”;
Chapter Seven – Database Processing Applications
odbc_close($Conn);
?>
<br />
<hr />
7.55 Create a Web page for Heather Sweeney Designs to display the data in the
SEMINAR_CUSTOMER table for the SEMINAR with SeminarID = 3. Add a hyperlink to
the HSD home page to access the page.
<!DOCTYPE html>
<html>
<head>
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 27 of 100
// Create SQL statement
$SQL = “SELECT * FROM SEMINAR_CUSTOMER WHERE SeminarID=3”;
// Execute SQL statement
$RecordSet = odbc_exec($Conn,$SQL);
// Test existence of recordset
if (!$RecordSet)
{
exit (“SQL Statement Error: “ . $SQL);
}
?>
<!– Page Headers –>
<h1>
The Heather Sweeney Designs SEMINAR_CUSTOMER Table
<br />
(SeminarID = 3)
</h1>
<hr />
<h2>
SEMINAR_CUSTOMER
</h2>
<?php
</tr>”;
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
Chapter Seven – Database Processing Applications
7.56 Create a Web page for Heather Sweeney Designs to display data in the SEMINAR,
SEMINAR_CUSTOMER, and CUSTOMER tables to list the SEMINAR data and the
EmailAddress, LastName, FirstName, and Phone of any CUSTOMER who attended the
SEMINAR with SeminarID = 3. Add a hyperlink to the HSD home page to access the
page.
<!DOCTYPE HTML>
<html>
<head>
</style>
</head>
<body>
<?php
// Get connection
}
// Create SQL statement
$SQL = “SELECT SEMINAR.SeminarID, SeminarDate, SeminarTime, Location, SeminarTitle, “;
// Execute SQL statement
$RecordSet = odbc_exec($Conn,$SQL);
Chapter Seven – Database Processing Applications
// Test existence of recordset
if (!$RecordSet)
{
exit (“SQL Statement Error: “ . $SQL);
}
</h1>
<hr />
<h2>
SEMINAR, SEMINAR_CUSTOMER, CUSTOMER
</h2>
<?php
// Table headers
echo “<table class=’output’ border=’1′>
}
echo “</table>”;
// Close connection
odbc_close($Conn);
Chapter Seven – Database Processing Applications
7.57 Code two HTML/PHP pages to add a new CUSTOMER to the HSD database. Create
data for two new CUSTOMERs and add them to the database to demonstrate that your
pages work.
New Customer Data:
Chapter Seven – Database Processing Applications
NewCustomerForm.html HTML Code:
<!DOCTYPE html>
<html>
<head>
</style>
</head>
<body>
</h1>
<hr />
<br />
<p>
<td>
<input type=“text” name=“EmailAddress” size=“50” />
</td>
</tr>
<tr>
<td> Last Name: </td>
<td>
<td>
<input type=“text” name=“FirstName” size=“25” />
</td>
</tr>
<tr>
<td> Phone Number: </td>
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 32 of 100
<b>Enter Customer Street Address, City, State and ZIP Code:</b>
</p>
<table>
<tr>
<td> Street Address: </td>
<td>
<input type=“text” name=“StreetAddress” size=“35” />
</td>
</tr>
<tr>
<td> City: </td>
<td>
<input type=“text” name=“City” size=“35” />
</td>
</tr>
<tr>
<td> State: </td>
<td>
<input type=“text” name=“State” size=“2” />
</td>
</tr>
<tr>
<td> ZIP Code: </td>
<td>
<input type=“text” name=“ZIP” size=“10” />
</td>
</tr>
</table><p>
<br />
<hr />
<p>
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 33 of 100
InsertNewCustomer.php HTML/PHP Code:
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Frameset//EN”>
<html>
<head>
Chapter Seven – Database Processing Applications
$Conn = odbc_connect($DSN, $User, $Password);
// Test connection
if (!$Conn)
{
exit (“ODBC Connection Failed: ” . $Conn);
// Execute SQL statement
$Result = odbc_exec($Conn, $SQLINSERT);
// Test existence of result
echo “<h1>
The Heather Sweeney Designs CUSTOMER Table
</h1>
<hr />”;
if ($Result){
echo “<h2>
New Customer Added:
</h2>
<table>
Chapter Seven – Database Processing Applications
echo “<td>StreetAddress:</td>”;
echo “<td>” . $StreetAddress . “</td>”;
echo “</tr>”;
echo “<tr>”;
echo “<td>City:</td>”;
// Create SQL statement to read CUSTOMER table data
$SQL = “SELECT * FROM CUSTOMER”;
?>
<!– Page Headers –>
<h1>
Chapter Seven – Database Processing Applications
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
<br />
<hr />
Chapter Seven – Database Processing Applications
ANSWERS TO MARCIA’S DRY CLEANING CASE QUESTIONS
Ms. Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry
cleaner in a well-to-do suburban neighborhood. Marcia makes her business stand out
CUSTOMER (CustomerID, FirstName, LastName, Phone, Email)
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 38 of 100
Chapter Seven – Database Processing Applications
Chapter Seven – Database Processing Applications
A. Create a database in your DBMS named MDC, and use the MDC SQL scripts for your
DBMS to create and populate the database tables. Create a user named MDC-User with
the password MDC-User+password. Assign this user to database roles so that the user
can read, insert, delete, and modify data.
B. If you haven’t completed exercise 7.51, do it now.
See exercise 7.51.
C. Add a new folder to the DBC Web Site named MDC. Create a Web page for Marcia’s
Dry Cleaning in this folder—using the file name index.html. Link this page.