Using MySQL LIMIT, OFFSET for paging?

First we need to create a table with some records and then we will use pagination with the help of limits and offsets.

Create a table with the help of CREATE command. The query is as follows -

mysql> CREATE table limitoffsetDemo
-> (
-> id int,
-> FisrtName varchar(200)
-> );
Query OK, 0 rows affected (0.45 sec)

After creating the table, we will insert records with the help of the INSERT command.

Insert record

mysql> INSERT into limitoffsetDemo values(1,'John');
Query OK, 1 row affected (0.11 sec)

mysql> INSERT into limitoffsetDemo values(2,'Bob');
Query OK, 1 row affected (0.16 sec)

mysql> INSERT into limitoffsetDemo values(3,'David');
Query OK, 1 row affected (0.13 sec)

mysql> INSERT into limitoffsetDemo values(4,'carol');
Query OK, 1 row affected (0.19 sec)

mysql> INSERT into limitoffsetDemo values(5,'Taylor');
Query OK, 1 row affected (0.10 sec)

mysql> INSERT into limitoffsetDemo values(1,'John');
Query OK, 1 row affected (0.24 sec)

mysql> INSERT into limitoffsetDemo values(7,'Smith');
Query OK, 1 row affected (0.13 sec)

mysql> INSERT into limitoffsetDemo values(8,'John');
Query OK, 1 row affected (0.12 sec)

mysql> INSERT into limitoffsetDemo values(9,'Johnson');
Query OK, 1 row affected (0.11 sec)

mysql> INSERT into limitoffsetDemo values(10,'Jason');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT into limitoffsetDemo values(11,'Bk');
Query OK, 1 row affected (0.17 sec)

After inserting the record, we can check how many records there are in my table with the help of the following command Select the command. The query is as follows -

mysql> SELECT * from limitoffsetDemo;

After executing the above query, we will get the following output

+------+-----------+
| id   | FisrtName |
+------+-----------+
| 1    | John      |
| 2    | Bob       |
| 3    | David     |
| 4    | carol     |
| 5    | Taylor    |
| 1    | John      |
| 7    | Smith     |
| 8    | John      |
| 9    | Johnson   |
| 10   | Jason     |
| 11   | Bk        |
+------+-----------+
11 rows in set (0.00 sec)

Now, I have 11 records. To set pagination with the help of limits and offsets, apply the following command Syntax -

SELECT * from yourTableName limit value1,value2;

In the above query, value1 tells us where we need to display the records, and value2 tells us the number of records we need to display. Record.

Now, I apply the above query to get the results as follows -

mysql> SELECT * from limitoffsetDemo limit 7,4;

The following is the output

+------+-----------+
| id   | FirstName |
+------+-----------+
| 8    | John      |
| 9    | Johnson   |
| 10   | Jason     |
| 11   | Bk        |
+------+-----------+
4 rows in set (0.00 sec)

The above is the detailed content of Using MySQL LIMIT, OFFSET for paging?. For more information, please follow other related articles on the PHP Chinese website!