How To Get The Last Result From A Mysql Query Using Php Pdo?

Hey

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];

PHP:

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 and PDO::FETCH_ORI_FIRST part of the query.
Thanks

 

You're fetching more rows than you need to. You only need two, so why fetch 10? You can achieve this by just with the LIMIT operator. It should just be LIMIT 0,1 for the latest, and LIMIT 9,1 for the 10th.

 

Yeah, that's my take on it too -- limit the results SQL-side instead of on the PHP side of things... unless you ARE planning on using the entire set... though really most of the 'orientation' commands that require a 'scrollable cursor' don't work with mySQL in my experience.

Also wondering why you're using prepare/exec instead of just query... since you're not actually passing any values.