SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Location
    Spain...
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select * Group By, Order By, Limit?

    Hello im new here but not to web developing...
    well i have a news system made in php, it retrieves all the data from a phpbb forum in a mysql db right?
    What i wanted to do was select only the first post of every forum i chose and not the other posts which are the replies...
    so i tried many things, first something like:

    Code MySQL:
    SELECT * FROM `phpbb_posts` WHERE forum_id=8
    OR forum_id=0
    OR forum_id=12
    OR forum_id=3
    OR forum_id=18
    OR forum_id=20
    OR forum_id=5
    OR forum_id=7
    GROUP BY topic_id DESC
    LIMIT $offset, $newsperpage

    $offset varies depending on the page you are in in the news and $newsperpage is a defined variable (it is 5) in php.

    But it does show replies i think it's beacuse of the limit...
    i tried this too:

    Code MySQL:
    SELECT * FROM `phpbb_posts` WHERE forum_id=8
    OR forum_id=0
    OR forum_id=12
    OR forum_id=3
    OR forum_id=18
    OR forum_id=20
    OR forum_id=5
    OR forum_id=7
    GROUP BY topic_id
    ORDER BY topic_id DESC
    LIMIT $offset, $newsperpage

    But it shows them too...
    and i think it is a LIMIT problem because if i remove the LIMIT and try the queries both work perfectly, without no replies.
    What can i do??
    i don't want to remove the LIMIT because then if i have many news it would take a looong time to load the query in each page... which would be unnecesary.

    Thx in advance to you all!

    P.S:i have also tried something with a DISTINCT but i wasn't sure how the SELECT * but DISTINCT topic_id so it didn't work neither...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    GROUP BY is not compatible with the dreaded, evil "select star"

    if you want the first post in each forum, it would have to be based on either the post_id (assuming it's an auto_increment) or else on some column like post_date

    if you could explain which columns are involved in the posts table, i might be able to help you
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2008
    Location
    Spain...
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh
    anyway i don't want the first post in each forum i want the first post in each topic.
    well the columns involved... i want to get all the columns data but filter out replies.
    i did the attached screenshot so u can see the config.

    thx for the fast help

    tell me if u want more pics or info i want to solve this :P
    Attached Images Attached Images

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i am not familiar with that application, so you will have to tell me which columns to use

    how you you identify "first post in each topic" -- based on lowest post_id?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Dec 2008
    Location
    Spain...
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's phpmyadmin don't u know it?
    well...

    how you you identify "first post in each topic" -- based on lowest post_id?
    that's what i don't know
    im not even sure if that's possible im not really good with mysql
    what i want is to select all the rows but distinct by topic_id column :S
    i don't know what else i can tell you?



    EDIT:
    maybe should i
    Code MySQL:
    SELECT post_id,topic_id,etc. GROUP BY topic_id DESC LIMIT $offset, $newsperpage
    ?

    thx again for your help!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    oh, i know phpmyadmin (and i don't like it -- HeidiSQL is better)

    i don't know phpbb

    so i'm going to guess
    Code:
    SELECT phpbb_posts.* 
      FROM ( SELECT topic_id
                  , MIN(post_id) AS min_post
               FROM phpbb_posts
              WHERE forum_id IN 
                    ( 8,0,12,3,18,20,5,7 )
             GROUP
                 BY topic_id ) AS m
    INNER
      JOIN phpbb_posts 
        ON phpbb_posts.topic_id = m.topic_id
       AND phpbb_posts.post_id = m.min_post
    ORDER 
        BY phpbb_posts.topic_id DESC
    LIMIT $offset, $newsperpage
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Dec 2008
    Location
    Spain...
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh, i know phpmyadmin (and i don't like it -- HeidiSQL is better)
    well i cannot choose which to use its just my hosting program
    well i thought about this:
    Code MySQL:
    SELECT post_id,topic_id,etc. 
    GROUP BY topic_id DESC
    LIMIT $offset, $newsperpage
    do you think it would work?
    thx again!

    EDIT: Tried mine didn't work...
    i really think is something with the LIMIT...
    when the limit is high enough or there isn't a LIMIT it shows it prefectly

  8. #8
    SitePoint Member
    Join Date
    Dec 2008
    Location
    Spain...
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great!! Though you guessed it yours works!!
    awsome man thanks a lot!!
    anyway can you explain me what the code does?
    thx again man!

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sure, i can explain it

    run the subquery by itself, and show me the results it produces

    then see if you can figure out what the INNER JOIN does
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Dec 2008
    Location
    Spain...
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    topic_id / min_post
    3 3
    4 4
    6 6
    7 8
    8 11
    10 13
    14 19
    16 27
    17 30
    18 36
    19 43
    20 46
    22 48
    23 49
    24 50
    25 51
    26 55
    27 56
    28 57

    anyway ill check mysql reference
    so dont care u have already helped me a lot!
    thx really!

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    did you figure out what the results of the subquery actually mean?

    can you see why these results are joined to the posts table?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Dec 2008
    Location
    Spain...
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well i figured out what the result on the subquery mean but not why they are joined

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    your query returns complete posts (using the dreaded, evil "select star")

    but which posts do you want?

    the ones picked by the subquery

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Member
    Join Date
    Dec 2008
    Location
    Spain...
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    nice!
    would you care about explaining me what does INNER JOIN ... ON... exactly do?
    reference isn't really clear :P
    if you haven't got time well i'll search a tutorial.

    And again... Thanks man!

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    an inner join returns rows which match

    have you never done a join before?

    it is described in every SQL tutorial
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Member
    Join Date
    Dec 2008
    Location
    Spain...
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no i haven't thx!
    i didn't really follow any sql tutorials...
    i just learnt from php tutorials.
    i'll check it out!
    bb!
    thx again!


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
  •