SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A Quick Syntax Question

    I'm confused why this query is selecting profile.name twice under two different aliases. Later this query does something similar by using an OR check to compare variable $user to 2 versions of profile.user_id.

    I'm guessing we're using these two different prefixes because we're pulling back two different user_id's corresponding to whichever of the conditional checks evaluated to true?

    Sorry if I just answered my own question. But thank you for any corrections or clarifications.

    PHP Code:
    "SELECT t.type_reference, t.type_name, s.*, UNIX_TIMESTAMP(s.posted) as timestamp, 
    p.name as poster_name, r.name as profile_name 
    FROM statuses s, status_types t, profile p, profile r 
    WHERE t.ID=s.type 
    AND p.user_id=s.poster AND r.user_id=s.profile 
    AND ( p.user_id=
    {$user} OR r.user_id={$user} 
    OR ( p.user_id IN (
    {$network}) AND r.user_id IN ({$network}) ) ) 
    ORDER BY s.ID DESC LIMIT 
    {$offset}, 20" 

  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)
    It would help if we knew what any of these tables were or what this query was supposed to do.

    Just based on the table names, I'm guessing this is something to do with posts on a profile of some kind of social website (like a Facebook "wall"), and one of the names is the person that made the post, and the other is the name of the profile they posted on.

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, you guessed it. The query is supposed to pull back the type of comment, who made it and upon whose profile it was posted.

    Results would show in a user's status stream.

    The query matches the p.user_id to the person who posted the status update and matches r.user_id to the person who has had their status updated.

    The query then checks that the person who is viewing this status is either in the same network as person who is posting as well the person who owns the profile the posting is made to, or that it is the viewer's own profile or his own post to another user's profile.

    I'd been dwelling on this query for days. It's just now coming together. I didn't realize that MySQL allows you to query the same column, draw multiple rows and distinguish between them by assigning the columns multiple prefixes.

  4. #4
    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 maestro360 View Post
    I'd been dwelling on this query for days. It's just now coming together. I didn't realize that MySQL allows you to query the same column, draw multiple rows and distinguish between them by assigning the columns multiple prefixes.
    You should pick up a book on relational databases. SitePoint publishes one. Your mental abstraction still isn't quite there. You're not creating multiple rows here, you're taking two existing rows from the same table and combining them into one row (think pasting the second row beside the first one, horizontally) based on the join condition. You differentiate between the same named columns in this single row by the alias.


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
  •