Pagination in PHP, MYSQL serverside API

Pagination is a useful concept when there is a requirement of retreiving large chunks of data from server. Pagination reduces the load on server and also is user friendly as the end user sees less data in a go.

So here I am using PHP to retreive rows from MYSQL using pagination.

#retreive start and limit values
if(isset($_POST[“start”])) {
$start = $_POST[“start”]-1;
} else {
$start = 0;
}

##number of rows to be retreived from starting point, can be 10,20,30…
if(isset($_POST[“limit”])) {
$limit = $_POST[“limit”] ;
} else {
$limit = 10 ; //get 10 as default from starting point
}

#set header
header(‘Content-Type: application/json’);

#connection to database
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

#form query
$sql = “Select * from mytable LIMIT $start, $limit “; ##this query retreives rows from mytable starting from start point and upto $limit rows

#execute query
$retval = mysqli_query( $conn, $sql );

#fetch rows if query executed succesfully else return error
if(! $retval ) {
$data = array(“status”=>”failure”,”message”=>”Log fetch error!!”);
echo json_encode( $data );
mysqli_close($conn);
die(‘Could not fetch logs: ‘ . mysqli_error());
}
else {
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC)) {
$data[] = $row;
}

##send response to client
$data = array(‘status’=>’success’,’message’=>’Log fetched.’,’records’=>$data);

#close connection
mysqli_close($conn);
echo json_encode( $data );
}

This script will fetch rows from database based on start and limit variables. If these variables are not passed during client request it sets to default of 0 to 10 rows.

Happy Coding.