doudou5101
2013-02-03 13:21
浏览 166
已采纳

如何使用PHP PDO从MYSQL查询中获取最后的结果?

I have a simple articles table with IDs. I want to get the highest and lowest ids from the latest 10 results. For example, if there are 11 ids, the result should be 2 and 11 and if there are 4 ids, should be 4 and 1 and so on. I use PHP PDO statements.

$aid = $DBH->prepare("SELECT id FROM articles ORDER BY id DESC LIMIT 10"); 
$aid->execute(); 
$row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST);
$lowest_article_id = $row[0];
$row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_FIRST);
$highest_article_id = $row[0];

The above will always return 11 if there are 11 records and 10 if there are 10 records. So, the query ignores the PDO::FETCH_ORI_LAST part of the query.

Thanks

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • duangu9173 2013-02-03 16:30
    已采纳

    I did some checking and from the docs it sounds like this should work. You need to enable cursor scrolling when you prepare the sql.

    $aid = $DBH->prepare("SELECT id FROM articles ORDER BY id DESC LIMIT 10", array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); 
    $aid->execute(); 
    $row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST);
    $lowest_article_id = $row[0];
    $row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_FIRST);
    $highest_article_id = $row[0];
    

    That being said it does not for me :/

    I did more reading/testing and I found these two bug reports:
    https://bugs.php.net/bug.php?id=34625 (this one is from 2005 though and I am almost positive there is cursor functionality in MySQL these days).
    https://bugs.php.net/bug.php?id=57623

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • douxing5199 2013-02-03 13:24

    You can use the MIN and MAX functions in SQL to achieve this.

    SELECT MIN(ID) min_ID, MAX(ID) max_ID
    FROM
    (
        SELECT id FROM articles ORDER BY id DESC LIMIT 10
    )
    
    评论
    解决 无用
    打赏 举报
  • duangouyan3328 2013-02-03 14:11

    Or use the union command in sql

    SELECT min(id) id FROM articles UNION SELECT max(id) id FROM articles;
    

    This statement returns the two ids you need.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题