RE: [PHP-DB] Paging large recordsets
On Fri, 13 Feb 2004, Robert Twitty wrote: > If you are not opearating in a stateless environment, then you could use a > cursor. The web is a stateless environment, and therefore the record set > needs to be cached either to disk or memeory. The other alternative is to > rerun the query for each page request. Using disk space to store query > results for the purpose of paging over the web is commonly done by search > engines, and even some database engines use disk space for cursor > implementation. I agree that using session variables for this purpose is > not ideal, but what's the alternative in PHP? Storing only the > identifiers instead of all the data significantly lessons the impact. > > I agree, it should be avoided if possible. If you are running it out of a DB, limiting the results to result 1-10 or 91-100 is pretty fast; caching results seems like more of a headache to me, and depending on how loaded your DB is, not worth the effort. If the query takes 3-5 seconds, then either your DB is configured wrong (no indexes), or your SQL is not written well, or you should consider some sort of DB query caching. Look to the manual for that. --- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Paging large recordsets
One way that I have found - but never used is... http://sqlrelay.sourceforge.net/ It can cache result sets in a file for later use. You can then use It does a whole bunch of other stuff too. I really need to install this and start working with it. - Paul -Original Message- From: Robert Twitty [mailto:[EMAIL PROTECTED] Sent: Friday, February 13, 2004 1:59 PM To: Paul Miller Cc: [EMAIL PROTECTED] Subject: RE: [PHP-DB] Paging large recordsets If you are not opearating in a stateless environment, then you could use a cursor. The web is a stateless environment, and therefore the record set needs to be cached either to disk or memeory. The other alternative is to rerun the query for each page request. Using disk space to store query results for the purpose of paging over the web is commonly done by search engines, and even some database engines use disk space for cursor implementation. I agree that using session variables for this purpose is not ideal, but what's the alternative in PHP? Storing only the identifiers instead of all the data significantly lessons the impact. I agree, it should be avoided if possible. -- bob On Fri, 13 Feb 2004, Paul Miller wrote: > In no way I am trying start some long thread here. But I have always > heard it was bad to store that much data in a session array? I could > just be really off here and not understanding what I have read. I > know PHP stores the sessions as text files. The only reason I can > come up with why one should not store large amounts of data would be > disk write/read speed per user. > > Can someone clarify this for me? > > - Paul > > -Original Message- > From: Robert Twitty [mailto:[EMAIL PROTECTED] > Sent: Friday, February 13, 2004 12:34 PM > To: Karen Resplendo > Cc: [EMAIL PROTECTED] > Subject: Re: [PHP-DB] Paging large recordsets > > > Most of the PHP solutions I have seeen require the use of session > variables. You could create an array containing only the unique > identifiers of all the records, and then store it into a session > variable. You would then use another session variable to retain the > page size, and then include the page numbers in the "Next", "Prev", > "First", "Last" and "Absolutr" page links. Printing is probably best > done with dynamically generated PDF instead of HTML. > > -- bob > > On Fri, 13 Feb 2004, Karen Resplendo wrote: > > > I guess the time has come that my boss wants "Next", "Previous", > > "First", "Last" paging for our data displays of large recordsets or > > datasets. > > > > Any good solutons out there already? I have pieces of a few > > examples. > > > > Also, how to deal with printing? I would assume that the ideal page > > size is not the ideal printed page size. oi vay! > > > > TIA > > > > > > - > > Do you Yahoo!? > > Yahoo! Finance: Get your refund fast by filing online > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Paging large recordsets
If you are not opearating in a stateless environment, then you could use a cursor. The web is a stateless environment, and therefore the record set needs to be cached either to disk or memeory. The other alternative is to rerun the query for each page request. Using disk space to store query results for the purpose of paging over the web is commonly done by search engines, and even some database engines use disk space for cursor implementation. I agree that using session variables for this purpose is not ideal, but what's the alternative in PHP? Storing only the identifiers instead of all the data significantly lessons the impact. I agree, it should be avoided if possible. -- bob On Fri, 13 Feb 2004, Paul Miller wrote: > In no way I am trying start some long thread here. But I have always > heard it was bad to store that much data in a session array? I could > just be really off here and not understanding what I have read. I know > PHP stores the sessions as text files. The only reason I can come up > with why one should not store large amounts of data would be disk > write/read speed per user. > > Can someone clarify this for me? > > - Paul > > -Original Message- > From: Robert Twitty [mailto:[EMAIL PROTECTED] > Sent: Friday, February 13, 2004 12:34 PM > To: Karen Resplendo > Cc: [EMAIL PROTECTED] > Subject: Re: [PHP-DB] Paging large recordsets > > > Most of the PHP solutions I have seeen require the use of session > variables. You could create an array containing only the unique > identifiers of all the records, and then store it into a session > variable. You would then use another session variable to retain the page > size, and then include the page numbers in the "Next", "Prev", "First", > "Last" and "Absolutr" page links. Printing is probably best done with > dynamically generated PDF instead of HTML. > > -- bob > > On Fri, 13 Feb 2004, Karen Resplendo wrote: > > > I guess the time has come that my boss wants "Next", "Previous", > > "First", "Last" paging for our data displays of large recordsets or > > datasets. > > > > Any good solutons out there already? I have pieces of a few examples. > > > > Also, how to deal with printing? I would assume that the ideal page > > size is not the ideal printed page size. oi vay! > > > > TIA > > > > > > - > > Do you Yahoo!? > > Yahoo! Finance: Get your refund fast by filing online > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Paging large recordsets
On Fri, 13 Feb 2004, Paul Miller wrote:
> I have always heard it was bad to store that much data in a session array?
>
> Can someone clarify this for me?
IMO It is bad to store lots of data in session variables.
The $_REQUEST var for the post/get should be enough.
URL: search.php?page=10
code:
$conf['maxresultsperpage'] = 10;
$ref = mysql_query("select count(*) as c from table where subject like '%foo%'");
list($cnt) = mysql_fetch_array($ref); // will assoc work here? dunno, didn't test
echo "Displaying ".($conf['maxresultsperpage']*$_REQUEST['page'])-9." through
".($conf['maxresultsperpage']*$_REQUEST['page']).".";
$ref = mysql_query("select subject, data from table where subject like '%foo%'
limit ".$conf['maxresultsperpage'].",
".($conf['maxresultsperpage']*$_REQUEST['page'])-10);
// loop through array returned from mysql
echo "Next";
I think. It might need some tweaking, but you get the idea (I hope).
No need to store variables here.
Beckman
> -Original Message-----
> From: Robert Twitty [mailto:[EMAIL PROTECTED]
> Sent: Friday, February 13, 2004 12:34 PM
> To: Karen Resplendo
> Cc: [EMAIL PROTECTED]
> Subject: Re: [PHP-DB] Paging large recordsets
>
>
> Most of the PHP solutions I have seeen require the use of session
> variables. You could create an array containing only the unique
> identifiers of all the records, and then store it into a session
> variable. You would then use another session variable to retain the page
> size, and then include the page numbers in the "Next", "Prev", "First",
> "Last" and "Absolutr" page links. Printing is probably best done with
> dynamically generated PDF instead of HTML.
>
> -- bob
>
> On Fri, 13 Feb 2004, Karen Resplendo wrote:
>
> > I guess the time has come that my boss wants "Next", "Previous",
> > "First", "Last" paging for our data displays of large recordsets or
> > datasets.
> >
> > Any good solutons out there already? I have pieces of a few examples.
> >
> > Also, how to deal with printing? I would assume that the ideal page
> > size is not the ideal printed page size. oi vay!
> >
> > TIA
> >
> >
> > -
> > Do you Yahoo!?
> > Yahoo! Finance: Get your refund fast by filing online
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
---
Peter Beckman Internet Guy
[EMAIL PROTECTED] http://www.purplecow.com/
---
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Paging large recordsets
In no way I am trying start some long thread here. But I have always heard it was bad to store that much data in a session array? I could just be really off here and not understanding what I have read. I know PHP stores the sessions as text files. The only reason I can come up with why one should not store large amounts of data would be disk write/read speed per user. Can someone clarify this for me? - Paul -Original Message- From: Robert Twitty [mailto:[EMAIL PROTECTED] Sent: Friday, February 13, 2004 12:34 PM To: Karen Resplendo Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Paging large recordsets Most of the PHP solutions I have seeen require the use of session variables. You could create an array containing only the unique identifiers of all the records, and then store it into a session variable. You would then use another session variable to retain the page size, and then include the page numbers in the "Next", "Prev", "First", "Last" and "Absolutr" page links. Printing is probably best done with dynamically generated PDF instead of HTML. -- bob On Fri, 13 Feb 2004, Karen Resplendo wrote: > I guess the time has come that my boss wants "Next", "Previous", > "First", "Last" paging for our data displays of large recordsets or > datasets. > > Any good solutons out there already? I have pieces of a few examples. > > Also, how to deal with printing? I would assume that the ideal page > size is not the ideal printed page size. oi vay! > > TIA > > > - > Do you Yahoo!? > Yahoo! Finance: Get your refund fast by filing online -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Paging large recordsets
I know phpclasses.org has a few recordset paging classes out there for your convenience. Might want to try on a couple of those too just to see if they fit. Rich > -Original Message- > From: Robert Twitty [mailto:[EMAIL PROTECTED] > Sent: Friday, February 13, 2004 1:34 PM > To: Karen Resplendo > Cc: [EMAIL PROTECTED] > Subject: Re: [PHP-DB] Paging large recordsets > > > Most of the PHP solutions I have seeen require the use of session > variables. You could create an array containing only the unique > identifiers of all the records, and then store it into a > session variable. > You would then use another session variable to retain the > page size, and > then include the page numbers in the "Next", "Prev", "First", > "Last" and > "Absolutr" page links. Printing is probably best done with > dynamically > generated PDF instead of HTML. > > -- bob > > On Fri, 13 Feb 2004, Karen Resplendo wrote: > > > I guess the time has come that my boss wants "Next", > "Previous", "First", "Last" paging for our data displays of > large recordsets or datasets. > > > > Any good solutons out there already? I have pieces of a few > examples. > > > > Also, how to deal with printing? I would assume that the > ideal page size is not the ideal printed page size. oi vay! > > > > TIA > > > > > > - > > Do you Yahoo!? > > Yahoo! Finance: Get your refund fast by filing online > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Paging large recordsets
Most of the PHP solutions I have seeen require the use of session variables. You could create an array containing only the unique identifiers of all the records, and then store it into a session variable. You would then use another session variable to retain the page size, and then include the page numbers in the "Next", "Prev", "First", "Last" and "Absolutr" page links. Printing is probably best done with dynamically generated PDF instead of HTML. -- bob On Fri, 13 Feb 2004, Karen Resplendo wrote: > I guess the time has come that my boss wants "Next", "Previous", "First", "Last" > paging for our data displays of large recordsets or datasets. > > Any good solutons out there already? I have pieces of a few examples. > > Also, how to deal with printing? I would assume that the ideal page size is not the > ideal printed page size. oi vay! > > TIA > > > - > Do you Yahoo!? > Yahoo! Finance: Get your refund fast by filing online -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Paging large recordsets
On Fri, 13 Feb 2004, Karen Resplendo wrote: > I guess the time has come that my boss wants "Next", "Previous", "First", > "Last" paging for our data displays of large recordsets or datasets. First, do a query to find out how many rows. select count(*) from table where (your where clauses for the query); That's your # of records. Then do: select count(*) from table where (your clauses) limit ($pagenum*$itemlimit)-$itemlimit), $itemlimit; so if your $itemlimit = 10 items per page, and you are on page 3, it would be ... limit 20, 10 page #1, limit 0,10 etc > Also, how to deal with printing? I would assume that the ideal page size > is not the ideal printed page size. oi vay! In IE 6, this works: Even if your text follows, IE will print a page break. Haven't researched how to do it in Mozilla or Netscape. Beckman --- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Paging large recordsets
I guess the time has come that my boss wants "Next", "Previous", "First", "Last" paging for our data displays of large recordsets or datasets. Any good solutons out there already? I have pieces of a few examples. Also, how to deal with printing? I would assume that the ideal page size is not the ideal printed page size. oi vay! TIA - Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online
