Search code examples
mysqlselectlimitrows

Check if SELECT statement with LIMIT returned all rows

Is there a way to check if a SELECT statement with a LIMIT returned all possible rows for example i have LIMIT 50 but i have 64 rows it should add false and if i have 28 rows it should add true. I think i can achieve this with php by doing a something like this but is it possible with SQL only?

$sql= "(SELECT * FROM tbl_group_message WHERE gid = ".$gid." ORDER BY mid DESC LIMIT ".$more.") ORDER BY mid ASC";
$messages = mysqli_query($dbc,$sql);
$row_cnt1 = mysqli_num_rows($messages);


$sql = "(SELECT COUNT(*) FROM  tbl_group_message WHERE gid = ".$gid." ORDER BY mid DESC) ORDER BY mid ASC";
$messages = mysqli_query($dbc,$sql);
$row_cnt2 = mysqli_num_rows($messages);

if(row_cnt1 < $row_cnt2){
   $allinlimit = true;
}
else{
   $allinlimit = false;
}
Solution

  • You can use SQL_CALC_FOUND_ROWS and FOUND_ROWS:

    $sql= "SELECT SQL_CALC_FOUND_ROWS * FROM tbl_group_message WHERE gid = ".$gid." ORDER BY mid DESC LIMIT ".$more." ORDER BY mid ASC";

    Then run the query SELECT FOUND_ROWS() as the next query to the database.

    More information is in the documentation.

    EDIT:

    As the OP points out, COUNT(*) seems to be faster (in this case).