I am developing an android app where i want to fetch 5 records each time using this query.
SELECT *from contest_table WHERE created_by='$me' LIMIT 5;
Now i get 5 records , but i want to fetch more 5 (next) records from data base when i click on "FETCH-MORE-RECORD" button inside the app
You can use OFFSET :
SELECT * from contest_table WHERE created_by='$me' LIMIT 5,5;
This will return 5 more rows, from position the 6th row (6-10).
The first number is to declare the start position of the fetch, and the second one is two declare how many to fetch.
This could also be written like this:
SELECT * from contest_table WHERE created_by='$me' LIMIT 5 OFFSET 5;
You will have to give a limit start and range. It will look as follows:
SELECT *from contest_table WHERE created_by='$me' LIMIT 5, 5;
To get first 5 records
SELECT * from contest_table WHERE created_by='$me' LIMIT 0,5;
To get next 5 records
SELECT * from contest_table WHERE created_by='$me' LIMIT 6,5;
Like wise you can change the starting point increasing it by 5.
So here is the solution. You need to keep the track of the off. So delcare a static variable for store the off. also update the offset after every click so next time you get right result.
public static int offset=5;
Then use this query with your listener;
SELECT * from contest_table WHERE created_by='$me' LIMIT 5,5;
It's a simple logic, Which I am representing as PHP codes, You request for first page from android by including page 1. in URL
http://SITENAME.COM/index.php?task=articles&page=1
You grab the result coming from URL like here
$page = $_GET['page'];
$max = 5; //As you want to retrieve 5 results only
And here define $start from where to start retrieving values
$end = $page * $max;
$start = $end+1 - $max;
Your query will be like
SELECT * FROM table order by column desc limit $start, $max //start will be 1 and max value to retrieve will be 5
After that you request page 2 and URL will be
http://SITENAME.COM/index.php?task=articles&page=2
and again in the query $start will be 6 and max value to retrieve will be 5 that is $max
Hope this helps, This is what the logic behind pagination.