0

I'm trying to make it so messages are displayed like this

*Old Message

Sooner Message

Newer Message

Latest Message*

I tried a few different ways. By putting 

  SELECT * FROM place_chat WHERE whereto = '".mysql_real_escape_string($where)."' ORDER BY     id DESC LIMIT 7"

^ But that just displays the results with the latest message at the top, so I tried this.

  SELECT * FROM place_chat WHERE whereto = '".mysql_real_escape_string($where)."' ORDER BY     id ASC LIMIT 7"

^ But then I released that's just going to ONLY display the oldest messages rather than adjust the way it displays and to be sure and tested it. It just displayed the oldest messages.

Could someone please explain the method of doing this? I've been trying to do it for a while now.

Oskar
  • 1,321
  • 9
  • 19
Jordan Richards
  • 532
  • 3
  • 18

2 Answers2

2

You need to get the newest 7 records first, then reorder them:

SELECT *
FROM
(
    SELECT *
    FROM place_chat 
    WHERE whereto = mysql_real_escape_string($where)
    ORDER BY id DESC
    LIMIT 7
) newest_place_chat
ORDER BY id ASC
lc.
  • 113,939
  • 20
  • 158
  • 187
  • Thank you for your reply. I'm having trouble using it though. I have SELECT * FROM SELECT * FROM place_chat WHERE whereto = '".mysql_real_escape_string($where)."' ORDER BY id DESC LIMIT 7 )newest_place_chat ORDER BY id ASC" however, that did not seem to work. – Jordan Richards Jul 14 '12 at 17:14
  • @JordanRichards Looks like you are missing the open parenthesis in the subselect... – lc. Jul 14 '12 at 17:17
0

Since you set the tag 'php' I assume you query the database from within a php script that also converts the query result into an output, most likely a html table in this case.

You can simply turn around the order in which you traverse the result array inside php. That way you don't have to change anything in your query.

arkascha
  • 41,620
  • 7
  • 58
  • 90
  • 1
    Although possible, it's better to get the correct result from the database directly. – Simon Forsberg Jul 14 '12 at 16:54
  • Why? Since you have to traverse the result set anyway there is no penalty in speed or load. Quite in contrary: you can keep the query more simple this way, since you don't need a sub select. – arkascha Jul 14 '12 at 16:57
  • It's the job of a database to handle those things. And even though the query looks more complex, the database performs the operation very fast. Also, if you want to show the exact same data on another page or another platform, it's always better to let the query give you the data as it's meant to be instead of implementing multiple different post-query code to reverse the order of the result. – Simon Forsberg Jul 14 '12 at 19:07
  • I do understand what you are trying to express. And in general I agree with you: keep the data source as clean as possible. However in this case there are no 'multiple' 'different' implementations. The result has to be traversed, there is no additional code. Zero. You are trying to make that appear more complex and round-the-corner-like as it actually is. Why ? – arkascha Jul 14 '12 at 20:02
  • AFAIK, the only way to traverse a database result backwards is by using mysqli_store_result or a similar function. When traversing a result normally, you use one of the ***_fetch_row functions which always traverse it the order it is received from the database. So I disagree with "there would be no additional code" unless you can give me an example. Database results are meant to be handled in the order they are received from the database. – Simon Forsberg Jul 14 '12 at 21:35
  • Actually you are right there, I did not think that way round. My fault. – arkascha Jul 14 '12 at 21:43