?
Solved

Limit query per page

Posted on 2011-03-08
10
Medium Priority
?
339 Views
Last Modified: 2012-08-14
Hi Guys,

I have a problem understanding setting mysql query limit per page.
below is the current scripts i have in page that generates the page split.
can someone explain in simple words what does this code mean? i am trying to change the limit and what happens is i am getting empty pages e.g. "page2", "page3" under the table but no data. or i am trying to generate more records per page and i get an error. I simply do not understand what this do.

 
above the query:
=============================================
if((int)$_GET['p']<1) $page=0;
	 else $page=(int)$_GET['p']-1;
	 $query_start=20*$page;
	 $query_end=$query_start+20;


query limit set
==================================================

ORDER BY $query_sort LIMIT $query_start,$query_end

end of the query execute
=====================================================

if($total_pages>1){
		for($i=1; $i<$total_pages+1; $i++){
		$url="companies_did.php?p=$i";
		 if(isset($_GET['sort_by'])) {
			$url.="&sortby={$_GET['sort_by']}";
				 }
			echo "<a class='pages' href='$url'>$i</a> ";
				}
}

Open in new window

0
Comment
Question by:Refael
  • 4
  • 4
  • 2
10 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35072968
This bit sets the limit

LIMIT $query_start,$query_end

and the query end is set to be 20 records more than its start

$query_end=$query_start+20;

What someone has in mind is that if the query returns 100 records and he wants to show only 20 records starting from number 40 (i.e. numbers 40 to 59). This way unwanted records can be ignored because the query only returns a maximum of 20.

However.... someone has their syntax wrong. The Limit clause defines a starting record followed the NUMBER of records to return. So it should read

LIMIT $query_start,20

"With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):"

http://dev.mysql.com/doc/refman/5.0/en/select.html
0
 
LVL 4

Accepted Solution

by:
gizmola earned 2000 total points
ID: 35072995
The MySQL limit simply gives you a range of values out of the overall result set you would have for a query.

So for LIMIT 10, 5


The first param is the "offset" into the result set -- the "offset+1" row.  This is because the first row is considered row "0".

The 2nd param is how many rows to return -- in the example... 5 rows.

This simple table and query should make it really clear.

mysql> select * from a;     
+------+------+---------------------+
| id   | name | created             |
+------+------+---------------------+
|    1 | A1   | 2009-06-23 13:54:29 | 
|    2 | A2   | 2009-06-23 13:54:36 | 
|    3 | A3   | 2009-06-23 13:54:44 | 
|    4 | A4   | 2009-06-23 13:54:52 | 
|    5 | A5   | 2009-06-23 13:55:35 | 
+------+------+---------------------+
5 rows in set (0.01 sec)

mysql> select * from a LIMIT 2,2;
+------+------+---------------------+
| id   | name | created             |
+------+------+---------------------+
|    3 | A3   | 2009-06-23 13:54:44 | 
|    4 | A4   | 2009-06-23 13:54:52 | 
+------+------+---------------------+
2 rows in set (0.00 sec)

Open in new window


So in the code you have, you should notice that they have hardcoded a constant into the query -- the number of rows is "20".

$query_start=20*$page;
	 $query_end=$query_start+20;

Open in new window


The code is pretty simple -- taking the 'p' url param and multiplying it by 20 to get the offset, then adding 20 for the number of rows per page.

Thus if you wanted to change that, you should change the constant "20" to some other number.


0
 

Author Comment

by:Refael
ID: 35073083

Hi bportlock

Thank you. I am trying to understand what you mean exactly.

All the records are showing up. It does not start from record number 40.
all i am trying to understand is for example: how can i set it to display 50 records or 100 records... and if i have less records than the limit of record per page .. how can i step the empty pages from showing up... e.g page2 page3.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Refael
ID: 35073093

Correction to the above last sentence:
"how can i STOP the empty pages from showing up... e.g page2 page3."
0
 
LVL 4

Expert Comment

by:gizmola
ID: 35073298
First off bportlock's answer was incorrect when he indicated that there was an issue in the code.  2nd, I gave you the answer to your question.  Did you read my reply?
0
 
LVL 4

Expert Comment

by:gizmola
ID: 35073315
Actually I retract my statement -- he's right that there's no reason for them to be adding the offset to the 20!  
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35073467

All the records are showing up. It does not start from record number 40.

The '40' was just an example number to illustrate the point in selecting from a set of records. Also remember that the LIMIT clause in the code above is wrong. It has $query_start,$query_end and $query_end is always 20 bigger than the query start. So if you have 100 records then the LIMIT (for page 1) will be LIMIT 0,20 which is OK, but for page 2 it will be LIMIT 20,40 which will return more records (40 of them) than you expect. On page 3 it will be LIMIT 40,60 which returns 60 records, and so on. The second parameter in your example should ALWAYS be 20 so you have

LIMIT 0,20
LIMIT 20,20
LIMIT 40,20

and so on....


all i am trying to understand is for example: how can i set it to display 50 records or 100 records...

Just change the second parameter in the LIMIT clause. That is what controls the number of records selected. So in the piece of code you have

LIMIT $query_start,20

will return a maximum of 20 records whereas

LIMIT $query_start,50

will return a maximum of 50 records.


and if i have less records than the limit of record per page .. how can i step the empty pages from showing up... e.g page2 page3.

It should not be a problem. Let us say that you have a limit of 20 records but the query only returns 15, then only 15 are returned to you. The secod parameter on the LIMIT clause defines a maximum number that can be returned.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35073498
Gizmola said: "....First off bportlock's answer was incorrect when he indicated that there was an issue in the code...Actually I retract my statement "

No worries.... happens to us all eventually.

Actually I would quite like it if LIMIT let me have a start/stop record syntax....

:-(

0
 
LVL 4

Expert Comment

by:gizmola
ID: 35073738
Yes, sadly I jumped on you, due to a lot of other incorrect or irrelevant answers that have been posted to questions, usually after my initial post.  It also bugs me when the OP seems to just completely ignore a reply.  ;D  Too much coffee, and I shouldn't be wasting my time here anyways lol.

I get frustrated with EE at times as a result, but I should have read your answer more carefully before jumping to the erroneous conclusion.  I sure wish there was a delete or even an edit button that was good for 2 minutes or something.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35073818
@gizmola - I no longer get worked up about these things because it happens all the time and on all forums. My blood pressure can't cope.

" I sure wish there was a delete or even an edit button that was good for 2 minutes or something.

Ohhh..... I *dream* of having an EE edit button....
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question