Counting results, but a little more in depth

[wookie]

I have a bridged Coppermine Gallery and Phpbb forum running from the same database, in the gallery people make comments on photo's members have uploaded...its a community thing. Now I have put an sql query together that counts those comments made by the members and displays the No of comments that member has made underneath their post count in the forum area of the site. Here is that query.

 

                $sql = "SELECT COUNT(*) as num_comments FROM `cpg14x_comments` WHERE author_id = '" . $row['user_id'] . "'";
                    $result = $db->sql_query($sql);
                    $count_row = $db->sql_fetchrow($result);
                    $comment = $count_row['num_comments'];
                    $db->sql_freeresult($result);

 

What I want to do is only count the comments made by members on other members photo's, not comments made on their own. At the moment the above query counts ALL comments made.

 

Here are the three table and some field names of note that I think can be used to help me achieve this, although I lack the SQL knowledge to implement this query further than I have so far.

 

Table name Field names, (Description)

 

cpg14x_pictures owner_id (Example: 2) owner_name (Example: Wookie) pid (Picture id No, Example: 37)

cpg14x_comments author_id (Example: 2) msg_author (Example: Wookie) pid (Picture id No, Example: 37)

phpbb_user user_id (Example: 2) username (Example: Wookie)

 

owner_id , author_id and user_id are all the same number.

owner_name, msg_author and username are the same name

pid, pid are the same number

 

I want to be able to count comments from cpg14x_comments where the author_id = user_id from phpbb_users (I'm doing this part already with the above query), but (and this is the bit I have had no luck doing) At the same time, I want to check the pid from those comments against the tables cpg14x_pictures pid number and  not include them is the final result if the pid is owned by the author_id.

 

I hope I've given enough info for clarity's sake, can you help me take this query further?

 

Many thanks in advance for any help offered.

 

Wookie

[wookie]

Just reading through some other topics searching for an answer, would inner join help me out here? I've no idea i it will, just guessing and trying to get my head round it all.

 

Wookie

[fenway]

Yes, joins are the right way to combine conditions across tables.

[wookie]

How would I use joins in this scenario? As I said, I'm way out of my depth now and have no idea on how to extend the query I have now.

 

Wookie

[fenway]

How would I use joins in this scenario? As I said, I'm way out of my depth now and have no idea on how to extend the query I have now.

 

I'm not faimliar with your DB -- you want to exclude comments that are linked to pictures?