Php - mysql count from morethan one table

Databases
1

Hai folks,

table1

empno|salary

10 | 1500
20 | 2750
21 | 4000

table2

empno|department

19 | a
24 | b
31 | x

i want a query to get the total records in both tables.
so the result should be 6 records.

my try :


$query="SELECT COUNT(empno) as tot
        FROM table1
		
		UNION ALL // and tried with UNION too

		SELECT COUNT(empno) as tot
        FROM table2";

if ($result=mysql_query($query) or die (mysql_error()));
$row=mysql_fetch_array($result);
echo "<br>" . $row['tot'];

shows incorect row count :frowning:

2

the union gives you two rows
loop through them in PHP and sum the ‘tot’ values.

3

Thank u for the instant reply.
if there is no native way, then ill do the looping :slight_smile:

4

You can do this if you don’t want to loop:


SELECT SUM(tot) AS tot
FROM
  (SELECT 
       COUNT(empno) AS tot 
   FROM table1
   UNION ALL
   SELECT 
       COUNT(empno) AS tot
   FROM table2
  ) AS a
5

Thanks so much for your answer. today only noticed.
ill chk this solution!