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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

#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.