Friday, January 26, 2018

PHP Limit Data Selections From MySQL

In this tutorial we will explain how Limit Data Selections From a MySQL Database. This technique helps to fetch large number data one by one without any performance issue and by this we can limit the number of records to be returned from MySQL database.



MySQL LIMIT Clause :

MySQL provides a LIMIT clause that is used to specify the number of records to return.The LIMIT clause makes it easy to code multi page results or pagination with SQL, and is very useful on large tables.
While Returning a large number of records can impact on performance, So to overcome the issue we are using LIMIT Clause in MySQL statement.

Advantage of LIMIT Clause :

  • Improve the performance.
  • Easy to use and provide the multi page results.

Lets start with simple examples :

Create table "POST" :
CREATE TABLE `post` (
  `POSTID` int(3) NOT NULL,
  `POSTTITLE` varchar(100) NOT NULL,
  `POSTDETAILS` varchar(10000) NOT NULL,
  `POSTLINK` varchar(100) NOT NULL
);

Scenario - 1 :
Let's say we have database table name as "POST" and it consists of 20 records. Here we wish to select 5 record from the MySQL database. The SQL query would then look like this:

SELECT * FROM `POST` LIMIT 5

Output :

Scenario - 2 :
Let's say we have database table name as "POST" and it consists of 20 records. Here we wish to fetch/ select records 6 to 10 ( 6 - 10 ) from database. In that case we need to use OFFSET. 
MySQL also provides a way to handle this by using OFFSET.

The SQL query below  "return only 5 records, start on record 6 (OFFSET 5)"
SELECT * FROM `POST` LIMIT 5 OFFSET 5

OR

you can also use below query :
Syntax : LIMIT Offset value , number of record to be returned.

SELECT * FROM `POST` LIMIT 5,5

Output:


This all about Limit Data Selections From a MySQL Database. Hope you like this example.



No comments:

Post a Comment