Php与SQL的分页无法正常工作

Question

I have done this pagination using PHP and SQL. Id 11 and 12 is missing in the page 2 module. I can't find out where my mistake is. Here my source code:

<?php
$per_page = 5;
$rec_count = $row['id'];
$total_pages = ceil($rec_count / $per_page);         

if (isset($_GET['page'])) {
    $show_page = $_GET['page']; //current page
    if ($show_page > 0 && $show_page <= $total_pages) {
        $start = ($show_page - 1) * $per_page;
        $end = $start + $per_page;
    } else {
        $start = 0;
        $end = $per_page;
    }
} else {
    $start = 0;
    $end = $per_page;
}

$page = intval($_GET['page']);
$tpages=$total_pages;
if ($page <= 0)
    $page = 1;

$sql = "SELECT COUNT(Id) AS id FROM [Register].[dbo].[RegisData] WHERE DeleteStatus = 1";
$result = sqlsrv_query($conn,$sql); 
$row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);
$rec_count = $row['id'];
$total_pages = ceil($rec_count / $per_page);

$sql1 = "SELECT [Id],[FirstName],[LastName],[ProfilePic],[Gender]
         ,[Email],[MobileNo],[Company],[Designation],[Country],[State],[Address],[City]
         ,[Pincode],[Hobbies],[DietaryHabits],[DeleteStatus] 
        FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY RegId) as row 
        FROM [Register].[dbo].[RegisData]) 
        a WHERE row between $start and $end 
        AND DeleteStatus = 1 
        ORDER BY RegId";

$result = sqlsrv_query($conn,$sql1);
$z = 1;
for ($x=1; $x<=$total_pages; $x++) {
    echo $x;
}  
?> 

Answer 1

the way you did the pagination combined with DeleteStatus = 1 will always create broken and incomplete pages (if you take all the rows between 1 and 5 and row number 4 is DeleteStatus = 0 you will only get 4 rows)

you should use SQL's LIMIT functionality that allows you to select the result row number to start and how many rows to show after the WHERE clause so try

$page = intval($_GET['page']);
if($page == 0) $page = 1;
$per_page = 5;
$offset = ($page -1) * $per_page;

$sql1 = "SELECT [Id],[FirstName],[LastName],[ProfilePic],[Gender]
     ,[Email],[MobileNo],[Company],[Designation],[Country],[State],[Address],[City]
     ,[Pincode],[Hobbies],[DietaryHabits],[DeleteStatus] 
    FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY RegId) as row 
    FROM [Register].[dbo].[RegisData]) 
    a WHERE DeleteStatus = 1 
    ORDER BY RegId
    LIMIT {$per_page} OFFSET {$offset}";

for more on LIMIT in SQL look at this short explanation: https://www.techonthenet.com/sql/select_limit.php it's really what you're looking for

---EDIT----

since you don't want to use my suggested solution (for unknown reasons) you can try to make this change to your code:

$sql1 = "SELECT [Id],[FirstName],[LastName],[ProfilePic],[Gender]
     ,[Email],[MobileNo],[Company],[Designation],[Country],[State],[Address],[City]
     ,[Pincode],[Hobbies],[DietaryHabits],[DeleteStatus] 
    FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY RegId) as row 
    FROM [Register].[dbo].[RegisData] WHERE DeleteStatus = 1) 
    a WHERE row between $start and $end 
    AND DeleteStatus = 1 
    ORDER BY RegId";

what I did is add the WHERE DeleteStatus = 1 condition to the internal query that numbers the lines, this way your filter should work as expected - although you should use LIMIT

To prevent duplicates (5 appearing both for page 1 and page 2) you should change this if:

 if ($show_page > 0 && $show_page <= $total_pages) {
    $start = ($show_page - 1) * $per_page;
    $end = $start + $per_page;
}

to this:

 if ($show_page > 1 && $show_page <= $total_pages) {
    $start = ($show_page - 1) * $per_page + 1;
    $end = $start + $per_page;
}

again, if you would have used LIMIT this would not be an issue