Count rows from two tables

[SimonIoa]

I want to count rows from table1 and rows from table2 and retrieve a sum from both tables.
i use this...

        $query=mysql_query("SELECT COUNT(*) AS total FROM (SELECT COUNT(*) FROM message_believe WHERE message_believe.uid_fk ='$uid' UNION ALL SELECT COUNT(*) FROM review_believes WHERE review_believes.uid_fk ='$uid' )");
        $data=mysql_fetch_array($query);
        echo $data['total'];

what do i do wrong?

[DJBirdi]

Any specific errors? I believe UNION statement has a few very specific requirements. Off the top of my head, two of them are that the SELECT statements must have the same number of columns you're quering for, and those columns' data types must match.

So instead of using COUNT(*) in your UNION statement, just use a column name that's the same in both tables:

$query=mysql_query("SELECT COUNT(*) AS total FROM (SELECT some_column_name FROM message_believe WHERE message_believe.uid_fk ='$uid' UNION ALL SELECT some_column_name FROM review_believes WHERE review_believes.uid_fk ='$uid' )");

[cereal]

Also, each subquery requires an alias, for example:

select a.total from (select count(*) as total from tablename) as a;

Otherwise you get an error, to see it use mysql_error(). In addition try this query:

$query=mysql_query("select a.total + b.total as total from (select count(*) as total from message_believe as mb where mb.uid_fk = {$uid}) as a, (select count(*) as total from review_believes as rb where rb.uid_fk = {$uid}) as b") or die(mysql_error());

Bye!

[urtrivedi]

I have put alias a after parenthesis. Try with alias

$query=mysql_query("SELECT COUNT(*) AS total FROM (SELECT COUNT(*) FROM message_believe WHERE message_believe.uid_fk ='$uid' UNION ALL SELECT COUNT(*) FROM review_believes WHERE review_believes.uid_fk ='$uid' ) a");

[urtrivedi]

I have put coluname in query as told by djbirdi and I have put alias a after parenthesis. Try with alias

$query=mysql_query("SELECT COUNT(*) AS total FROM (SELECT uid_fk FROM message_believe WHERE message_believe.uid_fk ='$uid' UNION ALL SELECT uid_fk FROM review_believes WHERE review_believes.uid_fk ='$uid' ) a");