Jump to content

PHP/MYSQL help!


xerodefect

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)
Link to comment
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?
Link to comment
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]
Link to comment
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.

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.