Member Avatar for 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?

  • 4 Contributors
  • 4 Replies
  • 146 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for 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' )");
Edited by DJBirdi
Member Avatar for 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!

Member Avatar for 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");
Member Avatar for 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");
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.