Solved

Limit query per page

Posted on 2011-03-08
10
321 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 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
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.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

808 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