Pagination in single Query

What people normally do in the code?
Example:

//query to count total records
$result = mysqli_query($conn,"SELECT COUNT(*) total FROM test"); //getting count
if($result)
{
   $totalRows=mysqli_num_rows($result);
   if($totalRows==1)
   {
     $resultData=mysqli_fetch_assoc($result);
     $total=$resultData['total']; //total count in a variable
   }
   else{
     echo "No rows found";
   }
}
else{
echo "error in query :: ".mysqli_error($conn);
}
//query to fetch record
$result =mysqli_query($conn,"SELECT * FROM test LIMIT 0,10 "); //query to get 10 records            while($resultData=mysqli_fetch_assoc($result)){
             echo $result['name'];
         }

But on this you are running 2 queries. Now suppose if the query is very heavy with lots of conditions and joins then it will be a heavy operation for mysql.

Fix:

Using the SQL_CALC_FOUND_ROWS keyword in your SQL query will allow you to fetch the total amount of results without being limited by the LIMIT clause.

Here’s how you use it:

$result = mysqli_query($conn,"SELECT SQL_CALC_FOUND_ROWS * FROM test limit 0,10");
if($result)
{
    $totalRows=mysqli_num_rows($result)>0;
    if($totalRows>0)
    {
      $count_result = mysqli_query($conn,"SELECT FOUND_ROWS() cnt");
      $CountingResult=mysqli_fetch_assoc($count_result);
      echo $total=$CountingResult['cnt'];
      while($resultData=mysqli_fetch_assoc($result))
      {
          echo $resultData['name']."<br>";
      }
   }
   else{
     echo "No rows found";
   }
}
else{
     echo "error in query :: ".mysqli_error($conn);
}