Script-for-postgresql-to-walk-through-the-results-limiting-the-results-shown-per


            // 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 "\n"; 
echo "\n"; 
echo "\n"; 
echo "\n"; 
echo "\n"; 
echo "\n"; 
echo "\n"; 
echo "\n"; 
echo " \n"; 
echo "\n"; 
echo "
\n"; 
echo "
\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 "\n"; 
echo "\n"; 
echo "\n"; 
echo "\n"; 
 // the top button will always show along with either 
 // the backward or forward button(s) 
 echo "\n"; 
 echo "\n"; 
echo "\n"; 
echo "\n"; 
echo "$last of " . pg_NumRows($result); 
echo "\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 "\n"; 
 // set value for start to start minus the page size to show 
 $incback = ($start - $pagesize); 
 echo "\n"; 
 echo "\n"; 
 echo "\n"; 
 echo "
\n"; 
 } 
 echo " \n"; 
 echo "\n"; 
 echo "\n"; 
 echo "\n"; 
 echo "
\n"; 
 echo " \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 "\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 "\n"; 
 echo "\n"; 
 echo ">\">\n"; 
 echo "
\n"; 
 } 
} // end show buttons 
echo "
\n"; 
// now show loop through results from query for items 
while ($start < $last) 
{ 
 echo "\n"; 
 echo "\n"; 
 echo "\n"; 
 // show number in human format i.e. zero is one 
 $startpos = $start + 1; 
 echo "\n"; 
 echo "\n"; 
 echo pg_Result($result, $start, "first"); 
 echo " \n"; 
 echo pg_Result($result, $start, "second"); 
 echo " \n$startpos
\n"; 
 $start++; 
} // end display loop of items from query 
// free results and then close connection to database 
pg_FreeResult($result); 
pg_Close($conn); 
?>
MySQL Database Php
