Php > Databases

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

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 "<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); 

?>