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.
"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"
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.
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.
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.