Mega Code Archive
Script for postgresql to walk through the results limiting the results shown per
<![CDATA[
<?
// This is a script to walk through the results of a query to a postgresql
// database and display a set number of results per page rather than
// displaying them all on one page.
// Define the db connect variables, the queries and the results per page
// The example query is a table set up as:
//
// create table authors
// ( first text,
// second text);
//
// Also built in is a method for users to select how the results are sorted
// name the db connection variables
$host = "localhost";
$port = "5432";
$dbname = "predb";
// set up the queries, this could come from a form on another page
$sort_default = array(" SELECT * FROM authors ", "Natural Sort");
$sort_second = array(" SELECT * FROM authors ORDER BY second ", "Last Name Sort");
$sort_first = array(" SELECT * FROM authors ORDER BY first ", "First Name Sort");
// set up a default query if none is set
if (!IsSet($query) ) :
$query = $sort_default[0];
endif;
// allow users to select how the query is sorted
echo "<form action=\"$PHP_SELF\" method=\"POST\">\n";
echo "<table summary=\"Select Sort Table\">\n";
echo "<tr>\n";
echo "<td>\n";
echo "<select name=\"query\">\n";
// test values and put asterisk so users know the default sort
echo "<option value=\"$sort_default[0]\"";
if ($query == $sort_default[0])
{ echo " SELECTED > "; }
else
{ echo " >"; }
echo "$sort_default[1]</option>\n";
echo "<option value=\"$sort_second[0]\"";
if ($query == $sort_second[0])
{ echo " SELECTED > "; }
else
{ echo " >"; }
echo "$sort_second[1]</option>\n";
echo "<option value=\"$sort_first[0]\"";
if ($query == $sort_first[0])
{ echo " SELECTED > "; }
else
{ echo " >"; }
echo "$sort_first[1]</option>\n";
echo "</select>\n";
echo "</td>\n";
echo "<td>\n";
echo "<input type=\"SUBMIT\" value=\"GO\">\n";
echo "</td>\n";
echo "</tr>\n";
echo "</table>\n";
echo "</form>\n";
// set how many records will appear per page
$pagesize = 5;
// if first time in to run query, test and set start variable
if (!IsSet($start) ) :
$start = 0;
endif;
// connect to the database, variables defined at top
$conn = pg_Connect("$host", "$port", "", "", "$dbname");
if (!$conn)
{
echo "A Connect to DB Error Occured\n";
exit;
}
// run query on table in database
$result = pg_Exec ($conn, "$query ;");
if (!$result)
{
echo "An error occured.\n";
exit;
}
// find out which is less, query results or start plus pagesize
// assign that variable to last
$last = min($start + $pagesize, pg_NumRows($result));
// let people know where they are as they walk through table
echo "<table>\n";
echo "<caption>\n";
echo "$last of " . pg_NumRows($result);
echo "</caption>\n";
echo "<tr>\n";
echo "<td align=\"right\" width=\"100\">\n";
// if results of query less than the results to show per page
// then do not show back, forth and top buttons
if (pg_NumRows($result) <= $pagesize )
{ echo "\n"; }
else // begin show buttons
{
// if the value of last is not equal to pagesize then
// show backward button, one or the other will show
// along with the top button
if ($last != $pagesize)
{
echo "<form action=\"$PHP_SELF\" method=\"POST\">\n";
// set value for start to start minus the page size to show
$incback = ($start - $pagesize);
echo "<input type=\"HIDDEN\" name=\"start\" value=\"$incback\">\n";
echo "<input type=\"HIDDEN\" name=\"query\" value=\"$query\">\n";
echo "<input type=\"SUBMIT\" value=\"<< Backward\">\n";
echo "</form>\n";
}
echo "</td>\n";
// the top button will always show along with either
// the backward or forward button(s)
echo "<td align=\"center\">\n";
echo "<form action=\"$PHP_SELF\" method=\"POST\">\n";
echo "<input type=\"HIDDEN\" name=\"query\" value=\"$query\">\n";
echo "<input type=\"SUBMIT\" value=\"TOP\">\n";
echo "</form>\n";
echo "</td>\n";
echo "<td align=\"left\" width=\"100\">\n";
// if value of start plus page size is
// NOT greater than or equal to rows returned
// show forward button
if (!( ($start + $pagesize) >= pg_NumRows($result) ) )
{
echo "<form action=\"$PHP_SELF\" method=\"POST\">\n";
// set value for start to start plus the page size to show
// no errors will occur if the start is greater than the
// results available in showing results
$incfor = ($start + $pagesize);
echo "<input type=\"HIDDEN\" name=\"start\" value=\"$incfor\">\n";
echo "<input type=\"HIDDEN\" name=\"query\" value=\"$query\">\n";
echo "<input type=\"SUBMIT\" value=\"Forward >>\">\n";
echo "</form>\n";
}
} // end show buttons
echo "</td>\n";
echo "</tr>\n";
echo "</table>\n";
// now show loop through results from query for items
while ($start < $last)
{
echo "<table border=\"1\">\n";
echo "<tr>\n";
echo "<td width=\"100\">\n";
echo pg_Result($result, $start, "first");
echo "</td><td width=\"100\">\n";
echo pg_Result($result, $start, "second");
echo "</td>\n";
// show number in human format i.e. zero is one
$startpos = $start + 1;
echo "<td align=\"right\" width=\"36\">\n$startpos</td></tr>\n";
echo "</table>\n";
$start++;
} // end display loop of items from query
// free results and then close connection to database
pg_FreeResult($result);
pg_Close($conn);
?>
]]>