MySQL JOIN

[eluser]tekhneek[/eluser]
Essentially you're joining hence "JOIN" the two tables together using a unique identifier (in most cases) such as an ID or UID, either or -- the concepts are the same whereas the naming is unique.

In database normalization 1st through 3rd normal forms the primary concerns are assuring that each table only contains the necessary information to describe itself entirely. You wouldn't have a "comments" column inside of the photos table, rather you'd have "photos_comments" where you used the photos ID as a foreign key on the photos_comments table

Code:

photos.id = photos_comments.photos_id WHERE photos.id = $row->id

You'll notice that the photos.id = photos_comments_photos_id is obviously signifying a relationship between the two, however this type of "JOIN" sometimes referred to as an "EQUIJOIN" or just plain "JOIN" means that we're going to get all of the comments for their corresponding photo.

The PROBLEM with this is that if you have 10 comments for 1 photo you're going to return the photos table, as well as the photos_comments table 10 times. You'll have 10 unique rows from the comments table and 1 identical photos row repeated because you never specified a unique identifier such as a photo id (or photos.id)

I noticed that you have userprofiles and users -- can a user have more than one profile? If not then this is considered a "one-to-one" relationship meaning that the only purpose of joining these is to make the resulting query easier to use. You might be able to combine userprofiles into the users table depending on the unique needs of your database architecture/application workflow.

In plain english "Let's get all of the users and their corresponding profiles for a given user id, and the users id that we want this information for is {$row->id}" albeit the majority of the time you will be using a variable substitution over a static user id (because if you got the user with id 5 every time this is a very linear approach, and not dynamic.)

I hope I explained this for you better -- I'm not an SQL expert so here are some resources that have helped me grasp some of these concepts.

http://www.codinghorror.com/blog/archives/000976.html <-- Visual explanation of JOINs (VERY good read.)
http://en.wikipedia.org/wiki/Database_normalization <-- Database normalization

Hope this helped.