SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Forum database

  1. #1
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    scarborough
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Forum database

    I am still learning the basics with databases

    I am making a database for a forum. I am trying to select the list of threads ... page one... page two etc with just lists of thread titles... all listed according to date time.

    How would I code the select where I wanted to selct for page two... with twenty posts per page so that would be between 21 and 40 (with 21 being the 21st most recent post)

    ALSO when I create a table with a primary index do I have to set the index when I select

  2. #2
    SitePoint Enthusiast adityamenon90's Avatar
    Join Date
    Apr 2010
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you're trying to do is called Pagination. Please search "PHP Pagination" on Google, for a gazillion tutorials.

    To make a select starting from a particular key, you should use an OFFSET.

    Getting all data:
    SELECT * FROM thread_list;

    Getting only the first ten:
    SELECT * FROM thread_list LIMIT 10;

    Now, get the index of the last item using PHP. Increment it by 1. Then:
    SELECT * FROM thread_list LIMIT 10 OFFSET last_incremented_id;

    Not sure if I understood your second question...

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hairybobby View Post
    ALSO when I create a table with a primary index do I have to set the index when I select
    no, once the index has been declared/created, you don't touch it again

    Quote Originally Posted by adityamenon90 View Post
    Now, get the index of the last item using PHP. Increment it by 1. Then:
    SELECT * FROM thread_list LIMIT 10 OFFSET last_incremented_id;
    i don't think you need to get anything from php about the last item, it's the same number each time

    also, LIMIT makes very little sense without ORDER BY

    so the first query, to get the first 20 rows, would be ...
    Code:
    SELECT columns FROM thread_list 
    ORDER BY postdate DESC LIMIT 0,20
    then to get the next 20 rows ...
    Code:
    SELECT columns FROM thread_list 
    ORDER BY postdate DESC LIMIT 20,20
    then to get the next 20 rows ...
    Code:
    SELECT columns FROM thread_list 
    ORDER BY postdate DESC LIMIT 40,20
    and so on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •