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?
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
The second parameter in the limit talks about the size of the rows you need so in your case since you need a chunk of 700, the second part after comma will always be 700.
So your queries will be like
LIMIT 0,700"; //for api 1
LIMIT 700,700"; //for api 2
LIMIT 1400,700"; //for api 3
The LIMIT keyword of is used to limit the number of rows returned from
a result set. The LIMIT number can be any number from zero (0) going
upwards. When zero (0) is specified as the limit, no rows are returned
from the result set. The OFF SET value allows us to specify which row
to start from retrieving data It can be used in conjunction with the
SELECT, UPDATE OR DELETE commands LIMIT keyword syntax
SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT N;
In your case it will be :
$sql = "SELECT number, status, user_id from number_list WHERE disable=0 order by id LIMIT 700,700";
$sql = "SELECT number, status, user_id from number_list WHERE disable=0 order by id LIMIT 1400,700";