Page 1 of 1

JOIN not retrieving the correct user's profile image

Posted: Fri Mar 26, 2010 3:17 pm
by ninethousandfeet
If a user sends a comment to another user, it appears something like: User123(comment_username) to User876(username) "hello, world."

The profile image that I want to display for the avatar is that of User123.

For some reason, my current script is not allowing me to do this - it shows User876's pic instead. I tried switching the join statement to uu.user_id = ut.user_id, but that didn't work either. Any ideas?

related sql:

Code: Select all

 
SELECT uu.uu_id, uu.user_id, uu.username, uu.comment_id, uu.comment_username, uu.comment, ut.user_id, ut.username, ut.image_data FROM uu JOIN ut ON uu.user_id = ut.user_id WHERE uu.uu_id = $_GET['id']
 
displayed with php using:

Code: Select all

 
if image_data is not empty do this...
 
<a href="/<?php echo $row_getuu['comment_username'];?>" title="<?php echo $row_getuu['comment_username'];?>"><img src = "http://www.mysite.com/.../<?php echo $row_getuu['comment_username'];?>/<?php echo $row_getuu['image_data']?>"/></a>
 
... else show default avatar and continue...
 
Cheers,
Brad

Re: JOIN not retrieving the correct user's profile image

Posted: Fri Mar 26, 2010 3:31 pm
by asterix299
I don't quite understand your code. In your tables, uu and ut, what exactly do each of them store? I'm assuming uu is the comments and ut is the user table. If this is the case, why does uu have two id's and one for the user? i.e. what is the difference between uu.uu_id and uu.comment_id? Also, I usually don't place image data directly into the database, rather, I put them in a file and place the file location in the db.

Perhaps explain the structure of your tables more and I can help.

Re: JOIN not retrieving the correct user's profile image

Posted: Fri Mar 26, 2010 5:31 pm
by ninethousandfeet
hey.

sorry for the confusion, let me see if i can explain it to make more sense.

ut table = the user table (user_id, username, image_data, email, pwd...)

uu table = the comment table
uu_id = the unique id for a new comment
uu.comment_id = the user_id of the user that creates the comment
uu.user_id = the user_id of the user who receives the comment

image_data = this is actual just the filename. i have something like what you mentioned (the file is created in the directory and just the filename is stored in the database in the image_data field).

hope this helps clear up my structure. lmk if i can send more info and i definitely will do what it takes to figure this problem out.

cheers,
brad

Re: JOIN not retrieving the correct user's profile image

Posted: Fri Mar 26, 2010 11:35 pm
by asterix299
Well your join only collects data from one of the users, right now the one that is receiving the comment. If you're more interested in the user that sent the comment's avatar (which i think you are), you should change it to:

Code: Select all

SELECT uu.username, uu.comment_username, uu.comment, ut.username, ut.image_data FROM uu JOIN ut ON uu.user_id = ut.comment_id WHERE uu.uu_id = $_GET['id']

Re: JOIN not retrieving the correct user's profile image

Posted: Sat Mar 27, 2010 2:30 am
by ninethousandfeet
okay, so i think you made a small typo, but i understood what you were saying. you think i should set uu.comment_id = ut.user_id, right? this would set the commenting user's id with the user table user id (so we get the right image). the only problem here is that when i try to echo the username of the receiving user (uu.username), it is the same as the sending username (uu.comment_username).

any idea why it would echo the wrong name? i double checked in my db and the names appear correctly in the db so i don't know what it would display that way...

Re: JOIN not retrieving the correct user's profile image

Posted: Sat Mar 27, 2010 2:19 pm
by asterix299
perhaps, since you need more than one user's info, you should add an AND to grab the second user's info as well. This way you will get two rows of info, one containing the sender's info and one containing the receiver's info. The problem with this is you will get redundancy because both rows will contain a lot of the same info. A solution would be to do it in two separate queries like this:

Code: Select all

SELECT comment, user_id, comment_id FROM uu WHERE uid= $_GET['id']
...extract the user ids from the result...
SELECT username, image_data FROM ut WHERE id=$senderID AND id=$receiverID
...Two rows will be returned; one for the sender, one for the receiver. Use the info as you wish.
This will be sufficient. A join will get complicated if you need the info of two users, and like I said, redundant in your result. I don't know how the efficiency compares but I would imagine there is not much difference.