Jump to content


Photo

Query last 20 entries


  • Please log in to reply
3 replies to this topic

#1 lance

lance
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 08 July 2003 - 05:49 PM

Or 10 or whatever number you want to display (not all). I\'m new to all this wonderful PHP MySQL stuff and would like to display a table of the last 20 entries made. To be more specific it\'s a PIREP reporting system for pilots to submit their flight hours, destination, etc...

I can\'t display all, but would like to be able to display the last 20 or so pireps in a table.

I AM Able to query the database and display ALL records, but that page will be come waaaaaaaaaay to large very fast ;-)

Thanks for any help or any links you could provide to tutorials that might help me understand how to do this.
Lance

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 08 July 2003 - 07:39 PM

use the limit feature at the end of your query. e.g.: select * from table limit 20
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 dammitjanet

dammitjanet
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationUK

Posted 08 July 2003 - 10:13 PM

just to expand of effigy\'s post you can do this.

select count(*) from table;

and stick it in a variable called $max ... and subtract 20 from it.

then make your query string
\"select * from table limit $max,-1\"

which should give you the last 20 lines of the table.

you may want to stipulate an order by clause on a datetime field or unique incremental to make sure the data is the last 20 entries.

#4 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,667 posts
  • LocationLos Angeles, CA USA

Posted 09 July 2003 - 07:15 AM

Mysql\'s limit feature is one of the really nice things it offers. That\'s definately the way to go. Hopefully you have a key to the table using auto_increment, or have a timestamp column. Then you would not have to bother with trying to get the count(*) and do a limit count(*)-20, and would instead simply do:

SELECT * FROM Table ORDER BY Table_id DESC LIMIT 20;

This will display the rows most recent first. May be what you want, and saves an extra query.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users