Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Limit query per page

Posted on 2011-03-08
10
Medium Priority
?
333 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 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