doufeng9567 2015-04-21 14:21
浏览 217
已采纳

LIMIT和OFFSET过滤数据库结果的问题(语法错误)

I am trying to create a page where a user can search for a book, they type in the title and author - this is optional and also enter where the list returned should start from and the length of the list. Then a list of books shoud be returned with it's details.

When I try to run the code with print_r($stmt->errorInfo());, i get the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' authors = '' LIMIT '2' OFFSET '0'' at line 1 )

Here is the main code:

$title = $_GET["title"];
$authors = $_GET["authors"];
$start = (int)$_GET["start"];
$length = (int)$_GET["length"];

$sql = "SELECT title, authors, description, price
FROM book2
WHERE title LIKE '$title%' 
AND author LIKE '%$authors%'
OFFSET 0,$start
LIMIT 0,$length";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':title', $title);
$stmt->bindParam(':authors', $authors);
$stmt->bindParam(':length', $_GET['length'], PDO::PARAM_INT);
$stmt->bindParam(':start', $_GET['start'], PDO::PARAM_INT);

     $stmt->execute(array(

    ':title' => $title,
    ':authors' => $authors,
    ':start' => $start,
     ':length' => $length   
      ));

print_r($stmt->errorInfo());

echo "<table>";
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
$title = $row['title'];
$authors = $row['authors'];
$description = $row['description'];
$price = $row['price'];

echo "<tr>";
        echo "<td>Title</td>";
        echo "<td>$title</td>";
echo "</tr>";
echo "<tr>";
        echo "<td>Authors</td>";
        echo "<td>$authors</td>";
echo "</tr>";
echo "<tr>";
        echo "<td>Description</td>";
        echo "<td>$description</td>";
echo "</tr>";
echo "<tr>";
        echo "<td>Price</td>";
        echo "<td>$price</td>";
echo "</tr>";
}
echo "</table>";

I'm not sure what the error is how to I get this code to work? I hope the method is correct to do so, as I can't test it.

  • 写回答

2条回答 默认 最新

  • dorisdong0514 2015-04-21 14:32
    关注

    You should use LIMIT only:

    $sql = "SELECT title, authors, description, price
    FROM book2
    WHERE title LIKE '$title%' 
    AND author LIKE '%$authors%'
    LIMIT $start,$length";
    

    The syntax with OFFSET is only for compatibility reason:

    For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

    Valid examples of LIMIT are following:

    LIMIT offset, row_count
    LIMIT row_count
    LIMIT row_count OFFSET offset
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常