EcalCondDB/webapp/pager_functions.php

0001 <?php
0002 /*
0003  * pager_functions.php
0004  *
0005  * Set of functions to return a paged result from an SQL query
0006  * Credit:  Inspired by Harry Fuecks article
0007  * http://www.oracle.com/technology/pub/articles/oracle_php_cookbook/fuecks_paged.html
0008  *
0009  */
0010
0011 include('Net/URL.php');
0012
0013 /* Returns an <a> tag with a link to $url and the page property set to $page */
0014 function page_a($url, $page, $text) {
0015   $url_obj = new Net_URL($url);
0016   $url_obj->addQueryString('page', $page);
0017   $newurl = $url_obj->getURL();
0018   return "<a href='$newurl'>$text</a>";
0019 }
0020
0021 /* Draws a pager with links to $url, given the $total_pages and $current_page*/
0022 function draw_pager($url, $total_pages, $current_page = 1, $new_search = NULL) {
0023   echo "<div class='pager'>";
0024
0025   if ($current_page <=0 || $current_page > $total_pages ) {
0026     $current_page = 1;
0027   }
0028
0029   if ($current_page > 1) {
0030     echo page_a($url, 1, '[Start]'), " \n";
0031     echo page_a($url, ($current_page-1), '[Prev]'), " \n";
0032   }
0033
0034   for ($i = ($current_page-5); $i <= $current_page+5; $i++) {
0035     if ($i < 1) continue;
0036     if ($i > $total_pages) break;
0037
0038     if ($i != $current_page) {
0039       echo page_a($url, $i, $i), " \n";
0040     } else {
0041       echo page_a($url, $i, "<strong>$i</strong>"), " \n";
0042     }
0043   }
0044
0045   if ($current_page < $total_pages) {
0046     echo page_a($url, ($current_page+1), '[Next]'), " \n";
0047     echo page_a($url, $total_pages, '[End]'), " \n";
0048   }
0049
0050   if ($new_search) {
0051     echo "<a href='$new_search'>[New Search]</a>";
0052   }
0053
0054   echo "</div>";
0055 }
0056
0057 /* Returns the total number of pages given $total_rows and $rows_per_page */
0058 function total_pages($total_rows, $rows_per_page) {
0059   if ($total_rows < 1) $total_rows = 1;
0060   return ceil($total_rows/$rows_per_page);
0061 }
0062
0063 /* Returns the starting row for $current_page given $rows_per_page */
0064 function page_to_row($current_page, $rows_per_page) {
0065   $start_row = ($current_page-1) * $rows_per_page +1;
0066   return $start_row;
0067 }
0068
0069 /* Counts the number of rows given by $select.  Binding done with $binds array */
0070 function count_rows(& $conn, $select, $binds) {
0071   $sql = "SELECT COUNT(*) AS num_rows FROM($select)";
0072   $stmt = oci_parse($conn, $sql);
0073   foreach ($binds as $handle => $var) {
0074     oci_bind_by_name($stmt, $handle, $binds[$handle]);
0075   }
0076   oci_define_by_name($stmt, "NUM_ROWS", $num_rows);
0077   oci_execute($stmt);
0078   oci_fetch($stmt);
0079   return $num_rows;
0080 }
0081
0082 /* Returns an statement handle containing the results of the query $select, from $start_row to ($start_row+$rows_per_page) */
0083 function & paged_result(& $conn, $select, $binds, $start_row, $rows_per_page) {
0084   $end_row = $start_row + $rows_per_page -1;
0085
0086   $sql = "SELECT * FROM ( SELECT r.*, ROWNUM AS row_number FROM ( $select ) r WHERE ROWNUM <= :end_row ) WHERE :start_row <= row_number";
0087
0088   $stmt = oci_parse($conn, $sql);
0089   $binds[':start_row'] = $start_row;
0090   $binds[':end_row'] = $end_row;
0091   foreach ($binds as $handle => $var) {
0092     oci_bind_by_name($stmt, $handle, $binds[$handle]);
0093   }
0094
0095   oci_execute($stmt);
0096
0097   oci_set_prefetch($stmt, $rows_per_page);
0098
0099   return $stmt;
0100 }
0101 ?>