Question 414933 in PHP Question posted in
Official manual can be found here.

Php – Why I'm getting error when using LIMIT with PDO

here my code :

$Limit=5;
$sql = 'SELECT * FROM [dbo].[table_name] ORDER BY [count] DESC LIMIT :limit';
$stmt = $db->prepare($sql);
$stmt->bindValue(':limit', (int) $Limit, PDO::PARAM_INT);
$results = $stmt->execute();

and here the error im getting :

Fatal error: Uncaught PDOException: SQLSTATE[HY090]: Invalid string or buffer length: [FreeTDS][SQL Server]Invalid string or buffer length

Any idea what im doing wrong?

Tags:

2

Answers


  1. 0

    try this query using top

    $Limit = 5;
$sql = 'SELECT TOP :limit * FROM [dbo].[table_name] ORDER BY [count] DESC';
$stmt = $db->prepare($sql);
$stmt->bindValue(':limit', (int) $Limit, PDO::PARAM_INT);
$results = $stmt->execute();
    Login or Signup to reply.
  2. 0

    The error you are encountering is likely due to the fact that LIMIT is not supported in Microsoft SQL Server, which is indicated by the error message you received.

    To achieve similar functionality in SQL Server, you can make use of the OFFSET and FETCH clauses. Here is how you can modify your SQL query to achieve pagination in SQL Server:

    $Limit = 5;
$Offset = 0; // You can adjust this value for pagination

$sql = 'SELECT * FROM [dbo].[table_name] ORDER BY [count] DESC 
        OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY';

$stmt = $db->prepare($sql);
$stmt->bindValue(':offset', $Offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $Limit, PDO::PARAM_INT);
$results = $stmt->execute();

    By using OFFSET and FETCH NEXT, you can achieve pagination in SQL Server without using LIMIT. Adjust the $Offset value as needed to fetch different pages of data.

    Make sure that your PHP PDO connection is set up correctly to work with Microsoft SQL Server, and that your database table names and column names are correct in your query.

    Also, please check your SQL Server driver and version to ensure that it supports the OFFSET and FETCH NEXT clauses.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search