Session based paged listing? 1

[Olavxxx]

Hi,

I'm currently making a ShoutBox script, which will be quite simple. However, for a paged listing, as far as I know, I would have to run two queries.

One of them, would have to check the number of rows, to know the limit calculus end.

Eg.

$numrows = mysql_num_rows($get_main);
// how many pages we have when using paging?
$maxPage = ceil($numrows/$limitposts);

This has to run after a query, and this means that I would have to run two queries?

So, my idea -- Which I hope you have feeback on, is this:

I run a query, where I retrieve all rows.
This query I put in a session array, as then I can simply do a count() on the array and easilly generate a paged listing.

This will also give less stress on the database, as it wont have to run a new query with a new limit for each time.

Ps. I know that mysql will "buffer" a bit, but even so, I think the session will give less system stress?

Ps. It's a small page, and I guess noone will "never" bother to check the other pages in the shoutbox..
(shoutbox / guestbook).

My idea, is:
* Retrieve all rows, put in session array
* Generate paged listing, based on count($session_array) and divided on $perpage.

I would have to have some way to "refresh" the session array.

Is this a "dumb" idea, should I just as well run two queries? In my silly mind, I think it would be better to store the results in a session and then maybe set expires after some minutes, if on first page of listing.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[sleipnir214]

Let's say you have 100 mostly simultaneous users.

Your idea will download the entire database 100 times, then make 100 copies on the server's filesystem. That doesn't sound like a good idea.


I would do the two queries: one a "SELECT <columnname> ... LIMIT..." query to fetch the limited number of records necessary to populate the page and one a "SELECT count(*) from <tablename>" query to get the number of records.

MySQL optimizes a "SELECT count(*) from <tablename>" query quite well. I remember once reading somewhere on the MySQL site that a query of that type doesn't even check the data in the table. It checks table metadata instead.

And unless you know for a fact that every user will look at every record in your database every time they visit your site, downloading the entire database for every user is counterproductive.


Also, see faq434-5244


Want the best answers? Ask the best questions! TANSTAAFL!

 

[Olavxxx]

Thank you for the technical insight.
The current page that I'm working on is REALLY small, but even so, I believe in good programming practice.

This also means that my old way is the highway!

Good night!

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster