SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Setting Up A User Comment Feed Query

    Let me first explain what it is I'm trying to accomplish. I have a site that has a bunch of topics with comments and I want users to be able to subscribe to other users' comments. Under each user's public profile, any other user can press a link: "subscribe to USERNAME's comments"

    What happens when that link is pressed is that some data is put into a table:

    feed_id: The unique identifier of each feed
    feed_from: Who the feed is coming from
    feed_to: Who the feed is going to

    So that is all well and good. The problem is when I try to set up a query to grab all of the most recent comments made by people my users subscribe to and spit them out.

    I have this to start:

    PHP Code:
    $sql_get_comments_feed_from "SELECT feed_from FROM users_feeds WHERE feed_to = '$_SESSION['username']' GROUP BY feed_from";
        
    $res_get_comments_feed_from mysql_query($sql_get_comments_feed_from); 
    From there, I would imagine I would use the resulting feed_from usernames to search my comments table for all of the most comments made by these users by date. Make sense? If not, I'd be more than happy to elaborate.

    Any help would be appreciated.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sp0om
    The problem is when I try to set up a query to grab all of the most recent comments made by people my users subscribe to and spit them out.
    you described the setup very well, but you never actaully said what the problem is.

  3. #3
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry if I wasn't clear.

    From there, I would imagine I would use the resulting feed_from usernames to search my comments table for all of the most comments made by these users by date.
    This is what I want to do, but I don't know what the code would be. I started putting some stuff together

    PHP Code:
     $sql_get_comments_feed_from "SELECT feed_from FROM users_feeds WHERE feed_to = '$_SESSION['username']' GROUP BY feed_from";
        
    $res_get_comments_feed_from mysql_query($sql_get_comments_feed_from); 
    But I don't know how to grab stuff from my comments table by the users that come from the above query...

    Make sense now?

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it looks like you're setting up to do this in multiple queries. you could do it that way, but even better, you can do it in one query:
    Code:
    select c.comment_text
         , c.user_id
      from comments c
      join users_feeds f
        on f.feed_from = c.user_id
     where feed_to = $_SESSION['username']

  5. #5
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PErfection.

    Can't say I understand the code, but it seems to be working. Thanks much.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    if you're going to be doing more queries, it's EXTREMELY important that you learn joins early on. are you a learn by example kind of person? if so, the above query is equivelant to this, which you might find easier to understand:
    Code:
    select c.comment_text
         , c.user_id
      from comments c
         , users_feeds f
     where f.feed_from = c.user_id
       and feed_to = $_SESSION['username']
    however, DO NOT get in the habit of writing queries in this format; i post it only as an educational tool.


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
  •