Hey ginerjm. Sorry I disappeared for a while. I've been attempting every JOIN and every UNION I can think of and kept thinking "AH! This next attempt should give me what I want, but to no avail." Anyway, I realized that I haven't given you sufficient info for you to be able help as effectively. First things first I need to help you understand the structure of the tables:
comments
id | comment | username | photo_file | date_time
photos
id | username | initiator | file | gallery | did_read | date_time
So, the reason I was using the "DISTINCT" value is because in the photos table their are numerous duplicates of both the image file "file" and the "initiator" (the user who uploaded the image file). These duplicates are for notification purposes for the user's "friends". The reason that either "username" or "initiator" has to equal "$log_username" is so that only the logged in user and his/her friends can see the uploaded photos.
So the rows and columns don't match up at all. The columns don't match up at all. The only thing that ties these tables together at all is the image file ("file" for photos table, "photo_file" for comments table) but again even the image files aren't in any particular order. When the user submits a comment it gets inserted into the comments table with the username, the photo file the comment was made for, the comment itself and the date_time and obviously the id is auto increment.
Also, another thing to note is that regardless of how the query looks I need to be able to fetch the data and use a "while" loop so that I can "echo" the actual image itself, info about the image such as who uploaded it and what gallery it was uploaded to as well as the comments for that specific image and the respective users who made the comments.
So now to my attempts:
I initially wanted to see if I could simply get all photos to show along with all comments for those respective photos regardless of the logged in user and their friends so I attempted this:
$sql = "SELECT photos.initiator, photos.file, photos.gallery, comments.comment FROM photos LEFT OUTER JOIN comments ON photos.file=comments.photo_file ORDER BY photos.file";
This just gave me a ton of dead images with no comments under each image other than the static comment text (: |).
I also attempted a UNION. I added two 'NULL' columns to the comments table so that both tables would have an equal amount of columns so the UNION could work.
$sql = "SELECT DISTINCT initiator, username, file, gallery, did_read, date_time FROM photos WHERE username='$log_username' OR initiator='$log_username'
UNION
SELECT id, comment, null1, username, null2, photo_file, date_time ORDER BY date_time DESC";
This was the closest I've been able to get however it was displaying all of the photos including duplicates and then all of the comments with no photos along with comments showing in the wrong places etc.
I attempted numerous variations of these including the example you gave above. I will say any attempt at a JOIN I'm echoing out dead images as if the
$file = $row["file"];
$image = 'user/'.$u.'/'.$file; doesn't even exist
At any rate that's where I'm at. I've also considered just adding the comments stuff into the photos table but I can't see how I could do that without constantly altering the order of the photos and/or limiting the amount of comments.
Anyway...that's all I got.