SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Help with query

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with query

    I have two tables poems and pcomments. I'm trying to display the last 5 commented poems, but I don't want repeating poems. So if the two newest comments are for one poem, I want that poem displayed only once, not twice. I tried a join query but I suck at joins and the people in the MySQL forum said it was a php problem, so I'm here.

    Code:
    poems
    poem_id
    title - used to create a url link
    rtitle - actual poem title
    
    pcomments
    pc_id
    poem_id
    posted
    I have more fields but figured these are the relevant ones for the problem. I need to grab the title and rtitle of the 5 last commented poems, not poem comments. Any help would be appreciated!!

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 700lbGorilla View Post
    the people in the MySQL forum said it was a php problem
    Don't see how that could be if this is what you posted; there's no clue that you're using any particular language here...

    Code:
    SELECT 
      poem_id, 
      title, 
      rtitle
    FROM poems
    WHERE poem_id IN (
      SELECT DISTINCT poem_id
      FROM pcomments
      ORDER BY posted DESC
      LIMIT 5
    )

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I posted my query there to, forgot to post it here, sorry. I'll give that a try though thank you, allow it looks like it makes sense, I still don't understand joins.

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well it probably would work, but: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

    This was my old query:
    PHP Code:
    "SELECT p.title, p.rtitle, p.poem_id, max(c.posted) AS latestcomment FROM poems p JOIN pcomments c ON c.poem_id = p.poem_id GROUP by p.poem_id ORDER BY latestcomment DESC LIMIT 5"

  5. #5
    Passionate Web Developer Egyptechno's Avatar
    Join Date
    Jan 2004
    Location
    Dubai
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a good idea for you, will improve your dbdesign ..

    alter 1 field in poems table called "comments"

    anytime, you add a comment to pcomments , increase the value of " comments " by 1

    anytime you delete a comment from pcomments , decrease the value of " comments " by 1

    then the query at the end will look like :
    PHP Code:
    mysql_query("select poem_id, rtitle from poems where comments != 0 order by poem_id desc limit 5"); 

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the idea, but I hate unnecessary query inserts, but I got the join working and it is working fine.

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Just a note that it's been almost 3 years since MySQL 4.1 went production-ready, and the current version is 5.1. To not support subqueries you're running a very old copy and it might be time to upgrade

  8. #8
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I'm through a host so can't do much about it, I know it sucks. They are running PHP 5.1.6, but yeah, still 4.1 for MySQL.

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 700lbGorilla View Post
    still 4.1 for MySQL.
    4.1 is fine, that's the first version that supported subqueries. I must've made a mistake if that query above didn't run on 4.1. Anyway, most (but not all) subqueries can be rewritten as joins, so glad you got it working either way.


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
  •