Count from two tables

Discussion in 'PHP' started by red-x, Oct 5, 2008.

0
  1. #1
    Hi, I'm trying to count data from two mysql tables. One is called comments and the other one is called posts. I want to count how many comments and posts a user have. Here's the code I'm using to count how many comments does the user have from the table comments.

    
$sql = "SELECT COUNT(*) FROM comments WHERE author='$user'";  
$result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR);  
$r = mysql_fetch_row($result);  
$numrows = $r[0];
    PHP:
    In both tables I have the same name "author" that's what I want to count.
    I was trying to add FROM comments,posts but it didn't work.

    Thanks in advance. :)
     
    red-x, Oct 5, 2008 IP
  2. bartolay13

    bartolay13 Active Member

    Messages:
    735
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    98
    #2
    do subquery

    select (select count(*) from table1) as countCAT, (select count(*) from table2) as countDOG
    from table1 as t1 left join table2 as t2 on t1.colmname = t2.colmname where t1.columname = 'whatever'
     
    bartolay13, Oct 6, 2008 IP
  3. bartolay13

    bartolay13 Active Member

    Messages:
    735
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    98
    #3
    select (select count(*) from comments) as commentcount, (select count(*) from posts) as postcount
    from comments as t1 left join posts as t2 on t1.authorname = t2.authorname where t1.authorname = 'whatevernamehehas'
     
    bartolay13, Oct 6, 2008 IP
  4. red-x

    red-x Peon

    Messages:
    48
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Hey, thanks but when I did that its telling me all the users have 30 post and they don't. Here's the code I used, I'm not sure if I did it right..

    $sql = "SELECT (SELECT COUNT(*) FROM comments) AS commentcount, (SELECT COUNT(*) FROM posts) AS postcount
FROM comments AS t1 left join posts AS t2 on t1.author = t2.author WHERE t1.author = '$user'";
    PHP:
     
    red-x, Oct 6, 2008 IP