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); }