Problem show result of pagin with php and mssql ROW_NUMBER

i have a search form and after submit the form i have fetch the result and show the content from MSSQL database, and for that’s i have problem with show number of row in page as it’s show me every time all content and i need to show just 10 per page, can someone help me.

this is the query i have after fetch the search form :

“SELECT *, ROW_NUMBER() OVER (ORDER BY ROOMNO) AS row FROM dbo.Rooms WHERE ROOMNO NOT IN (SELECT DISTINCT S_ROOMNO FROM dbo.Stay WHERE ((S_ADATE BETWEEN ‘$CheckIn’ AND ‘$CheckOut’) AND (S_DDATE BETWEEN ‘$CheckIn’ AND ‘$CheckOut’)) OR (S_ADATE < ‘$CheckIn’ AND S_DDATE > ‘$CheckOut’) OR (S_ADATE < ‘$CheckIn’ AND ( S_DDATE BETWEEN ‘$CheckIn’ AND ‘$CheckOut’)) OR (( S_ADATE BETWEEN ‘$CheckIn’ AND ‘$CheckOut’ ) AND S_DDATE > ‘$CheckOut’))”;

and after i have to add this to query (this add the error ) :

$offset = ($currentpage - 1) * $rowsperpage ;
$limit=($offset) + $rowsperpage;
//$query .= “ORDER BY ROOMNO DESC LIMIT $offset, $rowsperpage”;
$query .= " AND row BETWEEN $offset AND $limit";