Pagination, OFFSET, LIMIT
In this post I want to talk about LIMIT and OFFSET and how to use them, to paginate your results when you are developing a website. Let's start with an example. During this post I will refer to this example. Imagine we have one table which is called ‘Person‘. ‘Person‘ attributes are, ‘pid, name, type‘ and our primary key is 'pid'
In this post I want to talk about LIMIT and OFFSET and how to use them, to paginate your results when you are developing a website.
Let's start with an example. During this post I will refer to this example.
Imagine we have one table which is called ‘Person‘. ‘Person‘ attributes are, ‘pid, name, type‘ and our primary key is 'pid'
What is LIMIT?
If we want to fetch records from this table without any conditions, our query would be
“SELECT * FROM Person“
This will return all records in our table without any conditions, so if we have 1000 records, all of them will be fetched from the database. If you want only the first 10 users you have to limit your query using LIMIT. So your query changes to
“SELECT * From Person LIMIT 10″
Unlike the previous query, this time we can only see the first 10 records of our database. So we have a shorter page size and a cleaner webpage. When we use ‘LIMIT’ in our query, we are basically telling our database that we want restricted number of records
Some of you may ask, what if we want to see the third 10 records of our database?
In this case you have to add something to you query to tell it to bring the third 10 records. Our query only fetches records 1 to 10, but now we want records 20 to 30. We have to use OFFSET.
What is OFFSET?
We want records 20 to 30. You know how to LIMIT your query to fetch only 10 records. But what does offset mean? Offset is the position of a point or a particle in reference to an origin or to a previous position. By adding OFFSET to the end of your query you can handle these types of queries. Since we wanted records 20 to 30, we have to write the following SQL query:
“SELECT * From Person LIMIT 10 OFFSET 20″
Now, only records 20 to 30 will be fetched from the database.
What is Pagination?
Let's walk in the viewers shoes. If you are viewing a webpage with 1000 records fetched in only one page, will you continue reading the article? Well, in 98% of the times, viewers cannot stand a page with lots of data. It is a bit tiring to follow what the post is about, if this happens. Let's talk about another situation. Imagine you want to see list of users , which will be fetched from 'Person' table, and the website shows you 1000 users in just one page, it takes time to find what you want. So, what should we do? The answer to this question is simply paginating your result.
When we are querying our database for a considerable amount of data, instead of fetching all of our data, we can simply limit the number of fetched records using ‘LIMIT’ in our SQL syntax. And as you have already guessed, when we go to the next page, we have to dynamically change the value of OFFSET so each page shows a specific range of records.
How can we change the value of OFFSET? When you want to create url for each page we can simply use $_GET method to see on which page we are right now. In your url you can add ?page=VALUE and when programming this value is set in $_GET['page']. If you are on the first page you want to see records 1 to 10, and when you are on the second page, you want to see records 10 to 20. So you have to decrement the number of you page and multiply it by the value you have set for LIMIT which in this case is 10 and store it in another variable like $offset.
<?php $offset = ($_GET['page'] -1) * 10; ?>
So, your query will look like this:
“SELECT * From Person LIMIT 10 OFFSET $offset″
WARNING: Be careful, when you want to give your SQL command to your database, you have to concatenate $offset to a string which is your SQL command.
WARNING: Be careful, always check $_GET['page'] to make sure the value is not out of rage, for example $_GET['page'] cannot be a string, and it cannot be zero or less.
You also have to see how many records you have in your table, regardless of LIMIT, and by dividing it by 10 (which is the value of your LIMIT) and getting the floor of the result and incrementing it, you can see how many pages you have.
You can get the amount of your results using count in an SQL syntax:
"SELECT count(id) From Person"
This will give the the amount of records you have in your table. And you can see how many pages you have in the following way:
<?php $pages = floor($countResults/10) + 1; ?>
By passing $pages to your view, you can print your pages (1,2,3,4...), using a loop, and linking each one to its page.
REMEMBER: You can add your desired conditions to all SQL Queries above.