Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 1st, 2004, 08:52 PM
Authorized User
Join Date: Apr 2004
Posts: 69
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to junemo
Default Please.....Pagination

Hi, I want craete a page that can list of record in html format. Let say the query may result in hundreds or thousands of records. Then, I need create button PREV and NEXT.

1. Isn't I need to create a procedure and run at SQL* Plus, i.e.

create or replace procedure paginate( p_start in number default 1)
l_next boolean default false;
l_pagesize number default 50;
htp.bold( 'p_start =' || P_start);
for x in (select *
from (select a.*, rownum r from bill a
where rownum <= p_start+l_pagesize) a
where r >= p_start)
l_next :=( x.r = p_start=l_pagesize);
htp.p( '<tr><td>' || x.r || '</td><td>' ||
x.invno || '</td></tr>');
end loop;
htp.p( '</table>');

if( p_start >1)
htp.formOpen( 'paginate');
htp.formHidden( 'p_start', p_start-l_pagesize);
htp.formSubmit( cvalue => 'prev' || (p_start-l_pagesize));
end if;
htp.formOpen( 'paginate');
htp.formHidden( 'p_start', (p_start+l_pagesize));
htp.formSubmit( cvalue => 'next' || (p_start+l_pagesize));
end if;

Are this code write in correctly? Do have duplicate record?

2. After I have create a procedure, how I want apply this in my application code(PHP)?
$connection = OCILogon("prod", "prod");
$stmt = OCIParse($connection, "SELECT * FROM ( SELECT a.*, rownum rnum
                                                    FROM (SELECT invno FROM bill ORDER BY invno) a
                                                    WHERE rownum < 50)
                                                    WHERE rnum >= 1 ");
$rownum = OCIFetchStatement( $stmt, $results);
//display all entries
for ( $i=0; $i<$rownum; $i++ )
echo " <td class=text8Arial11> " .$results["INVNO"][$i]."</td>";
echo "</tr>";

3. Should I need create a button PREV and NEXT links in <html> tag?i.e.
<a href="javascript:doSubmit_prev();">Previous 50 Records</a>
<a href="javasrcipt:doSubmit_next();">Next 50 Records</a>

Then, where should I POST this form method each time I click the button NEXT / PREV?

4. If this more complicate way, are there have other more easier and understanding solution way to solve it?

Thanks in advance.

Oracle 8.1.7
PHP 4.3.4
Apache / IIS webserver

Old September 25th, 2004, 01:04 PM
Authorized User
Join Date: Aug 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to dkittell Send a message via AIM to dkittell Send a message via Yahoo to dkittell

A code to try:

<table cellpadding="2" cellspacing="0" width="100%" border="1" bordercolor="#333333">
<table cellpadding="2" cellspacing="0" width="100%" border="0" bordercolor="#333333">
<td width="10" class="tablebottomsb"><strong>Date</strong></td>
<td class="tablebottomsb"><strong>Title</strong></td>
<td class="tablebottomsb">&nbsp;</td></tr>

  // Get the search variable from URL

  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost", "dbuser", "dbpass"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("dbname") or die("Unable to select database");
//select which database we're using

// Build SQL Query  
$query = "select * from bulletin WHERE bulletin_flags=0 order by bulletin_date DESC";
// EDIT HERE and specify your table and field names for the SQL query


// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  echo "<p>Sorry, we currently don't have information here.</p>";

// google
 echo "<p><a href=\"http://www.google.com/search?q=" 
  . $trimmed . "\" target=\"_blank\" title=\"Look up 
  " . $trimmed . " on Google\">Click here</a> to try the 
  search on google</p>";

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

// begin to show results set
//echo "Results";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {

  $currentDate = $row["bulletin_date"];
  if ($currentDate != $lastDate)
    if ($cellBgColor=="#FFFFFF")
        $cellBgColor = "#CCCCCC";
        $cellBgColor = "#FFFFFF";
  $date = $row['bulletin_date'];
  $series = $row['bulletin_series'];
  $title = $row['bulletin_title'];

    echo "<tr bgcolor=" . $cellBgColor . "><td>" . strftime("%m/%d/%Y",strtotime($date)) . 
    "</td><td>" . $series . $title . "</td><td><a href='bulletin/" . $row['bulletin_id_k'] .
    ".pdf' target=newwindow><img src=images/view.gif border='0' height='14' width='14'
align='absmiddle' alt='View this Bulletin.'></a></td></tr>" ;
  $count++ ;
  $lastDate = $currentDate;

$currPage = (($s/$limit) + 1);

//break before paging
  echo "</table><br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
  Prev 10</a>&nbsp&nbsp;";

// calculate number of pages needing links

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  //echo "<p>Showing results $b to $a of $numrows</p>";

You can view an example of this code at http://muthbible.com/bulletin.php

I want to make a point that I didn't create this code, I found it on the web, but this is working on a few of my sites flawlessly.

David Kittell
Old September 25th, 2004, 01:14 PM
Authorized User
Join Date: Aug 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to dkittell Send a message via AIM to dkittell Send a message via Yahoo to dkittell

I believe pasted too many closing tags in there, but it should help you.

David Kittell

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pagination with checkboxes argan Pro JSP 2 November 28th, 2008 12:48 AM
Grouping and Pagination jayne XSLT 13 June 6th, 2007 01:52 AM
pagination problem solos XSLT 1 April 4th, 2007 05:40 PM
pagination Regornil Pro JSP 2 May 31st, 2005 06:10 AM
pagination junemo Oracle 0 July 1st, 2004 04:07 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.