first-prev-next-last using PHP

[AR John]

Hi Everyone,

I was developing a browser based software and one of the features of
that software is that it will search some products with some matching
criteria and the result will be displayed the product names as a
links. Sometimes I got few thousands of product names and I want to
show 30 items at a time using html. I was using MySql as database and
PHP for programming.

Could anybody tell me how it is possible to show the result with 30
items each time having "first-prev-next-last" links? Where to store
the result? How can handle multiple user access?

Thanks
AR

[Tim Van Wassenhove]

Re: first-prev-next-last using PHP

On 2003-12-07, AR John <arjohn7681@yah oo.com> wrote:[color=blue]
> Hi Everyone,
>
> I was developing a browser based software and one of the features of
> that software is that it will search some products with some matching
> criteria and the result will be displayed the product names as a
> links. Sometimes I got few thousands of product names and I want to
> show 30 items at a time using html. I was using MySql as database and
> PHP for programming.
>
> Could anybody tell me how it is possible to show the result with 30
> items each time having "first-prev-next-last" links? Where to store
> the result? How can handle multiple user access?[/color]

AFAIK there is no way to store resultsets. So you would have to perform
the query for each page, and then use LIMIT limit,offset to filter out
the rows you want to show. Having an index on the columns you use for
your selection might speed things up.

--
verum ipsum factum

[Tom]

Re: first-prev-next-last using PHP

You may want to have a look at this tutorial:

http://www.phpfreaks.com/tutorials/73/0.php

"Tim Van Wassenhove" <euki@pi.be> wrote in message
news:bqus23$26m uvp$3@ID-188825.news.uni-berlin.de...[color=blue]
> On 2003-12-07, AR John <arjohn7681@yah oo.com> wrote:[color=green]
> > Hi Everyone,
> >
> > I was developing a browser based software and one of the features of
> > that software is that it will search some products with some matching
> > criteria and the result will be displayed the product names as a
> > links. Sometimes I got few thousands of product names and I want to
> > show 30 items at a time using html. I was using MySql as database and
> > PHP for programming.
> >
> > Could anybody tell me how it is possible to show the result with 30
> > items each time having "first-prev-next-last" links? Where to store
> > the result? How can handle multiple user access?[/color]
>
> AFAIK there is no way to store resultsets. So you would have to perform
> the query for each page, and then use LIMIT limit,offset to filter out
> the rows you want to show. Having an index on the columns you use for
> your selection might speed things up.
>
> --
> verum ipsum factum[/color]

[AR John]

Re: first-prev-next-last using PHP

Thanks for the idea.

Tim Van Wassenhove <euki@pi.be> wrote in message news:<bqus23$26 muvp$3@ID-188825.news.uni-berlin.de>...[color=blue]
> On 2003-12-07, AR John <arjohn7681@yah oo.com> wrote:[color=green]
> > Hi Everyone,
> >
> > I was developing a browser based software and one of the features of
> > that software is that it will search some products with some matching
> > criteria and the result will be displayed the product names as a
> > links. Sometimes I got few thousands of product names and I want to
> > show 30 items at a time using html. I was using MySql as database and
> > PHP for programming.
> >
> > Could anybody tell me how it is possible to show the result with 30
> > items each time having "first-prev-next-last" links? Where to store
> > the result? How can handle multiple user access?[/color]
>
> AFAIK there is no way to store resultsets. So you would have to perform
> the query for each page, and then use LIMIT limit,offset to filter out
> the rows you want to show. Having an index on the columns you use for
> your selection might speed things up.[/color]

[Adi Schwarz]

Re: first-prev-next-last using PHP

AR John wrote:[color=blue]
>
> Could anybody tell me how it is possible to show the result with 30
> items each time having "first-prev-next-last" links? Where to store
> the result? How can handle multiple user access?[/color]

Has anybody tried to store the result into a PHP session (if it is not
too large)? I suppose that would be faster to do and save database
server load...

[Matty]

Re: first-prev-next-last using PHP

Adi Schwarz wrote:
[color=blue]
> AR John wrote:[color=green]
>>
>> Could anybody tell me how it is possible to show the result with 30
>> items each time having "first-prev-next-last" links? Where to store
>> the result? How can handle multiple user access?[/color]
>
> Has anybody tried to store the result into a PHP session (if it is not
> too large)? I suppose that would be faster to do and save database
> server load...[/color]

select *
from mytable
order by some_field
limit zero_based_offs et, record_count;

HTH

Matt

[Adi Schwarz]

Re: first-prev-next-last using PHP

Matty wrote:[color=blue]
> Adi Schwarz wrote:
>
>[color=green]
>>AR John wrote:
>>
>>
>>Has anybody tried to store the result into a PHP session (if it is not
>>too large)? I suppose that would be faster to do and save database
>>server load...[/color]
>
>
> select *
> from mytable
> order by some_field
> limit zero_based_offs et, record_count;[/color]

Of course that works, but the point is that this query is executed once
for every page of the result set -> the server gets all rows, sorts them
and then takes the rows he needs (normally the minority of all rows) -
for every single page, always (almost) the same query. I would say it
saves database server load if this is only done once.

-as

[Matty]

Re: first-prev-next-last using PHP

Adi Schwarz wrote:
[color=blue][color=green]
>> select *
>> from mytable
>> order by some_field
>> limit zero_based_offs et, record_count;[/color]
>
> Of course that works, but the point is that this query is executed once
> for every page of the result set -> the server gets all rows, sorts them
> and then takes the rows he needs (normally the minority of all rows) -
> for every single page, always (almost) the same query. I would say it
> saves database server load if this is only done once.
>[/color]

Depends on how your code is written, whether you use persistent
connections, etc. Bear in mind, that if the user is only likely to want
to see 10 records, then it's maybe a little wasteful to fetch 2000 that
they won't ever see.

If you're talking about caching the actual data returned, then look at
using an application-level cache. I personally go the roll-your-own
route, but PEAR has a Pear::Cache class (or similar) that does this.

And just how large would the entire result be? How much do you want to pull
across the network connection to the DB server, how much do you want to
serialize/deserialize to/from disk?

Better still, why not just look at caching the page output (depending on
your application), saving executing most of your php and database code at
all?

[Tim Van Wassenhove]

Re: first-prev-next-last using PHP

On 2003-12-12, Adi Schwarz <adolf.schwarz. news.12-03@gmx.at> wrote:[color=blue]
> Matty wrote:[color=green]
>> Adi Schwarz wrote:
>>
>>[color=darkred]
>>>AR John wrote:
>>>
>>>
>>>Has anybody tried to store the result into a PHP session (if it is not
>>>too large)? I suppose that would be faster to do and save database
>>>server load...[/color]
>>
>>
>> select *
>> from mytable
>> order by some_field
>> limit zero_based_offs et, record_count;[/color]
>
> Of course that works, but the point is that this query is executed once
> for every page of the result set -> the server gets all rows, sorts them
> and then takes the rows he needs (normally the minority of all rows) -
> for every single page, always (almost) the same query. I would say it
> saves database server load if this is only done once.[/color]

You could create a temporary table, insert the rows in there...
And then retrieve data from this table,...

--
verum ipsum factum