get records between from a start to end number

Question

This SQL return all records as (JSON) from 2 tables "posts_main" and "posts_comments" that depends on specific User

$sql = "select posts_main.*,
(select groupid from posts_comments where groupid = posts_main.id group by groupid ) as count_comments
from posts_main
WHERE posts_main.user_id = '$user_id' ";


$obj = json_decode($_GET["x"], false);
$stmt = $con->prepare($sql);
$stmt->bind_param("ss", $obj->table, $obj->limit);
$stmt->execute();
$result = $stmt->get_result();
$outp = $result->fetch_all(MYSQLI_ASSOC);

echo json_encode($outp);

My Question: how can I get the first 10 records, then the next 10 records, to the end?

For example: I'll send "start" = 0, I'll get the first 10 records. Next time I'll send: "start" = 10, then I'll get the records FROM 10 TO 20. and so on.

Thank you...

Answer 1

If you have a "start" number, you can frame your query like this -

$sql = "select posts_main.*,
(select groupid from posts_comments where groupid = posts_main.id group by groupid ) as count_comments
from posts_main
WHERE posts_main.user_id = '$user_id' limit 10,10";

MySQL reads 10 records starting at the 11th record in the table. If you are looking for a Prev, Next solution then PHP pagination is the only option.

Answer 2

Hope this helps you:

$start = 0;
$sql = "select posts_main.*,
(select groupid from posts_comments where groupid = posts_main.id group by groupid ) as count_comments
from posts_main
WHERE posts_main.user_id = '$user_id' 
limit '$start' 10";