How can I join 3 tables with mysql & php?

Question

check out the page [url]http://www.mujak.com/test/test3.php[/url]

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~??

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

I haev tried multiple queries with no success:

$result = mysql_query("
    SELECT * FROM mybb_users
    LEFT JOIN (mybb_posts, mybb_userfields, mybb_threads)
    ON (
        mybb_userfields.ufid=mybb_posts.uid
        AND mybb_threads.tid=mybb_posts.tid
        AND mybb_users.uid=mybb_userfields.ufid
    )
    WHERE mybb_posts.fid=42");


$result = mysql_query("
    SELECT * FROM mybb_users
    LEFT JOIN (mybb_posts, mybb_userfields, mybb_threads)
    ON (
        mybb_userfields.ufid=mybb_posts.uid
        AND mybb_threads.tid=mybb_posts.tid
        AND mybb_users.uid=mybb_posts.uid
    )
    WHERE mybb_threads.fid=42");

$result = mysql_query("
    SELECT * FROM mybb_posts 
    LEFT JOIN (mybb_userfields, mybb_threads) 
    ON (
        mybb_userfields.ufid=mybb_posts.uid
        AND mybb_threads.tid=mybb_posts.tid
    )
    WHERE mybb_posts.fid=42");

Answer 1

Your syntax isn't appropriate for carrying out multiple LEFT JOINs. Each join needs its own ON clause.

SELECT
    *
FROM
    mybb_users
    LEFT JOIN mybb_userfields ON mybb_users.uid = mybb_userfields.ufid
    LEFT JOIN mybb_posts ON mybb_userfields.ufid = mybb_posts.uid
    LEFT JOIN mybb_threads ON mybb_posts.tid = mybb_threads.tid
WHERE
    mybb_posts.fid = 42

This query should give the results you want. But it may not be the most efficient query for getting those results. Check the output of EXPLAIN as part of testing, to make sure it is not using table scans or anything like that.

Do all of these joins need to be LEFT JOINs? LEFT JOIN forces MySQL to join the tables in the indicated order, rather than allowing the query optimiser to determine the best order in which to join them. That's why you might need to be careful about the query execution plan. The main difference between JOIN and LEFT JOIN as far as query output is concerned is that LEFT JOIN resultsets will contain at least one row for each row of the table on the left-hand side of the join, whereas a regular JOIN will not contain a row if there aren't matches on the right-hand side of the join.

Edit: Also, you say that "I don't want it to SHOW the changed subject title, just the original title of all posts in that thread." This suggests that you only want a subset of the columns from these tables, in which case SELECT * is inappropriate.