Having trouble with LEFT JOIN

[bruceleejr]

http://www.mujak.com/test/test3.php

It pulls the users Post,username,xbc/xlk tags etc which is perfect... BUT since I am pulling information from a MyBB bulletin board system, its quite different. When replying, people are are allowed to change the "Thread Subject" by simplying replying and changing it.

 

I dont want it to SHOW the changed subject title, just the original title of all posts in that thread.

 

By default it repies with "RE:thread title". They can easily edit this and it will show up in the "Subject" cell & people wont know which thread it was posted in because they changed their thread to when replying to the post.

 

So I just want to keep the orginial thread title when they are replying.

 

Make sense~??

 

 

My code works, I just want the subject to stay as the original subject even if the LASTPOSTER changes his thread title, thats all.

 

And I THINK the only way to do that is by joining 3 tables. Argh.

 

Tables:mybb_users

Fields:uid,username

 

Tables:mybb_userfields

Fields:ufid

 

Tables:mybb_posts

Fields:pid,tid,replyto,subject,ufid,username,uid,message

 

Tables:mybb_threads

Fields:tid,fid,subject,uid,username,lastpost,lastposter,lastposteruid

 

This is the mybb_threads table:

http://img251.imageshack.us/img251/222/helpyo.jpg

 

You can see in the subject, when they reply it leaves "RE:" before the title.

Well I just want to show the ORIGINAL subject (without the "re:" no matter if they changed THEIR post title in the thread or not) in the cell column of the thread.

 

Does it have somethin to do with matching the "replyto" field~?? Thats why I crossed out anything other than a 0 in the picture

 

 

Here is the code:

<div style="position:relative;left:10px;">

<?php
$con = mysql_connect("mujak.com","rootkbc1_muser","*******");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("rootkbc1_halo2x", $con);

$result = mysql_query("SELECT * FROM mybb_posts LEFT JOIN mybb_userfields ON (mybb_posts.uid = mybb_userfields.ufid) WHERE fid=42 ORDER BY pid DESC LIMIT 0,3");

echo "<center><table border=1 bgcolor=white>
<tr>
<th><h3>Subject</h3></th>
<th><h3>Posts</h3></th>
<th><h3>Username</h3></th>
<th bgcolor=#C0CFFA><h1>XBC</h1></th>
<th bgcolor=#C9FFC2><h1>XLK</h1></th>
<th><h3>Team</h3></th>
<th><h3>Console</h3></th>
<th><h3>Location</h3></th>
<th><h3>Bandwidth</h3></th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td style='-moz-border-radius:9px;padding:5px;'bgcolor=#9CDCFF><b>" . $row['subject'] . "</b></td>";
  echo "<td style='-moz-border-radius:9px;padding:5px;font-size:10px;'bgcolor=#C9ECFF>" . date_default_timezone_set('MST') . gmdate("D F j, Y, g:i a", $row['dateline']) . "<br>" . $row['message'] . "</td>";
  echo "<td bgcolor=#C9ECFF>" . $row['username'] . "</td>";
  echo "<td bgcolor=#C0CFFA>" . $row['fid5'] . "</td>";
  echo "<td bgcolor=#C9FFC2>" . $row['fid6'] . "</td>";
  echo "<td bgcolor=#C9ECFF>" . $row['fid9'] . "</td>";
  echo "<td bgcolor=#C9ECFF>" . $row['fid7'] . "</td>";
  echo "<td bgcolor=#C9ECFF>" . $row['fid1'] . "</td>";
  echo "<td bgcolor=#C9ECFF>" . $row['fid8'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con)
?>
</div>

[fenway]

TLDR... what's wrong with that query?