SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2000
    Posts
    528
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello.

    I have set up a MySQL database, and want to have a thing on my main page that displays the last 10 articles from the database. What command do I use for this?

    Any help would be greatly appreciated.
    Corbb O'Connor
    Looking for quality website design or database programming?
    Contact me for more information and a FREE quote!

  2. #2
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Is this what you are looking for:

    SELECT * FROM articles LIMIT 10

    Then you'd mysql_query it, then

    while($result=mysql_fetch_array($query)) {
    extract($result);
    print $title;
    }

  3. #3
    ********* Addict
    Join Date
    Apr 2000
    Location
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since you want the last 10 articles you'd probably want your query to be:

    SELECT * FROM articles LIMIT 10 ORDER BY last_updated DESC

    It might depend on how you store the date, though. (Probably the best way is to store the timestamp, because then you can do all sorts of funky things with it.)

    Pedro
    Last edited by pedro_gb; May 20, 2001 at 15:11.

  4. #4
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Or you can do what I do and order by ID as sometimes the date is unreliable.

    So just add ORDER BY ID DESC

  5. #5
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by petesmc
    Is this what you are looking for:

    SELECT * FROM articles LIMIT 10
    That would only display the first 10 rows of article table.

    Corbbmacc,

    Just include the OFFSET parameter in the pete's code,

    Code:
    SELECT * from articles LIMIT N, 10
    where N is the total number of articles minus 10.

  6. #6
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Pedro, pete

    wow, guys you are really fast (I was writing my post)

  7. #7
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The best way is Pedro's way, as it doesn't involve any other queries or a complex query to find out how many items are already in the database.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by pedro_gb
    SELECT * FROM articles LIMIT 10 ORDER BY last_updated DESC
    I think you will find that the limit clause will need to come after the order by clause. Something like this:

    SELECT *
    FROM articles
    ORDER BY last_update DESC
    LIMIT 10

  9. #9
    SitePoint Evangelist
    Join Date
    Dec 2000
    Posts
    528
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot everybody!
    Corbb O'Connor
    Looking for quality website design or database programming?
    Contact me for more information and a FREE quote!

  10. #10
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, and actually, nothing personal Pete, but the ID is much more unreliable than the date. The date is far more reliable. In fact, the only way it can be considered unreliable is that there's a problem when you have two entries from the same day at times.

    This can be fixed by simply storing a timestamp in the database though. This narrows the time of entry down to the very last second. You can assign the timestamp a variable like this:

    $time = mktime();

    Good luck.


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
  •