Count from two tables

[red-x]

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];

 

In both tables I have the same name "author" that's what I want to count.

 

Thanks in advance. 

[waynew]

$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];

$sql2 = "SELECT COUNT(*) FROM comments WHERE author='$user'"; 
$result2 = mysql_query($sql2) or trigger_error("SQL", E_USER_ERROR); 
$r2 = mysql_fetch_row($result2); 
$numrows2 = $r2[0];

$total_rows = $numrows + $numrows2;

 

If you're wanting to do it the easy way.

[waynew]

$result = mysql_query("SELECT comments.COUNT(*), posts.COUNT(*) FROM comments, posts WHERE comments.author='$user' AND posts.author='$user'") or die(mysql_error());

$r = mysql_fetch_row($result);

$total_posts = $r[0] + $r[1];

 

 

[red-x]

I tried the second one but it give me an error...

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*), posts.COUNT(*) FROM comments, posts WHERE comments.author='Alex' AND posts' at line 1

[waynew]

Try

 

$result = mysql_query("SELECT comments.COUNT('author'), posts.COUNT('author') FROM comments, posts WHERE comments.author='$user' AND posts.author='$user'") or die(mysql_error());

$r = mysql_fetch_row($result);

$total_posts = $r[0] + $r[1];

[red-x]

Now it says this..

 

FUNCTION comments.COUNT does not exist

[waynew]

My very bad!!!

 

$result = mysql_query("SELECT comments.COUNT(author), posts.COUNT(author) FROM comments, posts WHERE comments.author='$user' AND posts.author='$user'") or die(mysql_error());

$r = mysql_fetch_row($result);

$total_posts = $r[0] + $r[1];

[red-x]

Thank you! that worked