phpbb mysql statement

[Citrate]

I am looking for one query to extract private messages from a phpbb forum. For each message I want the sender and receiver of the message. I would like it to be one command if possible.

The tables are:

I will list the fields that are needed, not every field!)

table: phpbb_privmsgs_text (has the body of the private message)
needed fields:
>> privmsgs_text_id
>> privmsgs_text

table: phpbb_privmsgs (has the senders id and the receivers id etc.)
needed fields:
>> privmsgs_id
>> privmsgs_from_userid
>> privmsgs_to_userid

table: phpbb_users
needed fields:
>> user_id
>> username


So I would like to list all private messages in the forum with the message body, sender username and receiver username.

Thanks for your help!!

 

[r937]

select m.privmsgs_id
     , t.privmsgs_text
     , s.username as sender
     , r.username as receiver
  from phpbb_privmsgs as m
inner
  join phpbb_privmsgs_text as t
    on m.privmsgs_id
     = t.privmsgs_text_id
inner
  join phpbb_users as s
    on m.privmsgs_from_userid
     = s.user_id
inner
  join phpbb_users as r
    on m.privmsgs_to_userid
     = r.user_id

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Citrate]

thanks mate!!

i got it to work from:

SELECT pt.privmsgs_text_id, u1.username as from_user, u2.username as to_user, pt.privmsgs_text
FROM phpbb_privmsgs_text pt, phpbb_privmsgs p, phpbb_users u1, phpbb_users u2
WHERE pt.privmsgs_text_id = p.privmsgs_id AND p.privmsgs_from_userid = u1.user_id AND p.privmsgs_to_userid = u2.user_id
GROUP BY pt.privmsgs_text
having count(*) >= 1
ORDER BY pt.privmsgs_text_id DESC

is there a benefit to using inner joins? i really appresiate this.

 

[r937]

yes, there are several benefits to using inner joins, but that's a different thread

who gave you the GROUP BY? i think that's wrong

what happens if you omit the GROUP BY and HAVING clauses?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Citrate]

the group by was a life saver really. phpbb seems to have two copies of each message (one for the receivers inbox and one for the senders outbox) so by adding the "group by" and "count" function it removed the duplicates for me.

 

[r937]

whoa

well, all i can say is, duplicate data wasn't obvious from the original problem statement, was it



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Citrate]

very true, but i only told you because you asked about the "group by" statement, thanks for your help anyway, i know i can learn from your example. happy new year!