Solved

Limit query per page

Posted on 2011-03-08
10
326 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 Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
WordPress  Failed to Import Media 8 65
SSL unsecure page mystery 17 46
Undefined variable with $_POST in PHP 5 39
Load string Array from file 23 42
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 article discusses how to create an extensible mechanism for linked drop downs.
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.

739 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