SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    May 2002
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to filter entries in MySQL ?

    Well, this is wat I wanna do with MySQL and PHP. I wud like to display the newest ten entries of a mysql db via php on my webpages. The mysql db itself will hv more than 10 entries, and new entries will be added from time to time. So how can I display only the newest ten entries?

  2. #2
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Like this:

    SELECT what FROM table ORDER BY what LIMIT 10

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  3. #3
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you'd also probably want to add a 'DESC' since the default sort order is ascending:
    Code:
    SELECT what FROM table ORDER BY what LIMIT 10 DESC
    - Joel

  4. #4
    SitePoint Zealot
    Join Date
    May 2002
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so if I get you right, it shud be..

    SELECT info FROM a_table ORDER BY date LIMIT 10 DESC

    or SELECT info FROM a_table ORDER BY id LIMIT 10 DESC right???

    which one do you guys think it wud be better to filter for the newest ten entries? id or date?

    btw, thanks a lot of helping me out

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the syntax is actually

    SELECT info FROM a_table ORDER BY id DESC LIMIT 10

    as far as whether to use id or date to ORDER BY, i would probably say id if the newest entries will always have the highest id. why ORDER BY id rather than date? because i assume that id is probably the PRIMARY KEY, right? if it is, MySQL will use that index for sorting (by reading the last 10 entries backwards), which is faster. whereas if you ORDER BY date (and date isn't indexed), MySQL will have to select the columns from ALL rows, sort them by date, and finally take the last 10 each time the query is run. so you can see which is more efficient.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  6. #6
    SitePoint Zealot
    Join Date
    May 2002
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot!!

    what abt if I wanna randomly choose ten entries from mysql? wat shud I replace with ORDER BY ?

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT info FROM a_table ORDER BY RAND() LIMIT 10




Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •