Solved

Limit query per page

Posted on 2011-03-08
10
328 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 500 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

623 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