Join tables and counting?

[phaedrusGhost]

Hi all,

For some reason I can't seem to wrap my mind around JOINING tables and getting the data I need from this.

I have three tables I am trying to join. I need to count the number of blogs, comments and number of views for each user. I have all data ready to be pulled from the DB but I am just not sure of the cleanest most effective way to do this. I could do separate queries for each table but this seems illogical to me.

Some of the areas are not yet filled in here. It's the query I need help with.

Here is what I have:

function displayAgents()
{

$sql = ("SELECT * FROM users AS u JOIN comments AS c JOIN blogs AS b WHERE pu.id_user = pc.id_user AND pc.id_user = pb.id_user ");
$result=mysql_query($sql);

$display = '';
      while ($row = mysql_fetch_array($result)) {
          $agentId = $row['id_user'];
          $agentName = $row['name'];
          $agentEmail = $row['email'];
          $agentImage = $row['user_image'];
          $agentHeadline = $row['headline'];
          $agentStatus = $row['user_status'];
          $agentPro = $row['pro_status'];
          $agentCompany = $row['company'];
          $agentLocation = $row['location'];
          $agentWebsite = $row['websites'];
          
          
		  
      
          $display .= "
          
          <div class=\"agentLocated\">
            <div class=\"agentLocatedTop\">
              <div class=\"agentLocatedName\"><a href=\"#\">".  $agentName . "</a></div>
              <div class=\"agentLocatedProStatus\">,&nbsp;Agent</div>
              <div class=\"agentLocatedLevel\"><img width=\"15\" height=\"15\" src=\"images/icons/medal_gold_2.png\" /></div>
              <div class=\"agentLocatedProfile\"><a href=\"\">Profile</a></div>
              <br/>
              <br/>
              <div class=\"agentLocatedImageWrapper\">
                <div class=\"agentLocatedImage\"><img class=\"\" width=\"54\" height=\"64\" src=\"images/users/Asha1.jpg\"></div>
              </div>
              <div class=\"agentLocatedTagline\">". $agentHeadline . "</div>
              <br/>
              <div class=\"agentLocatedInfo\">" . $agentCompany . " | | " . $agentLocation . "</div>
              <br/>
              <br/>
              <div class=\"agentlocatedStats\">9,999 Blog posts</div>
              <div class=\"agentlocatedStats\">9,999 Comments</div>
              <div class=\"agentLocatorRec\">
                <div class=\"agentLocatedRecs\">
                  <div class=\"agentLocatorRecsCount\">0</div>
                  <div class=\"agentLocatorRecsText\">RECS</div>
                </div>
                <div class=\"agentLocatedRevs\">
                  <div class=\"agentLocatorRevsCount\">1</div>
                  <div class=\"agentLocatorRevsText\">REVIEWS</div>
                </div>
                <div class=\"agentLocatorRecommend\"><a href=\"#\"></a></div>
              </div>
            </div>
          </div> \n";
          
          }  
          
          if (!$result) {
              return false;
          } else {
              return $display;
          }       
        }

Thanks in advance for any help on this...

[cereal]

I don't know your database structure, so here's an example:

# tables structure
mysql> explain users;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(9)      | NO   | PRI | NULL    | auto_increment |
| fname   | varchar(50) | NO   |     | NULL    |                |
| lname   | varchar(50) | NO   |     | NULL    |                |
| userid  | varchar(50) | NO   |     | NULL    |                |
| referer | varchar(50) | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> explain blog;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(9)      | NO   | PRI | NULL    | auto_increment |
| title   | varchar(50) | YES  |     | NULL    |                |
| user_id | int(9)      | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> explain comments;
+---------+--------+------+-----+---------+----------------+
| Field   | Type   | Null | Key | Default | Extra          |
+---------+--------+------+-----+---------+----------------+
| id      | int(9) | NO   | PRI | NULL    | auto_increment |
| message | text   | YES  |     | NULL    |                |
| user_id | int(9) | NO   |     | NULL    |                |
| blog_id | int(9) | NO   |     | NULL    |                |
+---------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

# queries to see what is inside:
mysql> select * from users; select * from blog; select * from comments;
+----+-------+-------+------------+---------+
| id | fname | lname | userid     | referer |
+----+-------+-------+------------+---------+
|  1 | jane  | doe   | janedoe    |         |
|  2 | john  | doe   | johndoe    | janedoe |
|  3 | mary  | smith | marysmith  | janedoe |
|  4 | cary  | grant | carygrante | johndoe |
+----+-------+-------+------------+---------+
4 rows in set (0.00 sec)

+----+---------+---------+
| id | title   | user_id |
+----+---------+---------+
|  1 | title 1 |       1 |
|  2 | title 2 |       2 |
|  3 | title 3 |       2 |
|  4 | title 4 |       1 |
|  5 | title 5 |       4 |
+----+---------+---------+
5 rows in set (0.00 sec)

+----+-----------+---------+---------+
| id | message   | user_id | blog_id |
+----+-----------+---------+---------+
|  1 | message 1 |       1 |       1 |
|  2 | message 2 |       2 |       2 |
|  3 | message 3 |       2 |       2 |
|  4 | message 4 |       2 |       3 |
|  5 | message 5 |       1 |       2 |
|  6 | message 6 |       1 |       3 |
|  7 | message 7 |       3 |       4 |
+----+-----------+---------+---------+
7 rows in set (0.00 sec)

# you need a query like this one:
mysql> select u.id,u.fname as firstname,u.lname as lastname, coalesce(b2.blog_c,0) as blogs, coalesce(c2.comments_c,0) as comments from users as u LEFT JOIN (select user_id,count(*) as blog_c from blog group by user_id) as b2 on u.id = b2.user_id LEFT JOIN (select user_id,count(*) as comments_c from comments group by user_id) as c2 on u.id = c2.user_id;
+----+-----------+----------+-------+----------+
| id | firstname | lastname | blogs | comments |
+----+-----------+----------+-------+----------+
|  1 | jane      | doe      |     2 |        3 |
|  2 | john      | doe      |     2 |        3 |
|  3 | mary      | smith    |     0 |        1 |
|  4 | cary      | grant    |     1 |        0 |
+----+-----------+----------+-------+----------+
4 rows in set (0.00 sec)

Hope this helps :)

[phaedrusGhost]

Awesome cereal, really appreciate that solution. Though I haven't been able to try it yet i will reply back when I get the chance. Thanks a million!