how to get the first and last results from a MYSQL query using PHP PDO?

[butlimous]

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

 

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

[PFMaBiSmAd]

Those two options are only available when the result set has been setup with a scrollable cursor (assuming your database type/driver supports scrollable cursors). You would need to add the following as the 2nd parameter to the ->prepare() statement -

 

array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)

[butlimous]

Hey

 

Thanks for your input. Tried it like this before with no avail also. I don't know if it has something to do with configuration of PHP or what.

 

$aid = $DBH->prepare("SELECT id FROM kisses 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];

 

I can also get the correct results using the following query with subquery:

 

SELECT MIN(id),MAX(id) FROM (SELECT id FROM articles ORDER BY id DESC LIMIT 10) t

 

However a lot of people advise against using subqueries on a large scale because they are performance hit.

[Jessica]

What results DO you get when you use that code? 

[butlimous]

What results DO you get when you use that code?

 

Hey Jessica

 

Which one? You mean the one after adding

PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL

?

 

I'm getting 11 and 10 (the reason for 10 is that using fetch another time will skip the previous results so the first time it fetched 11 and then at the second time it skips the 11th row and start from the 10th row)

 

Thanks

[PFMaBiSmAd]

https://bugs.php.net/bug.php?id=34625

 

^^^ Assuming you are using a mysql database (lol, which I just reread your thread title and you are), this is not supported.

 

You will need to use the fetchall() method and access the first and last element in the fetched array.

Edited February 3, 2013 by PFMaBiSmAd

[butlimous]

Thanks....surprisingly very old bug!

[Jessica]

https://bugs.php.net/bug.php?id=34625

You will need to use the fetchall() method and access the first and last element in the fetched array.

 

Just thinking out loud, but if all he wants is the first and last, wouldn't it be better to just do two queries, one ASC and one DESC? 

If OP is going to use the results at all that's different, but hypothetically if he only wants first and last?

[butlimous]

Hey

 

Do you think would it be better performance wise using the following subquery:

SELECT MIN(id),MAX(id) FROM (SELECT id FROM articles ORDER BY id DESC LIMIT 10) t

 

Or using 2 separate queries??

 

Thanks

[Barand]

I wouldn't worry about the performance hit with that subquery. When I ran these two queries on a 200,000 row table they both took less than 0.001 seconds.

 

SELECT MIN(id),MAX(id) FROM (SELECT id FROM bigtable ORDER BY id DESC LIMIT 10) t;

SELECT id FROM bigtable ORDER BY id DESC LIMIT 10;

[butlimous]

thanks Barand for your effort