Jump to content

Archived

This topic is now archived and is closed to further replies.

xerodefect

PHP/MYSQL help!

Recommended Posts

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)

Share this post


Link to post
Share on other sites
[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?

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.