Transcript form
Web Database Programming
Connecting Database to Web
Example Design
Customers come to the web site and register. The sales department
sets up a list of widgets that customers can order from. A customer
makes one or more orders for widgets. When the order is filled the
1 are classified by type.
order is removed from the database. Widgets
Name
Passwo
rd
User
Name
M
1
Customers
issue
Order
price
name
time
M
1
includ
e
Widgets
M
belong
Type
1
SQL Query
• SELECT … FROM … WHERE … ORDER
BY
• Examples of SQL
– SELECT * FROM Customer
– SELECT FirstName, LastName
FROM Customer
WHERE FirstName=“Andrew” AND LastName=“Fuller”
– SELECT FirstName AS FName, LastName AS LName
FROM Customer
WHERE LastName=“Fuller” OR
(YearOfBirth<"1950” AND Photograph Is Null)
ORDER BY LastName
Get a Table and Display it on
the Web
• Connect to a database server (get a handle
to a connection)
– Host
– User name
– Password
• Select a database
• Issue SQL query to get a table
• Fetch results row by row
– Display each row
• Error checking in every step
Example Code
<html><head><title>Customer List</title></head>
<body>
<?php
$db = mysql_connect(“db_host”,
“db_user”,“db_pwd”)
or die(“cannot connect”);
mysql_select_db(“dbname”,$db)
or die(“cannot select db”);
$sql = “SELECT FirstName, LastName ”;
$sql = $sql.“FROM Customer”;
$result = mysql_query($sql,$db)
or die(“query failed: ”.mysql_error());
while ($row = mysql_fetch_row($result))
{
$fn = $row[“FirstName”];
$ln = $row[“LastName”];
print “<li>$fn $ln</li>”;
}
?>
</body>
Include file
• Put database login information into a separate file
– For Portability and security
<?php
$db_host = “localhost”;
$db_user = “instruct”;
$db_pwd = “xxxxx”;
?>
• Each database accessing script “include” this file at
the beginning (or “require”)
include(“db_login.php”);
• Use variables and functions in included file as if it is
in this file
Using HTML Forms for Queries
• Example
– For a customer table (FirstName, LastName)
– Use a page to query the database, find a record,
and display it
– Later we will look at other actions you can take
(insert, update, and delete)
• Notice
– What happens when the user submits the form
– Form display and form processing may be done
with different files
The Form Display Part
• Tags in CustomerSimpleQueryForm.html
<form action="FirstNameQuery.php" method=post>
Enter a first name:
<input type=text name="queryFirstName">
<input type=submit
value="Query with first name.">
</form
• Notes
– The form action is the URL of the file or script that will
“handle” the form
– The name of the form input field is used in the action file to
identify the corresponding value, I.e. it is the same name in
$_POST[‘name’]
The Action
• Query:
$sql="SELECT FirstName, LastName from
Customer ";
$sql = $sql."WHERE
FirstName='$_POST[queryFirstName]'";
$results = mysql_query($sql,$db) or
die(“query failed: ”.mysql_error());
• Notes
– in SQL, enclose text in single quotes (‘text’) while numbers
do not have them (e.g. 3)
– The $sql string can be used for debugging (e.g.
print(“sql = $sql”); )
The Output
• PhP Code Fragment
while ($row = mysql_fetch_row($results))
{
// print fields one by one
// or use a loop
print("<li>$row[0] $row[1]</li>");
}
Or:
$count = mysql_num_rows($results);
for ($i = 0; $i < $count; $i++)
{…
• Note
– what happens when result set is empty?
Redirect using header()
• The PHP function header() takes over the
sending of the “header” to the client
• Example:
if (!$_POST[queryFirstName]) {
// there was no id value
// so send the user to the add form
header(“Location: add.html”);
exit; // and get out of here!
}
• Note there can be NO OUTPUT prior to the
use of header() – no print statements – not
even a blank line (outside of the <?php tag)
Alternate means of doing a
Query
• URL parameters
– On the URL following ?, and separated by
&
– Each value is a name=value pair
– For example:
http://host/path/file.php?a=5&b=12&c=
yes
• These parameters can be accessed
with $_GET[‘name’]