Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 1st, 2004, 08:52 PM
Authorized User
 
Join Date: Apr 2004
Location: malacca, malacca, Malaysia.
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)
as
l_next boolean default false;
l_pagesize number default 50;
begin
htp.bold( 'p_start =' || P_start);
htp.p('<table>');
for x in (select *
from (select a.*, rownum r from bill a
where rownum <= p_start+l_pagesize) a
where r >= p_start)
loop
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)
then
htp.formOpen( 'paginate');
htp.formHidden( 'p_start', p_start-l_pagesize);
htp.formSubmit( cvalue => 'prev' || (p_start-l_pagesize));
htp.formClose;
end if;
if(l_next)
then
htp.formOpen( 'paginate');
htp.formHidden( 'p_start', (p_start+l_pagesize));
htp.formSubmit( cvalue => 'next' || (p_start+l_pagesize));
htp.formClose;
end if;
end;
/

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)?
i.e.
<html>
.....
<?php
require_once("DB.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 ");
OCIExecute($stmt);
$rownum = OCIFetchStatement( $stmt, $results);
//display all entries
for ( $i=0; $i<$rownum; $i++ )
{
echo " <td class=text8Arial11> " .$results["INVNO"][$i]."</td>";
echo "</tr>";
}
OCILogOff($connection);
?>
.....
</html>

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?

Please...
Thanks in advance.

Oracle 8.1.7
PHP 4.3.4
Apache / IIS webserver
Win2000


Reply With Quote
  #2 (permalink)  
Old September 25th, 2004, 01:04 PM
Authorized User
 
Join Date: Aug 2003
Location: Bay City, mi, USA.
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
Default

A code to try:

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

  // Get the search variable from URL

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

// rows to return
$limit=10; 

//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

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

// 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)) {
  $s=0;
  }

// 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";
    }
    else
    {
        $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
  $prevs=($s-$limit);
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
  Prev 10</a>&nbsp&nbsp;";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

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

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

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

  // not last page so give NEXT link
  $news=$s+$limit;

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

?>
</td></tr></table></td></tr></table></td></tr></table>
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
Reply With Quote
  #3 (permalink)  
Old September 25th, 2004, 01:14 PM
Authorized User
 
Join Date: Aug 2003
Location: Bay City, mi, USA.
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
Default

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

---
David Kittell
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pagination with checkboxes argan Pro JSP 2 November 27th, 2008 11:48 PM
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



All times are GMT -4. The time now is 07:46 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.