PHP/MYSQL help!

[xerodefect]

Hello guys here :P
Ok i got 3 tables to grab info. topics, post, and accounts. Well in tables i need to grab 2 diff account rows. Im doing this in one query using inner,left, and right join. Im having trouble grabbing 2 rows from accounts.

Query: SELECT t.id AS tid,t.*,p.id AS pid,p.*,a.id AS aid,a.* FROM topics AS t INNER JOIN posts AS p ON t.post_id=p.id INNER JOIN accounts AS a ON t.owner=a.id AND p.post_owner=a.id WHERE t.board_id=$board[bid] ORDER BY t.date DESC LIMIT $start,$limit

I really dont know how to match owner (its an id of accounts) and post_owner (id from accounts) to replace with the account username where it matchs. Can you, if soo please help.

(I never had to do this b/4 :D)

[btherl]

[code]SELECT t.id AS tid,t.*,p.id AS pid,p.*,a.id AS aid,a.*
FROM topics AS t
INNER JOIN posts AS p ON t.post_id=p.id
INNER JOIN accounts AS a ON t.owner=a.id AND p.post_owner=a.id
WHERE t.board_id=$board[bid]
ORDER BY t.date DESC
LIMIT $start,$limit[/code]

I've reformatted the query a bit.

I don't fully understand what you are trying to do.  What do you want your results to look like?

[xerodefect]

Well now how do i get the 2 account username from the 2 IDs. (t.owner and p.post_owner) something like that i forgot im not looking at it :P

thanks

EDIT - Snapped pciture. Wanna replace the thing circal with the user's username.
(Topics  Owner  Replies  Views  Last Action is the order)

[attachment deleted by admin]

[btherl]

Ok I understand now.  Instead of the account id, you want to display the account username.  But you want to do this for both the owner and the post_owner?  To do that in a single query, you'll need to join with the accounts table twice (assuming the accounts table has the username).

[code]JOIN accounts AS a ON t.owner = a.id
JOIN accounts AS ap ON p.post_owner = ap.id[/code]

Then a.username will be the owner's username, and ap.username will be the post_owner's username.  Is that what you're looking for?

There's no need to specify "INNER", since that's the default.

[xerodefect]

Hey dude thanks i see what you mean. I never thought of doing it that way.

SQL Now: [code]SELECT t.id AS tid,t.*,p.id AS pid,p.*,a.id AS aid,a.username AS ausername,a.*, ap.id AS apid, ap.username AS apusername, ap.* FROM topics AS t INNER JOIN posts AS p ON t.post_id=p.id JOIN accounts AS a ON t.owner = a.id
JOIN accounts AS ap ON p.post_owner = ap.id WHERE t.board_id=$board[bid] ORDER BY t.date DESC LIMIT $start,$limit[/code]

lol 2 inner joins and rename them :D tyz  :P