PHP: MYSQL QUERY Ordery By

Question

Image Table

Hello i have a table comments and i need to take the last three comments but another way like this:

8
9
10

My code show this:

10
9
8

Code:

$sql_query = $connection->query("SELECT * FROM comments WHERE `post_id` = '38' ORDER BY `id` DESC LIMIT 3");

while ($ff = $sql_query->fetch_array(MYSQLI_ASSOC)) {
    echo $ff["text"]. "</br>";
}

Thanks in advance !

Wrap your statement in a subquery and resort it, in ascending order, by comment ID. — Terry
– Terry, Commented Dec 26, 2016 at 21:36
@Fred-ii- Interesting. In this particular case, I think the easiest way is to reverse the set with PHP but indeed if the count of rows is known limit with an offset is a super clean way of doing it. However I don't know if it is possible to count the rows as a subquery and use it as offset, all in one query. Just posted a question about that. — sidyll
– sidyll, Commented Dec 26, 2016 at 22:30
@sidyll doubly interesting comment there. I'll keep an eye out on your question, cheers — Funk Forty Niner
– Funk Forty Niner, Commented Dec 26, 2016 at 22:31

Community · Accepted Answer · 2017-05-23 11:53:43Z

2

You can do it with a subquery, as described in this question. However it might be easier to just use PHP's array_reverse():

$rows = array_reverse($sql_query->fetch_all(MYSQLI_ASSOC));
foreach ($rows as $ff)
    echo $ff["text"]. "</br>";

With the subquery, if you wish:

SELECT * FROM (
  SELECT * FROM comments
  WHERE `post_id` = '38'
  ORDER BY `id` DESC LIMIT 3
) t ORDER BY t.id

Basically this creates a derived table aliased to t with the result of your original query, and reorders this limited set of results.

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Dec 26, 2016 at 21:41

sidyll

59.5k14 gold badges115 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Funk Forty Niner Over a year ago

I'll have to remember about that array_reverse(). I've never used it before but might in the future.

reza · Accepted Answer · 2016-12-26 21:48:39Z

0

Following query will give your desider output

select * from (SELECT * FROM comments WHERE `post_id` = '38' ORDER BY `id` DESC LIMIT 3) temp order by id

answered Dec 26, 2016 at 21:48

reza

1,5072 gold badges12 silver badges17 bronze badges

Collectives™ on Stack Overflow

PHP: MYSQL QUERY Ordery By

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related