SQL查询语法错误排查：获取指定行数范围时出错

Hey there, let's work through this syntax error you're facing. The error message makes it clear: your LIMIT clause is using quoted string values ('5' , '5') instead of plain integers, which MariaDB refuses to accept for that part of the query.

Why this keeps happening #

Even if you converted $from and $per to integers, if you're still wrapping them in single quotes when building your SQL string (like concatenating '$from'), MariaDB will treat those values as strings instead of the numeric offsets it expects. That's exactly what's triggering the 1064 syntax violation.

The Correct Fix: Use PDO Parameter Binding #

The best way to fix this (and also protect your code from SQL injection) is to use PDO's prepared statements with parameter binding. This ensures your integers are passed as the correct data type without any quoting mishaps.

Here are two working examples:

Option 1: Named Parameters with Explicit Type Binding

// Your offset and limit values
$from = 0;
$per = 5;

// Prepare the query with named placeholders
$sql = "SELECT * FROM your_table LIMIT :offset, :limit";
$stmt = $pdo->prepare($sql);

// Bind parameters as integers (critical for LIMIT to work correctly)
$stmt->bindParam(':offset', $from, PDO::PARAM_INT);
$stmt->bindParam(':limit', $per, PDO::PARAM_INT);

// Execute and fetch results
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Option 2: Positional Parameters (Shorter Syntax)

If you prefer a more concise approach, positional placeholders work too:

$from = 0;
$per = 5;

$sql = "SELECT * FROM your_table LIMIT ?, ?";
$stmt = $pdo->prepare($sql);

// Pass values directly to execute - PDO will infer integer types here
$stmt->execute([$from, $per]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

What to Stop Doing #

Avoid building your query by concatenating variables directly into the string, even if you cast them to integers. For example, this will still fail:

// ❌ Bad: Quotes turn the integer back into a string in the final SQL
$sql = "SELECT * FROM your_table LIMIT '$from', '$per'";

Casting $from to an integer doesn't fix the issue if you wrap it in quotes afterward.

Quick Recap #

• MariaDB's LIMIT requires numeric (integer) values, not quoted strings.
• Use PDO prepared statements with parameter binding to ensure correct data types.
• This approach also keeps your code safe from SQL injection attacks.

内容的提问来源于stack exchange，提问作者jack