How to find the next set of records after a specified one in MYSQL

Greetings Of The Day!

While we were working with web services using PHP and Mysql  for an android application, we had a use case to retrieve and show a set of data to one user and remaining set of data to other user based on the login details.

Application had Master and Partner users. Consider we have around 25 records in 'abc' mysql table. Now I have to fetch and show first 12 data to Master and the remaining 13 data to the partner.  

For the above use case we had to write the web-services. I am not going to discuss about the web services here But the mysql query we used to get such data was very important here that we are going to discuss here.

First we fetched the total number of rows or the records for the query:

$result_row = mysql_query("SELECT * FROM abc where uid='$uid' ");

$totalrows=intval(mysql_num_rows($result_row)/2);

Here $uid is Master's User ID. As we have two users, I divide the total number of rows by 2. And convert it to integer to get the whole number.  Now I will write the query to get only the first $totalrows as below

$result = mysql_query("SELECT * FROM abc where uid='$uid' 

LIMIT $totalrows") 

or die(mysql_error());

while ($row = mysql_fetch_array($result)) {

//Get the data

....

....

}

Here the main part doing the trick is "LIMIT". This limits the records to fetch. This gives you first half of the records.

Now similarly we write the query for Partner also to fetch the remaining records as below

  $result_row = mysql_query("SELECT * FROM abc where uid='$uid' ");
  $totalrows=mysql_num_rows($result_row);
  $startindex=intval($totalrows/2);

Here I have the fetch the records in between $startindex and $totalrows. That's it. here we go:

$result = mysql_query("SELECT * FROM abc where uid='$uid'

LIMIT $startindex,$totalrows") 

or die(mysql_error());

while ($row = mysql_fetch_array($result)) {

//Get the data

....

....

}

The above query fetches the remaining records from the table. That's it. Happy Coding !