Jump to content


Photo

PHP/MYSQL help!


  • Please log in to reply
4 replies to this topic

#1 xerodefect

xerodefect
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 18 October 2006 - 12:00 AM

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)

#2 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 18 October 2006 - 03:07 AM

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

#3 xerodefect

xerodefect
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 18 October 2006 - 07:23 PM

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]

#4 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 19 October 2006 - 03:22 AM

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).

JOIN accounts AS a ON t.owner = a.id
JOIN accounts AS ap ON p.post_owner = ap.id

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.



#5 xerodefect

xerodefect
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 19 October 2006 - 07:24 PM

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

SQL Now:
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

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users