0

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?

4
Contributors
4
Replies
22
Views
3 Years
Discussion Span
Last Post by urtrivedi
0

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' )");

Edited by DJBirdi

0

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!

0

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");
0

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");
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.