0

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;
}
Maantje
  • 1,781
  • 2
  • 20
  • 33

1 Answers1

2

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).

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank You for answering my question but after some research it seems count(*) is faster then SQL_CALC_FOUND_ROWS https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/ and http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count . Is there a hidden benefit that i am missing here? – Maantje May 30 '15 at 14:03
  • @marsje . . . (1) That doesn't surprise me. (2) Thank you very much for including the comment. That is useful information for anyone stumbling on this question. – Gordon Linoff May 30 '15 at 14:21