mysql分页rownum_如何在分页上连续获取Mysql Rownum

Basically, i want to display the number of rows from table along with the data by using @rownum it works just fine for first page but when we go to the next page we it start with row one again.

query code:

$sql = "SELECT @rownum:=@rownum+1 as rownum, t.*FROM (SELECT @rownum:=0) r, (select * from tbl) t

LIMIT $Page_Start , $Per_Page";

pagenation code:

$objConnect = mysql_connect("localhost","user","pass") or die("Error Connect to Database");

`enter code here`$objDB = mysql_select_db("WEB");

$strSQL = "SELECT * FROM line ";

$objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]");

$Num_Rows = mysql_num_rows($objQuery);

$Per_Page = 2; // Per Page

$Page = mysql_real_escape_string($_GET["Page"]);

if(!$_GET["Page"])

{

$Page=1;

}

$Prev_Page = $Page-1;

$Next_Page = $Page+1;

$Page_Start = (($Per_Page*$Page)-$Per_Page);

if($Num_Rows<=$Per_Page)

{

$Num_Pages =1;

}

else if(($Num_Rows % $Per_Page)==0)

{

$Num_Pages =($Num_Rows/$Per_Page) ;

}

else

{

$Num_Pages =($Num_Rows/$Per_Page)+1;

$Num_Pages = (int)$Num_Pages;

}

pagenation usage:

if($Prev_Page)

{

echo " Back &nbsp   ";

}

if($Page!=$Num_Pages)

{

echo " Next ";

}

So i want the number of row continuously increase page after pages

example page 1 row 1-5 and page2 should be 6-10 something like that

Thanks a lot

解决方案

You need to wrap the calculation to get row number inside a subquery and limit it on the outer SELECT statement so the row_number won't break, ex,

the column name and table name might be different form your example above but the thought of the query is the same.

SELECT RowNumber, Student_ID, Student_Name

FROM

(

SELECT @rownum := @rownum + 1 RowNumber,

t.*

FROM student t, (SELECT @rownum := 0) s

ORDER BY t.Student_ID

) subQ

// LIMIT 0, 3