Display Posters avatar.

[system]

Ok i have a site that has a member system and users are able to leave comments on news posts and such if logged in.

What I am trying to do is display the user’s avatar next to each of their comments based on poster ID.

When a user posts a comment i pull different info from their account/site such as userid, username, newsid and store it in a table called “comments” along with the comment text.

This is part of my code for displaying comments, I want to display the posters avatar based on $row[‘posterid’] for each comment. The problem is posterid is stored in a table called “comments” and the users avatar is stored in a table called “users”. There is no way to do something like this is there?

[php]

<?php $ava = mysql_query("SELECT * FROM `users` WHERE user_id='".mysql_escape_string($row['poserid'])."'") or die(mysql_error()); $avatar=mysql_fetch_assoc($ava); echo ''.$avatar['user_avatar'].''; ?>

[/php]

Display Comments:
[php]

<?php $query1 = "SELECT * FROM $tableName WHERE newsid='".mysql_escape_string($_GET['newsid'])."' ORDER BY com_id ASC LIMIT $start, $limit"; $result = mysql_query($query1); while($row = mysql_fetch_array($result)) { echo '

Post #'.$row['com_id'].' by '.$row['user_name'].' on '.$row['date'].'
'.nl2br($row['comment_text']).'

'; } ?>

[/php]

[aztech4mac]

I’ve done this many times in the past, but a little different.

First, a member account must have ALL data about a member in a single table on a single row.

For example; create a table named members and add columns…

id, fname, lname, sex, age, height, weight, address, city, state, zip, phone, email, picpath, notes (your table may vary).

the column pictpath contains the image file name (and sometimes the full path to the image file).

Next, add id, username, email, and pictpath to session vars at login. (You had to do a mysql query to validate the login, add session vars here)

Now, I build a second table named comments and add the columns…

id, articleid, date, userid, username, useremail, userpicpath, comment (again your table may vary).

now, to add a comment to an article…

INSERT INTO comments (articleid, date, userid, username, useremail, userpicpath, comment) VALUES (’$thisarticle’, ‘$date’, ‘$userid’, ‘$username’, ‘$useremail’, ‘$userpicpath’, ‘$comment’)

‘$userid’, ‘$username’, ‘$useremail’, ‘$userpicpath’ are all grabbed from session vars.

now, to display all comments for an article…

SELECT * FROM comments WHERE articleid = ‘$thisarticle’ ORDER BY date ASC

$row[‘userpicpath’] will contain the path to the image.

simple and easy…

[system]

I’ve done this many times in the past, but a little different.

First, a member account must have ALL data about a member in a single table on a single row.

For example; create a table named members and add columns…

id, fname, lname, sex, age, height, weight, address, city, state, zip, phone, email, picpath, notes (your table may vary).

the column pictpath contains the image file name (and sometimes the full path to the image file).

Next, add id, username, email, and pictpath to session vars at login. (You had to do a mysql query to validate the login, add session vars here)

Now, I build a second table named comments and add the columns…

id, articleid, date, userid, username, useremail, userpicpath, comment (again your table may vary).

now, to add a comment to an article…

INSERT INTO comments (articleid, date, userid, username, useremail, userpicpath, comment) VALUES (’$thisarticle’, ‘$date’, ‘$userid’, ‘$username’, ‘$useremail’, ‘$userpicpath’, ‘$comment’)

‘$userid’, ‘$username’, ‘$useremail’, ‘$userpicpath’ are all grabbed from session vars.

now, to display all comments for an article…

SELECT * FROM comments WHERE articleid = ‘$thisarticle’ ORDER BY date ASC

$row[‘userpicpath’] will contain the path to the image.

simple and easy…

I thought about doing it this way to, but the problem with doing it this way is if the user changes their avatar it won’t update the avatar displayed for each of their comments will it? i would have to add an UPDATE for the comments avatar as well if the user changes their avatar. I really didn’t want to have to add more to my edit profile script. Way too much the way it is.

[aztech4mac]

it will work perfectly if the image changes but the filename does not!

[phphelp]

When a user posts a comment i pull different info from their account/site such as userid, username, newsid and store it in a table called “comments” along with the comment text.

But you are already doing this way… if user change their username you’ll have to UPDATE this field in the ‘comments’ table. So adding one more field to the ‘comments’ table will not make much difference.

However, there is a more effective way to implement this. You can pull user avatars from the ‘users’ table in the same query where you select comments to display, like this:

SELECT `comments`.*, `users`.user_avatar FROM `comments` LEFT JOIN `users` ON `users`.user_id = `comments`.posterid  WHERE ... etc.

[aztech4mac]

Yes, but in my experience member accounts have real names and nicknames. Real names never change, nicknames do. Nicknames are substituted for real names on public pages.

Seems like you got him what he wanted… could have saved me some typing

[system]

But you are already doing this way… if user change their username you’ll have to UPDATE this field in the ‘comments’ table. So adding one more field to the ‘comments’ table will not make much difference.

However, there is a more effective way to implement this. You can pull user avatars from the ‘users’ table in the same query where you select comments to display, like this:

SELECT `comments`.*, `users`.user_avatar FROM `comments` LEFT JOIN `users` ON `users`.user_id = `comments`.posterid  WHERE ... etc.

Thanks for the reply. That did the trick. As for the username’s you are not able to change them on my site so i don’t have to worry about that.