Chris Pollett >
Students >
Chao
( Print
View )
[Bio]
[Del 1]
[Del 2]
[Del 3]
Database Query in PHP
Description:This deliverable solves Exercise 7.6 for HW2 of CS157b Fall 2003. It converts Dr. Pollett JSP solution to be PHP. The script first asks user to input information for a MySQL connection. When the user submits the request, the returned page is a current batch of query results from a Book table in the database. At the bottom of the page, query results are shown with two links: Previous and Next. These links allow user to navigate forwards and backwards through the query results respectively.
Example:This is what my code outputs on these inputs.
This is the screen shot of starting up a MySQL server.
This is the introduction page to ask the user to input the database information.
This is the first result set after the user clicks on "submit" button.
This is the second result set after the user clicks on "Next" button on the first-result-set screen, or on "Previous" button if the user comes from the third-result-set screen.
This is the last result set after the user clicks on "Next" button on the second-to-last-result-set screen.
query.php File
<?php
echo '<?xml version="1.0" encoding="ISO-8859-1"?>'
?>
<?php
/*
This PHP solves Exercise 7.6 for HW2 of CS157b Fall 2003.
It converts Dr. Pollett JSP solution to be PHP.
It has a start up screen requesting information to
connect to a MYSQL database. When this is entered and submitted,
the page returned is a current batch of query results from
a Book table. At the bottom of the page of query results.
Previous and next links allow the user to navigate forwards
and backwards through the query results.
author: Chao Liang
version: 2006/2/19
*/
/*
This function computes the next batch of query results
from the database and writes them to the client.
It also computes and return an array of highBookID and highTitle,
which are needed to construct the next link
at the bottom of the page.
Parameter: integer $numberDisplayed - entries to be displayed per page
Return: An array that contains the highTitle and highBookID.
*/
function computeQueryResults($numberDisplayed)
{
$conn = mysql_connect($_REQUEST['connect'],$_REQUEST['login'], $_REQUEST['password']);
mysql_select_db($_REQUEST['database'], $conn);
if( isset($_REQUEST['set']) && $_REQUEST['set'] != 0)
{
$temp1 = "b" . $_REQUEST['set'];
$temp2 = "t" . $_REQUEST['set'];
$sql = "SELECT title, bookid FROM Book WHERE (title >= '" . $_REQUEST[$temp2] .
"' && bookid >= " . $_REQUEST[$temp1] . " ) || (title > '"
. $_REQUEST[$temp2] . "')" .
" ORDER BY title, bookid";
}
else
{
$sql = "SELECT title, bookid FROM Book ORDER BY title, bookid";
}
$result = mysql_query($sql, $conn) or die(mysql_error());
echo "<p>";
$i = 0;
$highTitle = null;
$highBookID = null;
$lowTitle = null;
$lowBookID = null;
while(($more = mysql_fetch_array($result)) && ($i < $numberDisplayed))
{
$highTitle = $more['title'];
$highBookID = $more['bookid'];
if($i == 0)
{
$lowTitle = $highTitle;
$lowBookID = $highBookID;
}
echo " Title: " . $highTitle . "    BookID: " .
$highBookID . "<br/>";
$i++;
}
echo "</p>";
if($more == null)
{
$highTitle = null;
$highBookID = null;
}
mysql_close($conn);
return array ($highTitle, $highBookID);
}
/*
This function computes the query string needed for the next
and previous links at the bottom of the web page. It will extract the
previous book ids and book titles from the request string to construct
the "previous" link and use the passed-in parameter, which is an array of
highest book id and title for the current set, to construct the "next" link.
Parameter: reference to array &$high -
an array of two items: highTitle and highBookID
*/
function computeOutputLinks(&$high)
{
if(isset($_REQUEST['set']) && $_REQUEST['set'] > 0)
{
echo "[<a href=\"http://" . $_SERVER['SERVER_NAME'] . $_SERVER['PHP_SELF'] . "?";
echo ("connect=" . $_REQUEST['connect'] . "&");
echo ("database=" . $_REQUEST['database'] . "&");
echo ("login=" . $_REQUEST['login'] . "&");
echo ("password=" . $_REQUEST['password'] . "&");
echo ("set=" . ($_REQUEST['set'] - 1) . "&");
for($i = 0; $i < $_REQUEST['set']; $i++)
{
$temp1 = "t" . $i;
$temp2 = "b" . $i;
echo ("t" . "$i=" . $_REQUEST[$temp1] . "&");
echo ("b" . "$i=" . $_REQUEST[$temp2] . "&");
}
echo ("\" >Previous</a>] ");
}
if($high[0] != null)
{
if (!isset($_REQUEST['set']))
$set = 0;
else
$set = $_REQUEST['set'];
echo "[<a href=\"http://"
. $_SERVER['SERVER_NAME'] . $_SERVER['PHP_SELF'] . "?";
echo ("connect=" . $_REQUEST['connect'] . "&");
echo ("database=" . $_REQUEST['database'] . "&");
echo ("login=" . $_REQUEST['login'] . "&");
echo ("password=" . $_REQUEST['password'] . "&");
echo ("set=" . ($set + 1) . "&");
for($i = 0; $i <= $set; $i++)
{
if($set == 0)
{
echo ("t" . "$i=" . "&");
echo ("b" . "$i=" . "&");
}
else
{
$temp1 = "t" . $i;
$temp2 = "b" . $i;
echo ("t" . "$i=" . $_REQUEST[$temp1] . "&");
echo ("b" . "$i=" . $_REQUEST[$temp2] . "&");
}
}
echo ("t" . ($set +1) . "=" . $high[0] . "&");
echo ("b" . ($set +1) . "=" . $high[1] . "&");
echo ("\" >Next</a>] ");
}
}
?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Exercise7.6
<?php
if (!isset($_REQUEST['connect']))
// display correct title depending on whether we just came to site
echo "Intro Page";
else
echo "Query Results";
?>
</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1" />
<meta name="ROBOTS" content="NOINDEX, NOFOLLOW" />
<style type="text/css">
<!--
.center {text-align:center}
.right {text-align:right}
.left {text-align:left}
-->
</style>
</head>
<body>
<div class="center">
<h1>Welcome to the Hw2 Exercise 7.6 Book Query Page</h1>
<?php
if (!isset($_REQUEST['connect']))
{
?>
<h2>Please fill out the form below or use the
default values to connect to the Book database
and see the query results
</h2>
<div class="center">
<form method="get" action= <?php echo '"' . $_SERVER['PHP_SELF'] . '"' ?> >
<table summary="table used for formatting this form">
<tr>
<td class="right">
<label for="connect">
Database Connect String:
</label>
</td>
<td class="left">
<input type="text" name="connect" size="20"
maxlength="40" id="connect"
value="localhost"/>
</td>
</tr>
<tr>
<td class="right">
<label for="Database">
Database:
</label>
</td>
<td class="left">
<input type="text" name="database" size="20"
maxlength="40" id="database"
value="cs297test"/>
</td>
</tr>
<tr>
<td class="right">
<label for="login">
Login:
</label>
</td>
<td class="left">
<input type="text" name="login" size="20"
maxlength="40" id="login"
value="cs297"/>
</td>
</tr>
<tr>
<td class="right">
<label for="password">
Password:
</label>
</td>
<td class="left">
<input type="text" name="password" size="20"
maxlength="40" id="password"
value="cs297"/>
</td>
</tr>
</table>
<p>
<input type="submit" />
</p>
</form>
<?php
}
else
{
echo "<h2>Query Results:</h2>";
$arr = computeQueryResults(20); // Display 20 records per page
computeOutputLinks($arr);
}
?>
</div>
</body>
</html>