CODEWITHSUNDEEP
phpmysqlpdo
Greg Viv
Greg Viv

Reputation: 847

Select last 5 records and sort them ascending in PDO

I'd like to select last 5 records in table and sort them ascending. First i'd like to say that I'm aware of sollutions for this question already provided on stackoverflow (like Select last 20 order by ascending - PHP/MySQL). What i have problem with is to make them work in PDO. Here is my code:

$pdo = new PDO('mysql:host=localhost;dbname=somedb;port=3306', 'someuser', 'somepassword');
$stmval = $pdo -> prepare("SELECT * FROM (SELECT ID, col1, col2, col3 FROM table1 WHERE `col4` = 1 ORDER BY col3 DESC LIMIT 5) tmp ORDER BY col3 ASC" );
$stmval -> execute();
while($somesome = $stmval -> fetch())
{
echo $somesome ['col1'].",".$somesome ['col2'].",".$somesome ['col3'];
}

So basicly it works until I add extra SELECT * FROM (SELECT [...]) inside prepare() how can i select last 5 elements in table and sort them ascending using PDO?

Lets say i have array: 1,2,3,4,5,6,7,8,9,10 I select by mysql last 3 element so i need o use DESC ordering. I have: 10,9,8 now i take it and make it ascending so final result is what i desire: 8,9,10 Problem is whenever i try to modify second line of my code:

stmval = $pdo -> prepare("SELECT [...] ORDER BY col3 DESC" );

to

stmval = $pdo -> prepare("SELECT * FROM (SELECT [...] ORDER BY col3 DESC) ORDER  BY col3 ASC" );

it just wont work for some reason.

Upvotes: 3

Views: 1960

Answers (2)

Greg Viv
Greg Viv

Reputation: 847

Alright I;ve managed to figure this out and I'm a bit ashamed I could miss it.

My problem was that below code worked cause you do not have to SELECT col3 to ODRED BY it:

$stmval = $pdo -> prepare("SELECT col1, col2 FROM table ORDER BY col3 DESC LIMIT 5");

Howether in below case:

$stmval = $pdo -> prepare("SELECT * FROM (SELECT col1, col2 FROM table ORDER BY col3 DESC LIMIT 5) tmp ORDER BY col3 ASC);

You'll get error cause middle SELECT wasn't including col3 Final correct code should be:

$stmval = $pdo -> prepare("SELECT * FROM (SELECT col1, col2, col3 FROM table ORDER BY col3 DESC LIMIT 5) tmp ORDER BY col3 ASC);

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

I cannot replicate your findings.

In MySQL:

SELECT * 
  FROM 
     ( SELECT ID
            , col1
            , col2
            , col3 
         FROM table1 
        WHERE `col4` = 1 
        ORDER 
           BY col3 DESC LIMIT 5
     ) tmp 
 ORDER  
    BY col3 ASC;
+----+------+------+------+
| ID | col1 | col2 | col3 |
+----+------+------+------+
|  9 |   27 |   13 |   81 |
| 10 |   68 |   97 |   82 |
| 23 |   22 |   71 |   88 |
|  6 |   35 |   81 |   99 |
| 16 |   77 |   46 |  100 |
+----+------+------+------+

In PHP:

27,13,81
68,97,82
22,71,88
35,81,99
77,46,100

Upvotes: 1

Related Questions