TalkPHP - Count multiple tables + same row name

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - Count multiple tables + same row name (http://www.talkphp.com/general/1233-count-multiple-tables-same-row-name.html)

Count multiple tables + same row name

Hi!

How to count from multiple tables where all has same rowname?

table: commentsimage
rowname: poster

table: commentsprofile
rowname: poster

table: commentscafe
rowname: poster

I have tryed something like this which doesn't work tho..

PHP Code:


		
			
$count = mysql_query("SELECT COUNT(poster) FROM commensimage, commentsprofile, commentscafe WHERE poster = $poster") or die(mysql_error());

Thanks for all help!

Hi there, try the following code

PHP Code:


		
			
 $count = mysql_query("    SELECT 
                COUNT(DISTINCT commensimage.poster) as commensimage_count,  
                COUNT(DISTINCT commentsprofile.poster) as commentsprofile_count, 
                COUNT(DISTINCT commentscafe.poster) as commentscafe_count 
            FROM 
                commensimage, commentsprofile, commentscafe 
            WHERE 
                poster = $poster;") or die(mysql_error());

THis should return 3 seperate counts. If you wanted to add them all together and return the total, you can do something like this:

PHP Code:


		
			
SELECT 
                COUNT(DISTINCT commensimage.poster) + 
                COUNT(DISTINCT commentsprofile.poster) +
                COUNT(DISTINCT commentscafe.poster) as total_count 
            FROM