Limiting the display of rows - Need conceptual help

BLottman

Like most newbies, one of the problems I'm having is to limit the display of 1000s of rows to a defined limit. I'd like to display
Prev 1 2 3 4 5 Next links at the bottom of the list of rows.

I've reviewed and tried unsuccessfully for hours if not days to implement code segments from a number of contributors but they all fail to work. I think the difficulty I'm having is understanding the concept and purpose of the variables like $limit, $offset, $diff etc.

Can someone provide me with the concept and explain the purpose and description of the variables used?

This newbie would sure appreciate your help.

Bill😕

raymie

http://www.phpbuilder.com/columns/rod20001214.php3

klansman

hello!

that is fairly simple to implement.

all you have to do is to send a "get" variable to the script (or use post, if you like better) with an offset.

with that offset, you can make your select queries give your results from that offset (rejecting the previous rows - check the mysql documentation for the select ... limit syntax, to include offsets).

Then, you need to choose or ask for a row limit, for obvious reasons. this limit variable is used in the same select query as the offset.

then all you have to know is how many sql rows are left to show and how many you have shown, so that you can calculate the number of pages (packs of $limit rows) left to show and already shown.

hope it helped...

you can view an example of this working in http://arrakis.dhis.org/news - watch the address bar of the browser while clicking the next/previous buttons.

[]

BLottman

I checked the MySQL documentation and interestingly on SELECT query's where their is a "SORT BY" clause and a LIMIT clause in the same query, the engine will only display the first limit=n rows of data (i.e., if the variable used is $limiter = 10). Sorting on a table of 10,000s of thous of rows would take an enormous amount of time. So, according to the MySQL docs, it will only display the first 10 rows and no more. This is what happens to my script. Maybe my code was missing something.

I also checked the ref rayme provided but the same issue exists using LIMIT and SORT BY.

Your code uses SORT BY and LIMIT in the same query. Does your display stop after the first n limit rows? Mine stops after the first n=limit rows. How can I get around this? I need the rows sorted by a couple of columns minimum and limit the output to 25 rows max.

since then, I changed my approach to using a class based require() file entitled "pagedresults.php" provided by Kevin Yank. Although the pagedresults.php is not working either, I'd like to hear your comments. On the MySQL Select/LIMIT criteria. Thanks.

klansman

I checked the MySQL documentation and interestingly on SELECT query's where their is a "SORT BY" clause and a LIMIT clause in the same query, the engine will only display the first limit=n rows of data (i.e., if the variable used is $limiter = 10). Sorting on a table of 10,000s of thous of rows would take an enormous amount of time. So, according to the MySQL docs, it will only display the first 10 rows and no more. This is what happens to my script. Maybe my code was missing something.

so your problem is the consumed time? please clear that up a little bit more because after reading your whole reply, i got lost.

appearently you can sort the results and limit the display... but you are worried with the processing time of the script? is that it? or is it something else??

if it is the consumed time, you should consider adding some indexes to your sql tables to speed up (a lot) the select+sort query... did it help??

[]

BLottman

I can limit the results. I performed a test and set the $limiter variable value to 5 so that only five rows display per page. The first page results display. Then I press the Next Page link and no more rows display.

Researching why, I found MySQL will not send further query results, even if the rows are embedded in a While { } loop, if the query contains both SORT BY and LIMIT in the statement.

Is this why I can't display more than the $limiter value in the 2nd page and beyond? I've read many contributor statements that have provided examples and code segments about the Previous 1 2 3 4 Next approach but none mention the problem associated with SORT BY and LIMIT in the same query statement.

Can someone tell me if their code segment works using the two conditions above? Does this affect your code the same way as mine? I'm trying to implement this feature but have the belief that it works for everyone else but me.

klansman

i understand you problem.

please correct me if i am wrong...

you are making a query using "limit" in your select. then you show $limit rows in a table. then you want to change page and without making another query, display another $limit rows?

if that's it, than you are absolutely right. it does not work (as you can see, the hard way).

You can correct the problem by one of two ways (at least):

change the select query removing the "limit" - get all rows and keep track of what rows you shown and what rows you haven't shown (maybe with some get variables). i am sure you can figure this out... if not, say something, i can think of a couple ways to do it.
make a select query in every "page", so that for page 1 you will select $limit rows with offset 0. in page2 you will select $limit rows with offset $limit. in page 3 you will select $limit rows witn offset 2*$limit... u get the point...

I advise you method number 2 for small databases, but in your case, i guess method 1 is much more efficient.

hope it helped.

[]

BLottman

Thanks for the reply. I'm going to try and use Get variables. Although I've never used them before, I'll do a little reading to understand how to implement them.

I'll let you know how they work out later.

Thanks.

zalath

As you need conceptual help, here's the concept:

you're on the right track, but you're looking at it the wrong way.

You're not going through the same recordset n times, you're performing a query n times with varying parameters.

So you have displayed the first $limit number of rows? very good, you know how to use LIMIT then.

Build your query like so:

$offset = intval($_GET["offset"]);
$end = $offset + $limit;

$query = "SELECT ... FROM ... WHERE ... LIMIT $offset,$end";

(supposing your $limit is set to 10, the result is LIMIT 0,10 on the first go)

then for the next set of results, make a link that refers to the script itself, and pass a variable:

Now, after the first run that would link to scriptname.php?offset=10

which in turn would result in LIMIT 10,20 on the next run.

BLottman

Is $limit, $offset, and $end arbitrary variables you define or are they reserved words associated with the LIMIT parameter in the Select query?

I understand that "Select....LIMIT 0,10" means that 10 rows will display starting at the position of the first row.

After this "Select ....LIMIT 11, 10" means display the rows up to 10 each starting at the 11th resulting row.

So $offset is really the row at which to begin display and $end is really the number of rows to limit the output to?

Where does the $limit variable come into play?

Thanks for taking the time to help me to understand. I appreciate it much.

wilsonodk

$limit, $offset, and $end are arbitrary names, you could use $foo, $bar, $begin. But then later, it wouldn't make much sense 🙂

$limit is the number of entries that you want to show on each page. In his case it was 10, it might be 12, in which case...
page 1 would show entry 1 through entry 12,
page 2 would show entry 13 through entry 24,
page 3 would show entry 25 through entry 36,
etc.

Good Luck to you...

BLottman

Thanks for the help. It's now making sense.