avatar code error

i have trouble pulling from the post the avatar:

PHP Code:

$newssql=$DB_site->query("
SELECT * FROM thread
WHERE forumid='$newsforum'
ORDER BY dateline DESC LIMIT $newsposts
");
while ($news=$DB_site->fetch_array($newssql)) {
  $getnewssql=$DB_site->query_first("
  SELECT pagetext
  ".iif($avatarenabled,",avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline","")."
  FROM post
  ".iif ($avatarenabled,"LEFT JOIN avatar ON avatar.avatarid=user.avatarid
                         LEFT JOIN customavatar ON customavatar.userid=user.userid","")."
  WHERE threadid='$newsthreadid'
  ORDER BY postid ASC LIMIT 1
  ");
  $newstext=bbcodeparse($getnewssql[pagetext]);
  $newsavatar=$getnewssql[avatar];
} 


what do i do wrong?

[Admin]

You need to add a JOIN to your query, on the user table.

[TECK]

i pm you the code FireFly..
hehe a love that most of my hacks contain credits for your work..

[TECK]

LEFT JOIN user ON user.userid=post.userid

?

[Admin]

Yup. But since you are doing this every time anyway, I suggest using getavatarurl() as it will save you 3 JOINs, one of them is on the user table which is huge.

[TECK]

can you clear the last part of the mistery for me? that's all i need to release the hack.
i looked in functions.php but i'm a little confused how to call the avatar from it with [high]getavatarurl($userid)[/high]
thanks for your time.

[Admin]

Never mind, use what you had before it's the same. Either way you get the same thing I think.

But basically you just call getavatarurl with a user id and it gives you what you need to use in the <img src="...."> tag.

[TECK]

i will use the getavatarurl. i think is a more elegant solution.
i will post here the rest of the code so you can evaluate it.
although, can you tell me where is called, from functions.php that bit of code? so i can see the code mechanism?

thanks for your help firefly.

[TECK]

i got it working.

[TECK]

i have another problem:

PHP Code:

$newssql=$DB_site->query("SELECT * FROM thread
                          WHERE forumid='$newsforum'
                          ORDER BY dateline DESC LIMIT $newsposts");
while ($news=$DB_site->fetch_array($newssql)) {
  $newsthreadid=$news[threadid];
  $newstitle=$news[title];
  $newstime=vbdate($timeformat,$news[dateline]);
  $newsdate=vbdate($dateformat,$news[dateline]);
  $newsusername=$news[postusername];
  $newsuserid=$news[postuserid];
  $getnewssql=$DB_site->query_first("SELECT * FROM post
                                     WHERE threadid='$newsthreadid'
                                     ORDER BY postid ASC LIMIT 1");
  $newstext=bbcodeparse($getnewssql[pagetext]);
  $newsavatarurl=getnewsavatar($newsuserid);
  if ($newsavatarurl=="") {
    $newsavatarurl="{imagesfolder}/clear.gif";
  }

  eval("\$newsavatar .= \"".gettemplate('home_avatar')."\";");
  eval("\$newsbits .= \"".gettemplate('home_newsbits')."\";");
} 


it adds an extra image every time i add a new post, so if i have 3 threads started, i will have 3avatars for a post?? here it is an example: