LIMIT command SQL Server

Question

This is my PHP code:

require_once ('ConfigSQL.php');

$per_page = 10;
if(isset($_GET['page'])) {
    $page = ($_GET['page'] - 1);
}
else {
    $page = 0;
}
    $QueryCharacter = mssql_query ("
        SELECT Character.AccountID, Character.Name, Character.CtlCode, 
        AccountCharacter.Number, AccountCharacter.ID, 
        memb___id, memb_name, memb__pwd2, mail_addr
        FROM Character, AccountCharacter, MEMB_INFO
        WHERE Character.AccountID=AccountCharacter.ID AND 
        AccountID=memb___id AND AccountCharacter.ID=memb___id 
        LIMIT {$page},{$per_page}
        ");

$rows = mssql_fetch_row($QueryCharacter);
$pages = $rows / $per_page;
$pages = $pages ? ((int)$pages == $pages) : ((int)$pages + 1);

And that is the error I get:

Warning: mssql_query() [function.mssql-query]: message: Line 2: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\Pages\EditCharacter.php on line 19

Warning: mssql_query() [function.mssql-query]: Query failed in C:\xampp\htdocs\Pages\EditCharacter.php on line 19

Warning: mssql_fetch_row(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\Pages\EditCharacter.php on line 21

I know that the problem is the 'LIMIT' in my SQL query.

How should the query be?

Answer 1

Try changing your query into this:

WITH paging AS (
    SELECT
        ,Character.AccountID
        ,Character.Name
        ,Character.CtlCode
        ,AccountCharacter.Number
        ,AccountCharacter.ID
        ,memb___id
        ,memb_name
        ,memb__pwd2
        ,mail_addr
        ,ROW_NUMBER() OVER (ORDER BY Character.AccountID) AS RowNr
    FROM
        Character, 
        AccountCharacter, 
        MEMB_INFO
    WHERE
        Character.AccountID = AccountCharacter.ID
        AND AccountID=memb___id 
        AND AccountCharacter.ID=memb___id 
)
SELECT TOP ({$per_page}) *
FROM paging
WHERE RowNr > {$page} * {$per_page}
ORDER BY RowNr

Note that page 0 is the first page, 1 is the second, etc.

This uses Common Table Expressions introduced in MSSQL 2005, for earlier versions, something like this should probably work (source Efficient Paging (Limit) Query in SQLServer 2000?):

DECLARE @Sort int

SET ROWCOUNT {$page} * {$per_page}
SELECT @Sort = AccountID FROM Character ORDER BY AccountID

SET ROWCOUNT {$per_page}    
SELECT
    ,Character.AccountID
    ,Character.Name
    ,Character.CtlCode
    ,AccountCharacter.Number
    ,AccountCharacter.ID
    ,memb___id
    ,memb_name
    ,memb__pwd2
    ,mail_addr
    ,ROW_NUMBER() OVER (ORDER BY Character.AccountID) AS RowNr
FROM
    Character, 
    AccountCharacter, 
    MEMB_INFO
WHERE
    Character.AccountID > @Sort
    AND Character.AccountID = AccountCharacter.ID
    AND AccountID=memb___id 
    AND AccountCharacter.ID=memb___id
ORDER BY
    Character.AccountID