Select query for pagination using TOP

Question

I already want to create paging on my page. I use SQL Server and PDO.

Below is my query code.

if (isset($_GET['page_no']) && $_GET['page_no']!="") {
                  $page_no = $_GET['page_no'];
                  } else {
                    $page_no = 1;
                    }

                $total_records_per_page = 3;
                $offset = ($page_no-1) * $total_records_per_page;
                $previous_page = $page_no - 1;
                $next_page = $page_no + 1;
                $adjacents = "2"; 

                $sql_count = $conn->prepare("SELECT COUNT(*) As total_records FROM booking");
                $sql_count->execute();
                $total_records = $sql_count->fetch();
                $total_no_of_pages = ceil($total_records['total_records'] / $total_records_per_page);
                $second_last = $total_no_of_pages - 1; // total page minus 1

$query = $conn->prepare("SELECT TOP $offset, $total_records_per_page * FROM booking LEFT JOIN room ON booking.Room_ID = room.Room_ID WHERE Admin_email = 'hazim_m@topglove.com.my' 
                ORDER BY booking.Book_No DESC");

and what I got from the error is

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ','. in

Can I know how to solve this problem?

Check the [reference](https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-2017) top only takes a single argument. I recommend googling [pagination in sql server](https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) — Dale K, Aug 14 '19 at 03:08

score 1 · Answer 1 · answered Aug 14 '19 at 04:11

As per the SQL query you have written , is missing before *, since column names are separated by , in your select query. Please try this.

        $query = $conn->prepare("SELECT TOP 1 $offset, $total_records_per_page, * FROM booking LEFT JOIN room ON booking.Room_ID = room.Room_ID WHERE Admin_email = 'hazim_m@topglove.com.my' 
        ORDER BY booking.Book_No DESC");

score 0 · Answer 2 · answered Aug 14 '19 at 03:48

try this (correct me if it's wrong):

select top $total_records_per_page * from (
          select *, ROW_NUMBER() over (order by booking.Book_No DESC) as r_n_n 
          from booking ..... where ....
    ) someVariable where r_n_n >=$offset

reference :Equivalent of LIMIT and OFFSET for SQL Server?

Select query for pagination using TOP

2 Answers2