How to display ALL comments for Individual Photos (PHP, MySql)

This site is best viewed in a modern browser with JavaScript enabled.

How to display ALL comments for Individual Photos (PHP, MySql)

froppo

Issue: Only one comment will display at a time.

So I built a photo sharing script where a logged in user can share photos with their friends and where the user or their friends can comment on the photos. I've got it so only your friends photos (as well as your photos) will show on the page. I also got it so that when you enter a comment it only shows for the specific photo you're commenting on. The issue I'm running into is that only one comment is showing at a time. I want all comments for all photos to show at once. Here is the script I'm using to pull the pictures and comments:

PHP:

//WHERE I GATHER COMMENT DATA
$sql = "SELECT comment, username, photo_file, date_time FROM comments ORDER BY id ASC";
$query = mysqli_query($db_conx, $sql);	
$commentlist = "";
while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
	$c = $row["comment"];
	$us = $row["username"];
	$pf = $row["photo_file"];
	$time = $row["date_time"];
}	
//WHERE I GATHER PHOTO DATA	
$sql = "SELECT DISTINCT initiator, file, gallery FROM photos WHERE username='$log_username' OR initiator='$log_username' ORDER BY date_time DESC";
$query = mysqli_query($db_conx, $sql);
$photolist = "";
while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
	$u = $row["initiator"];
	$file = $row["file"];
	$gallery = $row["gallery"];
	$image = 'user/'.$u.'/'.$file;

//WHERE I DISPLAY THE PHOTO DATA
	$imagelist .= '&nbsp;&nbsp;<img height="200"
           onclick="this.height=500;"
           ondblclick="this.height=200;" src="'.$image.'" alt="'.$u.'" /><br />&nbsp;&nbsp;Added to <a href="user.php?u='.$u.'"><b>'.$u.''s</b></a> '.$gallery.' gallery<br /><br />       

<form action="comments_system.php" enctype="multipart/form-data" method="post">
&nbsp;<input type="text" name="comment">
<input type="hidden" name="photo_file" value="'.$file.'">
<input type="submit" class="submit" value=" Submit Comment " />
</form><br /><p style="border-bottom: 1px dotted #A0A0A0;"></p>';

//WHERE I DISPLAY THE PHOTO COMMENTS AND DICTATE WHAT COMMENT SHOWS WITH WHAT PHOTO
if ($pf == $file) {
$imagelist .='<p style="background-color:#E0E0E0;">&nbsp;&nbsp;<b>'.$us.'</b>: '.$c.' | '.$time.'</p>
<br /><br /><hr/>';
}
}

HTML:

<div id="pageMiddle">
  <!-- START Page Content -->
  <div id="notesBox">&nbsp;&nbsp;<br />&nbsp;&nbsp;<font size=4><b>Photos</b></font><br />
  </br /><br />
  <?php echo $imagelist; ?></div>
  <div style="clear:left;"></div>
  <!-- END Page Content -->
</div>

Again, the issue is that only one comment is showing up at a time. I'm looking for all comments to show under the respective photos. Any help you can give would be GREATLY appreciated. I've been trying to figure this out for weeks.

ginerjm

You run the first query, loop thru the results if it, and store the row data into a set of vars. Over and over. At the end of that loop, you have ONE set of info from the last result row. Then you do a query for the images and display them along with that one set of info from the first query.

You need to join your queries so that you have all your pics and comments in one place and can then loop thru them.

froppo

Hey ginerjm,

Thanks so much for responding! I really appreciate it. I get what you're saying but I'm having a hard time visualizing how I would join the two queries. What would that look like? Any chance you could give me a code example of what you explained? I'm a very visual person so I "get it" a lot easier when I see it.

Thanks!

ginerjm

Your two tables are related, no? They have some common info so that the comments can be connected to the images. You can (and will eventually have to learn how) 'join' two queries by writing a select within a select. I'm sure some well-meaning indiv will probably post a solution for you, but you should really read up on doing 'join' in your query. Basically, you would start by querying the images you want to show from your 'a' table, and include some fields from the comments table (your 'b' table) and have a where clause selecting the images. This would then be extended with a join clause that contained a new query ('select') that chose those fields that you want from the comments table and after this select you add a 'on' clause to connect the recs of the two tables.

$q = "select (a.),(b.) from images a where a.(fld)='xxx'
join (select (*) from comments ) as b
on a.(fld)=b.(fld)

This could be rough but it's basically how it would look.

The only thing to think of when processing the results is to check if the current record's image name is the same as the last one so that you don't re-display the image, only the comment data from that record. When the image name changes, then you display the image and the comment.

froppo

OK So I did some research on joining queries but I had a hard time finding a solid example of the formatting. What you wrote above was somewhat helpful and gave me a good start but I'm still struggling with how to format the query for my specific code. Anyone have a real life example where they joined two queries successfully or anyone willing to take the time to show me how I would join my two queries (above)?

FYI the two tables are related by the photo filename "photo_file" for the comments table and "file" from the photos table.

Thanks!

ginerjm

show me your attempt and I'll work on it.

froppo

Thanks ginerjm. So, I'll give you the things I attempted and what the result was.

//WHERE I GATHER PHOTO DATA	
$sql = "SELECT comment, username, photo_file, date_time FROM comments
UNION
SELECT DISTINCT initiator, file, gallery FROM photos WHERE username='$log_username' OR initiator='$log_username' ORDER BY date_time DESC";
$query = mysqli_query($db_conx, $sql);

$c = $row["comment"];
$us = $row["username"];
$pf = $row["photo_file"];
$time = $row["date_time"];
$u = $row["initiator"];
$file = $row["file"];
$gallery = $row["gallery"];
$image = 'user/'.$u.'/'.$file;

//WHERE I DISPLAY THE PHOTO DATA
	$imagelist .= '&nbsp;&nbsp;<img height="200"
           onclick="this.height=500;"
           ondblclick="this.height=200;" src="'.$image.'" alt="'.$u.'" /><br />&nbsp;&nbsp;Added to <a href="user.php?u='.$u.'"><b>'.$u.''s</b></a> '.$gallery.' gallery<br /><br />       

<form action="php_parsers/photocomments_system.php" enctype="multipart/form-data" method="post">
&nbsp;<input type="text" name="comment">
<input type="hidden" name="photo_file" value="'.$file.'">
<input type="submit" class="submit" value=" Submit Comment " />
</form><br /><p style="border-bottom: 1px dotted #A0A0A0;"></p>';

//WHERE I DISPLAY THE PHOTO COMMENTS AND DICTATE WHAT COMMENT SHOWS WITH WHAT PHOTO
if ($pf == $file) {
$imagelist .='<p style="background-color:#E0E0E0;">&nbsp;&nbsp;<b>'.$us.'</b>: '.$c.' | '.$time.'</p>
<br /><br /><hr/>';
}
}

Result: A single broken image link. Only the ":" and "|" from the comment section show

//WHERE I GATHER PHOTO DATA	
$sql = "SELECT DISTINCT (initiator*),(file*),(gallery*) FROM photos WHERE (username*)='$log_username' OR (initiator*)='$log_username' ORDER BY date_time DESC JOIN (SELECT * FROM comments) AS file(username)=photo_file(username)";

$query = mysqli_query($db_conx, $sql);


$time = $row["date_time"];
$u = $row["initiator"];
$file = $row["file"];
$gallery = $row["gallery"];
$image = 'user/'.$u.'/'.$file;
$c = $row["comment"];
$us = $row["username"];
$pf = $row["photo_file"];

//WHERE I DISPLAY THE PHOTO DATA
	$imagelist .= '&nbsp;&nbsp;<img height="200"
           onclick="this.height=500;"
           ondblclick="this.height=200;" src="'.$image.'" alt="'.$u.'" /><br />&nbsp;&nbsp;Added to <a href="user.php?u='.$u.'"><b>'.$u.''s</b></a> '.$gallery.' gallery<br /><br />       

<form action="php_parsers/photocomments_system.php" enctype="multipart/form-data" method="post">
&nbsp;<input type="text" name="comment">
<input type="hidden" name="photo_file" value="'.$file.'">
<input type="submit" class="submit" value=" Submit Comment " />
</form><br /><p style="border-bottom: 1px dotted #A0A0A0;"></p>';

//WHERE I DISPLAY THE PHOTO COMMENTS AND DICTATE WHAT COMMENT SHOWS WITH WHAT PHOTO
if ($pf == $file) {
$imagelist .='<p style="background-color:#E0E0E0;">&nbsp;&nbsp;<b>'.$us.'</b>: '.$c.' | '.$time.'</p>
<br /><br /><hr/>';
}
}

Result: Got the exact same thing here as well. A single broken photo link and the same result for comments.

ginerjm

Sorry I don't see the new query utilizing a join. That is all I need to see.

froppo

Hey ginerjm,

Sorry. So I guess the "UNION" command in the first example

...FROM comments
UNION
SELECT DISTINCT initiator, file, gallery FROM photos...

and the "JOIN" command from the second example:

FROM photos WHERE (username)='$log_username' OR (initiator)='$log_username' ORDER BY date_time DESC JOIN (SELECT * FROM comments)

isn't quite what you were looking for? So that's not the aspect of the code you were wanting me to join/combine? What am I missing here?

Thanks

ginerjm

I didn't bother to examine ALL that code - all I want is your query and we'll take it from there.

ginerjm

Don't know where you went.......

Anyway:::

Assuming that your image name is already a unique value then DISTINCT is probably not needed. As for the syntax below - so much I don't recognize or know what it is.

$sql = "SELECT DISTINCT (initiator*),(file*),(gallery*) FROM photos WHERE (username*)='$log_username' OR (initiator*)='$log_username' ORDER BY date_time DESC JOIN (SELECT * FROM comments) AS file(username)=photo_file(username)";

My try:


$sql = "select p.*, c.* from photos p 
   where (p.some_field) = '$log_username' or (p.some_other_field)='$log_username'
   left outer join (select * from comments) as c
   on p.(filename_field)=c.(filename_field)
   order by p.(filename_field)";

That would be my first attempt. You fill in the appropriate column names where I have parenthesized names. Your selection of photos will be determined by the two fields in photos that match the user name, while your selection of comments will be those that match the selected image names belonging to the selected user name.

froppo

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

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.

ginerjm

comments
id | comment | username | photo_file | date_time

1 - id is worthless - dump it.
2 - if file and photo_file are linked that's all you need although that allows for duplicate filenames in the photos table. That's a problem. You should have included either initiator in the comment file, or else the id in the comments file should match the id in the photos file, which I doubt it does.

Without a one-to-one link between the two table I don't know how you can link these two tables.

froppo

Hey ginerjm,

I really appreciate all of the time and effort you have put in to helping me. I've begun the process of redesigning this entire portion of my website. I think it will just be easier to have one table storing all of the required info at once. That way I don't have to worry about "joining" or "unioning" more than one query.

I did have one last question really quick which I believe is a little less complex. Is it possible to SELECT from one table WHERE a value from that table equals a value from another (completely different) table? So I've attempted this (highlighted important parts in red):

$sql = "SELECT [COLOR="#FF0000"]user2[/COLOR] FROM friends WHERE user1='$log_username' AND accepted='1'";
$query = mysqli_query($db_conx, $sql);
while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
        [COLOR="#FF0000"]$user2 = $row["user2"];[/COLOR]

$sql = "SELECT * FROM comments WHERE initiator='$log_username' OR [COLOR="#FF0000"]initiator='$user2'[/COLOR] ORDER BY id DESC";

So "initiator='$log_username'" is working as it should but "initiator='$user2'" isn't. I'm trying to filter what photos can be seen based on the logged in user. Only the logged in user should see his photos and his friends photos and vice versa. Even though I've defined the users friends and gathered them in the first query the second query doesn't seem to allow them to see the photos. Is that because the definition is coming from a different table?

Thanks.

ginerjm

It's your project, but if you want to avoid regretting a poor design for the life of it......

IMHO you need to normalize your database. Big word - big implications. A normalized db actually makes it Easier to query for the exact combination of data items you need.

Basically, you should have a table of images. Uniquely identified with an id and having at minimum the provider's user id and the image name should do the trick. Assign any other 'image' related info to that table and that's that. Secondly you should have a 'users' table with attributes that pertain to each individual who wishes to register and then post comments or provide images. Assign an id to each user, which I think you had an auto-inc field for previously.

Finally you should then have a table of comments posted. For each comment provide the user-id and the image-id and then you can join everything together with simple queries.
Let's say you build:

Users: user_id, name, registration_date, any other info collected by a "registration process".

Images: image_id, provider_id (the user-id of the provider), image_name, date_added, and any other image-related info fields

Comments: user_id, comment, image_id, date_written, etc.

A std. query for getting all the images a user commented on would then be:

$q = "select *.c, *.i from comments c, images i 
         where c.user_id = '$userid' and 
          c.image_id = i.image_id 
         order by i.date_added, c.image_id, c.date_written";

This would give you a user's comments in order of the date of the image and date of the comment for each image.

Sarah8812

Really great help ginerjm, thanks.

I had a similar problem, that i've basically wasted the last 2 days trying to fix - but FINALLY i think i have the solution. Thanks!

Sarah

ginerjm

Happy to help! I only help that all my efforts eventually helped the OP - haven't heard anything back in quite some time...