Search code examples
sqlsql-serverpdosyntax-errorlimit

SQL PDO Wrong syntax near "@P1"

I try to use LIMIT with PDO SQL.

My code is this:

$sql = "SELECT * FROM TblWerte LIMIT :limit1";
$result = $db->prepare($sql);
$result->bindParam(':limit1', $limit1);
$limit1 = 15;
$result->execute();

But when I write $result->execute(); I keep getting this error:

PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Falsche Syntax in der Nähe von "@P1". in C:...\werte.php:96

Stack trace:

0 C:\...\werte.php(96): PDOStatement->execute()

1 {main}

thrown in C:\...\werte.php on line 96

The line 96 is "$result->execute();"

I already tried different this like using bindValue() instead of bindParam(), I tried to directly parse the value "limit1" into an integer in bindParam() ...

The solution I had to use TOP instead of LIMIT. Also I had to remove the bindParams() part.

$sql = "SELECT TOP 15 * FROM TblWerte ORDER BY ID DESC";
$result = $db->prepare($sql);
$result->execute();
Solution

  • The LIMIT clause is not part of standard SQL. It's supported as a vendor extension to SQL by MySQL, PostgreSQL, and SQLite. SQL Server has something similar: TOP 

    $sql = "SELECT top :limit1 * FROM TblWerte";
$result = $db->prepare($sql);
$result->bindParam(':limit1 ', $limit1);
$limit1 = 15;
$result->execute();