MySQLi查询中的LIMIT问题

Question

I have three PHP APIs which have the below queries:

API 1:

$sql = "SELECT number, status, user_id from number_list WHERE disable=0 
   order by id LIMIT 0,700";

API 2:

$sql = "SELECT number, status, user_id from number_list WHERE disable=0 
   order by id LIMIT 701,1400";

API 3:

$sql = "SELECT number, status, user_id from number_list WHERE disable=0 
   order by id LIMIT 1401,2100";

I want take 700 records in each API. For example,

0 to 700 in API 1,

701 to 1400 in API 2 and

1401 to 2100 in API 3 (like shown above).

API 1 is working fine but API 2 and 3 are missing a result.

Why is this occurring and how do I correct it?

Answer 1

API 1

$sql = "SELECT number, status, user_id from number_list WHERE disable=0 
   order by id LIMIT 0,700";

API 2

$sql = "SELECT number, status, user_id from number_list WHERE disable=0 
   order by id LIMIT 700,700";

API 3

$sql = "SELECT number, status, user_id from number_list WHERE disable=0 
   order by id LIMIT 1400,700";

Explanation

Mysql also provides a way to handle this: by using OFFSET.

The SQL query below says "return only 10 records, start on record 16 (OFFSET 15)":

$sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15";

You could also use a shorter syntax to achieve the same result:

$sql = "SELECT * FROM Orders LIMIT 15, 10";

Demo

http://sqlfiddle.com/#!9/10ff28/7